Stacking Queries

[dubs]

Hi all,

Im trying to stack some queries which will produce a table of totals...i have around 40 of these so would like these to populate the same table... i could write all 40 out separately and use another query to join them but would much rather stack the queries together so i dont have to create 40 very similar queries, ive tried adding the query to the bottom of the previous one separting by a semi colon but get an error message 'Characters found after sql statement' so have tried to take the semicolon away and try other characters like +, And etc but then i get a syntax error:

SELECT Count(census.Id) AS 1985m
FROM census
WHERE (((census.DOB)>=#09/01/1985# AND(census.dob)<=#8/31/1986#)) and ((Sex)="Male")

WHAT WOULD GO HERE TO TELL ACCESS TO RUN THE NEXT QUERY?

SELECT Count(census.Id) AS 1985f
FROM census
WHERE (((census.DOB)>=#09/01/1985# AND(census.dob)<=#8/31/1986#)) and ((Sex)="Female");

is there a way to stack these queries together or do i have to run 40 separate queries?

greg

 

[Norie]

Why do you have to write seperate queries?

Can't you just use a totals query where you group by sex and year?

The SQL would look something like this:

SELECT Year([DOB]) AS Year, census.Sex, Count(census.Sex) AS CountOfSex
FROM census
GROUP BY Year([DOB]), census.Sex;

 

[dubs]

cheers ive cracked it using union

greg