crosstab output

[gsrai31]

Hi there, I have a table with thousands records in Access 2003 database. I want to be able summarise data from 2 fields. These are Country and Durations. I want to count number of durations for each country. I have attached a spreadsheet with some dummy data and an output table that I would like to populate or create in a crosstab format. I want to have columns and rows even if there is no data in the table. So the structure of the table stays same. One of the columns has to be populated with count of all durations where these are greater than 12. Output table in the attached spreadsheet should make it clear what I am trying to achieve. Hope someone can help me with this. Thanks in advance. gsrai31

 

[Alansidman]

Look at the attached. I only did 0-5 and 12+. Based upon this you should be able to figure out how to complete it. You need four queries. One is set up as a manually created cross tab (query 4) and the second one (Query5) uses the cross tab as a record source for the totalling by country. Next you need to create a query to total by fields, (Query 6).
Last, you need to create a Union query (final query) to join the Query6 and Query5 so that you have the detailed counts and the totals.

good luck.

 

[gsrai31]

That is brilliant. You are a star.
Thank you very much for your help.

 

[Alansidman]

You are welcome.
Alan