Parameter in a subquery?

[scottydel]

Hello,

I've got two questions:

1) Can you use a parameter in a subquery?

2) Can the parameter value be the value from another field in the query?

In a nutshell, I want to write a query that returns these fields about some marbles:

Color of Marble
Marble Stat1
Marble Stat2
Marble Stat3
Number of Marbles of this Color

i.e. desired query results

Blue Stat1 Stat2 Stat3 24
Red Stat1 Stat2 Stat3 12
Green Stat1 Stat2 Stat3 38

The query is fine except for the last field, Number of Marbles of this Color. This field is a subquery with a parameter that is the color of the marble. So if I run the subquery on its own and pass 'blue' as the parameter, it returns 24.

Here is the SQL for the subquery, qry_Count_Marbles:

SELECT Count([Marble_ID]) AS [Count]
FROM tbl_Marble_Info
WHERE (((tbl_Marble_Info.COLOR)=[Marble_Color]));

Marble_Color is a parameter, and If I enter 'blue' it returns 24.

In my main query, I am trying to pass the parameter for the subquery as the result of the first field. It's not working, but here is my SQL:

SELECT tbl_Marble_Guidelines.COLOR, tbl_Marble_Guidelines.LOW, tbl_Marble_Guidelines.HIGH, tbl_Marble_Guidelines.FUND, qry_Count_Marbles.Count(tbl_Marble_Guidelines.COLOR)
FROM tbl_Marble_Guidelines, qry_Count_Marbles


I'd like the main query to not prompt the user for the parameter, but simply grab the parameter value from the value of the first field. Is what I'm trying to do possible? Is there a way to pass a SQL parameter within the SQL itself, and not prompt the user?

Any ideas?

Thanks,

Scott

 

[Dennisk]

one possible solution is to use a table of colours and use that instead of the parameter by using a left outer join to join the colour table to your totals query.