Have you tried using the Wizard to create a Crosstab Query?
https://www.youtube.com/watch?v=RVFgjMDeGaw
Hi ItsMe
Thanks, and its useful when you're dealing with one item.
I'm actually trying to do this for multiple items. I give a slightly modified example to clarify.
Lets say we have table 1 and I want the information for only a specific country (USA) in a modified format.
Is there a way this can be done through a query.
Thanks
I do not work with crosstab queries often. I believe that there are limitations to their uses. Usually the question is how to do the opposite of what you are trying to accomplish. In those instances, a UNION query may be helpful.
If I was asked to present the data the way you are describing, I would look to reports and or forms. Another thing that comes to mind are Charts. At the end of the day, if someone demanded it look exactly like such and such, I might export the data to an external Excel file and then display it in a form via an OLE control. However, you will likely be able to do a similar thing using a subform and datasheet view.
crosstabs can only return one column/value so you need two crosstabs (one for revenue and one for qty). Then a third to combine the two and joining on client
I was wondering about that. So, it is possible to join two crosstab queries?
Thanks Ajax & ItsMe
I ended up creating crosstab queries for each variable (7 in my case). I was able to apply the country filter within each crosstab (I only needed one but it allows multiple filters).
I then joined them all with an 8th table using another query.
A bit tedious (which makes me think there must be a more efficient way, specially when more variables are involved) but dies the job for me.
join two crosstabs (or more) - yes - looks like OP has done that
re alternative ways - a bit tedious to create perhaps but relatively efficient and easy to maintain.
- you could use a pivot table, but that has limited presentation options.
- you could use one query with multiple subqueries (one for each column of final result) but would suggest equally tedious to create and perhaps not as efficient and would need modification for new columns
- create a vba function to return a recordset by looping- more complex, less tedious
Yeah, each time I looked at it, I thought VBA to myself. I also thought pivot table, but I think nowadays that involves using Excel. IIRC, Access is not using that control anymore and Excel has new features for Pivot. So maybe OLE to display Excel or export to a Read Only spreadsheet.