SQL within DCount()

[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

 

[Douglas J. Steele]

Sorry, but there's no way to put a SQL statement into a DCount statement.

 

[tina]

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

 

[John W. Vinson]

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));

I'd change the HAVING to WHERE, since the criteria don't involve aggregation.

 

[david]

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;

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)

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

 

[John W. Vinson]

But the problem with the query editor is that when if or when you use GroupBy,
you can't use WHERE.

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