asked on
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.
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!
ASKER
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!
ASKER
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.
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.