SQL within DCount()

  • Thread starter Thread starter Steve S
  • Start date Start date
S

Steve S

The following gives the correct result but I would like to include the SQL in
the DCount() instead of using a stored query

x = DCount("Event", "Query25") result is 2

query25 :
SELECT Fees.Event
FROM Fees
GROUP BY Fees.ContestID, Fees.Event, Fees.Triathlon
HAVING (((Fees.ContestID)=[Forms]![Menu2]![ContestID]) AND
((Fees.Triathlon)=True));

I have tried several versions of the SQL but always get errors.
and maybe there is a better way. is there a key work or function in DAO
that would show the number of hits for a select query

Any and all help is appreciated
 
i can't think of any way to GroupBy directly in a domain aggregate function;
if you need to group the events listed for a specific ContestID before
counting the records, then i think you're going to have to run the DCount on
the Totals query, as you're doing now.

hth
 
Since you're using the GroupBy query why not..

SELECT Count(Fees.Event) As EventCount
FROM Fees
GROUP BY Fees.ContestID, Fees.Event, Fees.Triathlon
HAVING (((Fees.ContestID)=[Forms]![Menu2]![ContestID]) AND
((Fees.Triathlon)=True));
Click to expand...

I'd change the HAVING to WHERE, since the criteria don't involve aggregation.
 
But note that it is possible to go halfway:

x =
Dcount("Event","Group_Query","((Fees.ContestID=[Forms]![Menu2]![ContestID])
AND (Fees.Triathlon=True))"

where
Group_Query:
SELECT Fees.Event
FROM Fees
GROUP BY Fees.ContestID, Fees.Event, Fees.Triathlon;
Click to expand...


or, if you really need it, you can write your own Dcount function.
I've done that to cache results, because a Dcount like this is
with a "group by" query is really slow if you use it inside another query.

(david)


Steve S said:
The following gives the correct result but I would like to include the SQL
in
the DCount() instead of using a stored query

x = DCount("Event", "Query25") result is 2

query25 :
SELECT Fees.Event
FROM Fees
GROUP BY Fees.ContestID, Fees.Event, Fees.Triathlon
HAVING (((Fees.ContestID)=[Forms]![Menu2]![ContestID]) AND
((Fees.Triathlon)=True));

I have tried several versions of the SQL but always get errors.
and maybe there is a better way. is there a key work or function in DAO
that would show the number of hits for a select query

Any and all help is appreciated
Click to expand...
 
But the problem with the query editor is that when if or when you use GroupBy,
you can't use WHERE.
Click to expand...

That is incorrect. Select Where as the "totals" operator, and uncheck the Show
checkbox (if it doesn't do so automatically).
 
Back
Top