updating values in a subform

[getrighteous]

I have a main form where users can select a variety of selections via checkboxes, comboboxes, etc. The results of these selections are then collected into a single SQL statement, the database is queried, and results are displayed in a subform via setting the .RecordSource = strSQL. Right now, the results that are displayed are counting transactions by individuals per state. So for example (this is simplified greatly for sample purposes!):

Data:
Cust ID Prod ID State
1 A NJ
2 A CA
3 B NJ
4 C HI
1 B NJ
2 C CA

Results:
State Count
CA 2
HI 1
NJ 3


I need to change my SQL statement so that the results are a count of distinct individuals, not transactions. The results would then be:

Results:
State Count
CA 1
HI 1
NJ 2

Ideally, I'd use something like:

Select Count(Distinct [Cust ID]), State
From tblTrans
Group By State

... but Access doesn't seem to like Count and Distinct used in this manner (I have another db application that actually does like this, and returns the results I need).

So, to make a short story a bit longer, I'd like to do one of two things:

1) Change the SQL statement to count the distinct customers

OR

2) Do it in 2 steps -- select distinct [Cust ID], state... then cycle through the results and accumulate the counts. The problem I have here is that I would have to change the subform update from .Recordsource to some other format, and I'm not sure how to do this.

Sorry for the lengthiness, hope someone can help.

 

[getrighteous]

OK, not too elegant a solution, but what I figured I could do here is simply create a saved query that would return distinct Cust ID's and pass parameters from the form to it. Then, in my code, I could create my recordset directly from this query, and tally the counts from there.

Problem now is that I am getting "Run-time error '3061'. Too few parameters. Expected 2."

SavedQuery1 (example):
Select Distinct [Cust ID], [State]
From tblTrans
Where ([State] In ([Forms]![frmPUB2]![varSt]))
Group By [Cust ID], [State]

(varSt = "'CA','HI','NJ'&quot

Then in code:
strSQL = "Select [State], Count([Cust ID]) From SavedQuery1 " & _
"GROUP BY [State];"

But I get the too many parameters error. If I turn on the immediate window, and copy my strSQL above into a new query, it works perfectly.

Anyone? Anyone?