troubleshooting Question

Union & Crosstab qrys 101!

Avatar of M M
M M asked on
Microsoft AccessSQL
6 Comments1 Solution192 ViewsLast Modified:
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.jpgand 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];
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros