-
joining two crosstab queries
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'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|