Report Query Woes

[Guest]

I need help with SQL syntax to combine two queries and use a criteria string
to limit the results and feed this all into a report. The criteria is
determined by options the user selects on the reports form, and often looks
like this

Criteria = "([BusinessID] = 1) AND ([PartyID] = 2) AND ([Sent]

=#01/01/2006#) AND ([Sent] <= #04/10/2007#) AND ([StatusID] <> 5)"

Now I usually run the report query with the criteria filtering it down to
the proper selection, then I run a summary query with the report one as the
source to total the items by each manager. This seems wrong because the first
query sits on the screen while the other works off of it.

What I would like to do is combine the two queries into one with the
criteria statement in there somewhere. Having relied on the graphic queries,
my sql skills are lacking. But my guess is something like this.

SELECT [qry Report].CRegionID, [qry Report].CRMFull, Count([qry
Report].CRMEID) AS CountOfCRMEID
FROM SELECT [qry Report].* FROM [qry Report] HAVING Criteria;
GROUP BY [qry Report].CRegionID, [qry Report].CRMFull;

I am not sure about the syntax for the having field.

Also, can you use one query inside another as I suggested above?

This is basically a summary query where the data is filtered and the items
are totaled for each manager.
The final question is I would like to have this in all in VB with all the
steps right there, like this.

strSQL = the query stuff goes here
DoCmd.OpenReport [Report], acViewPreview, , RecordSource = strSQL
but I am not sure about the syntax for the recordsource part

I apoligize for such a long question, but I have used workarounds to get
this to work in the past, and now the reporting features are getting more
complex and I need to organize it better. Any help you can give me would be
great.

 

[Carl Rapson]

I need help with SQL syntax to combine two queries and use a criteria
string
to limit the results and feed this all into a report. The criteria is
determined by options the user selects on the reports form, and often
looks
like this

Criteria = "([BusinessID] = 1) AND ([PartyID] = 2) AND ([Sent]

=#01/01/2006#) AND ([Sent] <= #04/10/2007#) AND ([StatusID] <> 5)"

Now I usually run the report query with the criteria filtering it down to
the proper selection, then I run a summary query with the report one as
the
source to total the items by each manager. This seems wrong because the
first
query sits on the screen while the other works off of it.

What I would like to do is combine the two queries into one with the
criteria statement in there somewhere. Having relied on the graphic
queries,
my sql skills are lacking. But my guess is something like this.

SELECT [qry Report].CRegionID, [qry Report].CRMFull, Count([qry
Report].CRMEID) AS CountOfCRMEID
FROM SELECT [qry Report].* FROM [qry Report] HAVING Criteria;
GROUP BY [qry Report].CRegionID, [qry Report].CRMFull;

I am not sure about the syntax for the having field.

Also, can you use one query inside another as I suggested above?

This is basically a summary query where the data is filtered and the items
are totaled for each manager.
The final question is I would like to have this in all in VB with all the
steps right there, like this.

strSQL = the query stuff goes here
DoCmd.OpenReport [Report], acViewPreview, , RecordSource = strSQL
but I am not sure about the syntax for the recordsource part

I apoligize for such a long question, but I have used workarounds to get
this to work in the past, and now the reporting features are getting more
complex and I need to organize it better. Any help you can give me would
be
great.

You don't embed queries that way. Instead, just refer to [qry Report] as if
it were a table:

SELECT [qry Report].CRegionID, [qry Report].CRMFull, Count([qry
Report].CRMEID) AS CountOfCRMEID
FROM [qry Report]
HAVING Criteria
GROUP BY [qry Report].CRegionID, [qry Report].CRMFull;

Your HAVING clause would look just like the Criteria string.

For the report, you can't change the RecordSource that way. The third
parameter of the OpenReport method is WhereCondition, which takes the place
of the WHERE clause in a SQL statement.

Here's what I would suggest: base your report on [qry Report], without the
Criteria. Pass the criteria to the report in the WhereCondition parameter.
Do your grouping and aggregate functions in the report itself rather than in
the query.

Carl Rapson