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

 

[AccessVandal via AccessMonster.com]

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

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]

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.

 

[AccessVandal via AccessMonster.com]

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

 

[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).

 

[AccessVandal via AccessMonster.com]

John,

You’re right when you said about the Where criteria. But I’m referring the
row Totals for the Column criteria. Sorry about cutting short on the details.

Usually beginners have problems understanding on how to build a query using
the GroupBy query. So I’m avoiding this for the moment. To use WHERE in fact
to correctly say, is to insert a new column and change the row Totals to
“Where†and the row “Show†is automatically uncheck. Beginners have problems
understanding on this part and including “Expression†becoming a confused lot
when there are no details and explanations.

Since the OP is no longer interested, let’s skip this.