Queries within a Query

  • Thread starter Thread starter Bungle bonce
  • Start date Start date
B

Bungle bonce

I have number of countof queries that I have to enter into a parameter query
so the user only has to enter the parameter once, and I can then create a
report from it.

1 Countof Blue users
2 Countof Red users
3 Count of Green users

Placed into the parameter query. The first 3 queries all come off one field
in a table. If the field has data showing a value for each of criteria, eg 2
Blue, 3 Red, 4 Green, the parameter query runs OK, but if for instance there
are no Greens the parameter query shows no data for any of the others as
well.

Where am I going wrong?
I have searched the net for weeks and tried various default settings on the
field, and null value commands in the queries, but nothing works.

Please Help.
Joe.
 
Joe,
Could you post your query's sql? Open your query in
design view, then click on view|sql view. This will show
the sql. Copy that into a post.
 
SELECT Count(userquery.Origin) AS CountOfOrigin, userquery.Origin
FROM userquery
GROUP BY userquery.Origin
HAVING (((userquery.Origin)="white"));

This works fine on it's own, but when it's included in the parameter query
things like this with other queries the problems start.

SELECT qryWhite.CountOfOrigin, qryAsian.CountOfOrigin,
qryBlack.CountOfOrigin
FROM qryWhite, qryAsian, qryBlack;

I am counting the ethic origin of users of my centre.

Thanks Les.
Joe.
 
I wonder if a query like this, instead of your 3 separate
querys would help you?

SELECT Table.ethnicity, Count(Table.ethnicity) AS
CountOfethnicity
FROM Table
GROUP BY Table.ethnicity;

It gives you the ethnicity, and the count for each. Then,
if you wanted to select a specific ethnicity, you could
create a parameter query and use the above as input.

If I misunderstood what you are trying to do, please
explain further.
 
It is possible that you could do these counts in one query. This depends on
your table structure.

Something like the following MIGHT work for you.

SELECT Abs(Sum(Origin="White")) as WhiteCount,
Abs(Sum(Origin="Asian")) as AsianCount,
Abs(Sum(Origin="Black")) as BlackCount
FROM YourTable
 
Les said:
I wonder if a query like this, instead of your 3 separate
querys would help you?

SELECT Table.ethnicity, Count(Table.ethnicity) AS
CountOfethnicity
FROM Table
GROUP BY Table.ethnicity;

It gives you the ethnicity, and the count for each. Then,
if you wanted to select a specific ethnicity, you could
create a parameter query and use the above as input.

If I misunderstood what you are trying to do, please
explain further.






the parameter query
Click to expand...
Thanks Les.

I ended up using a Union Select within the SQL and then Crosstabbing
it to get the headings correct.
EG
SELECT COT,T,S
FROM qryAsian
UNION SELECT COT,T,S
FROM qryBasicSkills
UNION SELECT COT,T,S
FROM qryBlack

COT is count of type (expression)
T is type
S is a filler because the Xtab needs 3 fields.

It runs Ok now, persistence is a great virtue EH.

Thanks Again.
Joe.
 
Back
Top