Tricky table format

[rakhasa]

OK, its Friday pm and I've probably fried my brain which why I can't see how to do this:


Creating a new table in an existing DB to hold data on different groups of people's training profiles. I've got upwards of 15 different groups and they need to be split further down by trained < 5 yrs, trained < 1 yr, trained in last year and not trained.

That part's easy enough.

However, these groups of people are also to be further divided by grand total in the country, total in each organisation below that, and total at each site below that.

All very clear in Excel but not so much when I try to create this table.

Anyone any thoughts on how to go about creating this and/or subdividing?

 

[rakhasa]

To make things clearer, I mean that if the data is on an Excel sheet it has headers across the top signifying &quot;grand total in the country, total in each organisation below that, and total at each site below that&quot;, and also has row titles for the various groups of people.

 

[TnGranny]

Create three SELECT queries from your new table that group employees by:
1) country
2) country and organization
3) country, organization and site
Make COUNT as one of the output fields in these queries.

Create another query that joins the new table with these 3 queries. COUNT is the number of employees in each group.

Specifics...

SELECT query #1 will output 2 fields, GROUP BY Country and COUNT Organization (or any field in the new table that cannot be null). Then this query will be joined to the new table by Country.

SELECT query #2 will output 3 fields, GROUP BY country, GROUP BY organization, and COUNT of site (Or any field in the new table that cannot be null). This query will be joined to the new table by country AND organization.

SELECT query #3 will output 4 fields...