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:![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];
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];
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!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.
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.
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
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!
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Thanks for using Experts Exchange.
Please provide your email to receive a sample view!
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.