One report to list out different queries with same fields?

[iht]

Say I have a database with types of car driven by people living in different cities.

I made several queries to separate out the database according to city, then made queries to count out numbers of cars. So what I have now are queries those tally different car types in each city (simplified version):

LAQuery
[Total] [NumHonda] [NumToyota] [NumFord] [NumBMW]
50 5 10 15 20

NYQuery
[Total] [NumHonda] [NumToyota] [NumFord] [NumBMW]
10 1 2 3 4

SFQuery
[Total] [NumHonda] [NumToyota] [NumFord] [NumBMW]
20 2 4 6 8

What I want as result is a report that will report number of different cars driven in each city in Columnar form:

[Type of Cars] [LA] [NY] [SF]
Total 50 10 20
Honda 5 1 2
Toyota 10 2 4
Ford 15 3 6
BMW 20 4 8


Currently the only way I could figure out is to write a union query for all the fields, then report the query by itself:

SELECT [Total], [NumHonda], [NumToyota], [NumFord], [NumBMW]
from [LAQuery]
SELECT [Total], [NumHonda], [NumToyota], [NumFord], [NumBMW]
from [NYQuery]
SELECT [Total], [NumHonda], [NumToyota], [NumFord], [NumBMW]
from [SFQuery]
ORDER by [Total];
 

But this code gets rather messy when you have more than 10 fields in each query (I guess it's all copy and paste, but still).

Can Access pull data straight from each query and just report it in columnar form? Or union query is the best thing I can do about this?

Another question is, if I have to do it by union query, how can I code it so it'll list records in specific orders, i.e. Always list LA, NY, SF from top to bottom instead of lowest number on top?
(In the original database the city and car names has some other info attached behind it so I can't really order just by names only.)

Thanks in advance for your help.

[NeoPa]

A Crosstab query may help you here.
It would probably be more sensible looking at the source data though, before being split up into separate datasets (by your other queries).
Can you give an example of your queries.
I have to say I'm not very hot on Crosstab queries but I'm sure someone will have an answer for you.
I will look when you've posted but I can't promise I can help.
Also, can you confirm that the lists of cites and makes are foreknown (So their names can be hard-coded in rather than being produced dynamically)?

[iht]

There were 2 databases originally where one has demographic info and other has make & model of car reported for each record, joined by an ID field as primary key.

2nd database has model of car reported under fields of different car makes.

I made some first queries (LA, SF, NY) separating the records according to different cities (LA only has info for ID's in LA, etc.), then based on that query I made a query to tally the numbers of a specific make.

i. e. for LAQuery I did:

SELECT Count([ID]) AS Total, 
Sum(IIf([Honda] is not null,1,0)) AS NumHonda,
Sum(IIf([Toyota] is not null,1,0)) AS NumToyota,
Sum(IIf([Ford] is not null,1,0)) AS NumFord,
Sum(IIf([BMW] is not null,1,0)) AS NumBMW
FROM [LA];
 

Cities are foreknown, unfortunately they want to be able to add car makes to the original database so it may have to be produced dynamically....

Thanks for the help.

[MMcCarthy]

There were 2 databases originally where one has demographic info and other has make & model of car reported for each record, joined by an ID field as primary key.

2nd database has model of car reported under fields of different car makes.

I made some first queries (LA, SF, NY) separating the records according to different cities (LA only has info for ID's in LA, etc.), then based on that query I made a query to tally the numbers of a specific make.

i. e. for LAQuery I did:

SELECT Count([ID]) AS Total, 
Sum(IIf([Honda] is not null,1,0)) AS NumHonda,
Sum(IIf([Toyota] is not null,1,0)) AS NumToyota,
Sum(IIf([Ford] is not null,1,0)) AS NumFord,
Sum(IIf([BMW] is not null,1,0)) AS NumBMW
FROM [LA];
 

Cities are foreknown, unfortunately they want to be able to add car makes to the original database so it may have to be produced dynamically....

Thanks for the help.

What NeoPa is suggesting is that if you tell us the structure of the original data then we could probably suggest a way of creating one table with all the data in it rather than separate tables for each city.

Mary