joining two crosstab queries

[shasta123]

I have 2 CrossTab Queries.
"qry_WBtotal_CrossTab" 'qry1
and
"qry_budTxAmt_CrossTab" 'qry2

both share same row and column headings.
row: DpName (from tbl_Dept)
column: RvName (from tbl_revenue)
value: WBtotal (from tbl_fteAllocation) 'qry1
value: budTxAmt (from tbl_budTx) 'qry2

I would like to make another CrossTab Query which will sum both data.

for example,

"qry_WBtotal_CrossTab" returns

DpName | Admission | A.Fund |
collection | 5,000 | 2,000 |
education| 0 | 1,500 |

"qry_budTxAmt_CrossTab" returns

DpName | Admission | Bus. Inc.|
collection | 5,000 | 500 |
info tech | 1,000 | 1,000 |

I would like this new query to return

DpName | Admission | A.Fund | Bus.Inc.|
collection| 10,000 | 2,000 | 500 |
education| 0 | 1,500 | |
info tech | 1,000 | | 1,000 |

is this possible? im keep messing around /w cross queries but im not getting the result i want.
any help would be great! thx in advance

here's the sql coding for the queries:

TRANSFORM Sum(Tbl_FTEallocation.WBtotal) AS WBtotalOfSum
SELECT tbl_Dept.DpName, Sum(Tbl_FTEallocation.WBtotal) AS [Total of WBTotalAmt]
FROM (Tbl_FTEallocation INNER JOIN tbl_Dept ON Tbl_FTEallocation.Dpid = tbl_Dept.DPID) INNER JOIN tbl_Revenue ON Tbl_FTEallocation.RevNum = tbl_Revenue.revnum
GROUP BY tbl_Dept.DpName
PIVOT tbl_Revenue.rvname;

other query is exactly the same except that it returns 'budTxAmt' not 'WBtotal'

[GolferGuy]

Here is the SQL that will bring your two crosstab queries together, record by record.

SELECT qry_budTxAmt_CrossTab.DpName, 
qry_budTxAmt_CrossTab.Admission, 
qry_Wbtotal_CrossTab.Afund, 
qry_budTxAmt_CrossTab.BusInc
FROM qry_budTxAmt_CrossTab 
INNER JOIN qry_Wbtotal_CrossTab 
ON (qry_budTxAmt_CrossTab.Admission = 
          qry_Wbtotal_CrossTab.Admission) 
AND (qry_budTxAmt_CrossTab.DpName = 
          qry_WBtotal_CrossTab.DpName);

Hope this helps,

Vic

[shasta123]

thx for your reply.
your sql statement is a select query though right?
i need to make a "total" crosstab query.
or am i misunderstanding your statemnt?

[GolferGuy]

DpName | Admission | A.Fund | Bus.Inc.|

That is what you asked for, the SQL I presented does that. It is not a crosstab query. Unless you can explain WHY it needs to be a crosstab query, I sure don't understand why the fixation on a crosstab query. I think the best thing for you to do is explain what you want, not how you want to get it. When we know what it is that you want, if it is not the 4 fields you originally asked for, then please explain WHAT you want, not what tool you want used to get there.
Thanks,

[cgwhites]

I would try the following:

Create union query named "qry_Crosstab_Union" to join the two crosstabs:

TABLE [qry_WBtotal_CrossTab]
UNION TABLE [qry_budTxAmt_CrossTab];

Then create select query with grouping to sum up the values:

Select DpName, Sum(Admission), Sum(A.Fund)
FROM qry_Crosstab_Union
GROUP BY DpName;

Hope this works for you,
Chris