-
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'
-
Here is the SQL that will bring your two crosstab queries together, record by record.
Code:
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
-
cr0sstab?
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?
-
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,
-
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
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
|
|