Can you open a recordset from another recordset in Access 2003?

[TylerTand]

ultimate goal:
I would like to count the unique subjects where the product purchased is Fish.



I have a query that gets a list of subjects where they have bought a certain product. But in this list there are multiple records of certain subjects. I need to ultimately count the unique subjects from the list.

The only way I know how to do this is first to Select all the subjects where the product = fish.

Then I can do a select from that query results using groupby on the subjects name.

I have 11 products and so I don't want to create 11 new tables to store each products subject list. Is there a way that I can open a recordset and then open another recordset based on the first to do this calculation without creating tables to store the first queries information?
This is what I was thinking:

item = "fish"
set varset=currentdb.openrecordset("Select Subject FROM Customers WHERE Product ='" & item & "';",2)

set vartotal = ("Select count varset!Subject AS FishTotal from varset group by varset!subject;",2)

me![text34]= vartotal!FishTotal

I know that this doesn't work. But this is what I am trying to accomplish.

I would like to count the unique subjects where the product purchased is Fish. If you guys have any ideas I would love to hear them,
Thanks,
Tyler

 

[mearle]

You can't reference one recordset from another, however you can do what you want using a subquery as follows:
SELECT COUNT(Subject) FROM (Select DISTINCT Subject FROM Customers WHERE Product = xxxx)

 

[Pat Hartman]

Or you can just create a query that counts and groups.

Select Product, Count(*) As ProdCount
From YourTable
Group By Product;
This will give you the following:
Cats 196
Dogs 205
Fish 35
Pandas 88