M M
asked on
Union & Crosstab qrys 101!
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?
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:
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];
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:
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];
Last Comment
ASKER
When I try something and it doesn't work but I don't want to actually delete it yet, I rename it to have three leading z's. That way if something fails because I was actually using the object, I can just remove the z's. Otherwise, every month or so, I delete all the z objects.That's sounds like a pretty good plan!
Yes you have to save the union query. Some people put the subqueries inside the union query so they have only one query. That's fine for just a couple of subqueries. If you have more, the union is simpler if it just says Select * from q1 Union All select * from q2, etc.Yes, I understood saving the union qry. It's those (my) 'preliminary qrys' = (your) 'subqrys' that was wondering if needed to be saved.
You confirmed no as I copied & pasted the sql of the prelim qrys/subqrys into the union qry, right?
Crosstab only "pivots" one value. If you need more, do a separate crosstab for each and then join the crosstabs. So you can select amounts from one crosstab and counts from another.I'll save joining the crosstabs for later ... besides potential more points!
I didn't realize that you needed to crosstab the data first. Once you pivot the data, you end up with columns from different months and quarters and there is no way to separate them. Step back and think about this again. Layout the report in a spreadsheet so you have a clear picture of what you are trying to do. Post the example and we'll help you get there. You may have to make several reports so you can segregate the month summaries in one report and the quarter summaries in a second.Not sure I exactly follow this. But here's a ROUGH mock up of what I had in mind (not current per se). Additionally if you can see what I'm trying to do, maybe you have suggestions as to how to do better!
THANK YOU PAT!!!!!
MockUp.xlsx
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I'm gonna thank you now PatHartman as it's going to take me a "minute" to absorb this all. Got a couple of qtns regarding terminology (ie - what's a fixed column; would WeekNum:Format(SDate, "ww" that YOU helped w earlier be applicable, etc.).
Let me absorb some of this and I'll close this qtn out (might be a day or two). I appreciate the direction you have provided and the patience you have exhibited.
Pat, you have gone above & beyond, and I thank you very, very much!
This is a lot more work than a volunteer can be expected to do for you so give it a shot and see where you end up. There is a useful example at FMSINC.com that includes a report with variable headings which is essentially what you have here with the period names.I apologize. I do not want to take advantage of any Expert here! I WILL take a stab at it!
Let me absorb some of this and I'll close this qtn out (might be a day or two). I appreciate the direction you have provided and the patience you have exhibited.
Pat, you have gone above & beyond, and I thank you very, very much!
ASKER
(Just took a look at the fms link, and it answered/clarified what a fixed column is! I will be studying this very helpful link!)
HOW can I award this Expert more points?!!! I can't sing his praises loudly enough!
PatHartman has gone above & beyond! He teaches ppl how to fish ...
HOW can I award this Expert more points?!!! I can't sing his praises loudly enough!
PatHartman has gone above & beyond! He teaches ppl how to fish ...
Thank YOU. The FMS site has tons of good samples and I can recommend their products also.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Yes you have to save the union query. Some people put the subqueries inside the union query so they have only one query. That's fine for just a couple of subqueries. If you have more, the union is simpler if it just says Select * from q1 Union All select * from q2, etc.
Crosstab only "pivots" one value. If you need more, do a separate crosstab for each and then join the crosstabs. So you can select amounts from one crosstab and counts from another.
I didn't realize that you needed to crosstab the data first. Once you pivot the data, you end up with columns from different months and quarters and there is no way to separate them. Step back and think about this again. Layout the report in a spreadsheet so you have a clear picture of what you are trying to do. Post the example and we'll help you get there. You may have to make several reports so you can segregate the month summaries in one report and the quarter summaries in a second.