Learned that I need a UNION qry to make crosstab qry for a report just yesterday (TY PatHartman!!!!).
created separate qrys from my tblSales and tblQuotes to get matching # of columns and the set identifiers (literal field indicating source, in my case 'sales' or 'quotes') to copy&paste SQLs for my union qry.
Do I need to save those preliminary qrys once I get everything finalized, y/n?
My nav pane is a mess, so many trials & errors, but afraid to delete anything for fear of everything falling like dominoes. Any tips would be appreciated!
In those preliminary qrys for the union qry, I included the field
week ending date to use as a column heading and it seems to work! Should I go back to the preliminary qry and add month and qtr fields (as those are the time periods I need to pull summaries of counts and totals/revenue for),
y/n? Or create separate union qrys for each desired column heading,
y/n?
Crosstab qrys
It appears I can only get 1 value per crosstab qry?! I tried to get counts and totals and wouldn't let me. Do I have to do separate crosstab qrys for counts and totals x 3 different column headings (weeks, months, qtrs) and then manipulate/arrange them in a report,
y/n?
I know this is a lot, assuming I am even understanding my initial foray into unions & crosstabs! Feel free to break it down. But thanks again, in advance.
Here's my first union qry of sales & quotes:
![firstUNIONqry.jpg]()
and its SQL:
TRANSFORM Sum(qryUnionSales.[SAmt]) AS SumOfSAmt
SELECT qryUnionSales.[RepID], qryUnionSales.[ActType], Sum(qryUnionSales.[SAmt]) AS [Total Of SAmt]
FROM qryUnionSales
WHERE (((qryUnionSales.[Week Ending Date])>#12/31/2016#))
GROUP BY qryUnionSales.[RepID], qryUnionSales.[ActType]
PIVOT qryUnionSales.[Week Ending Date];
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.