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?
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:
and its SQL:
TRANSFORM Sum(qryUnionSales.[SAmt]) AS SumOfSAmt
SELECT qryUnionSales.[RepID], qryUnionSales.[ActType], Sum(qryUnionSales.[SAmt]) AS [Total Of SAmt]
WHERE (((qryUnionSales.[Week Ending Date])>#12/31/2016#))
GROUP BY qryUnionSales.[RepID], qryUnionSales.[ActType]
PIVOT qryUnionSales.[Week Ending Date];