If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Report Query Woes
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. -- Michael Conroy Stamford, CT |
#2
|
|||
|
|||
Report Query Woes
"Michael Conroy" wrote in message
... 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. -- Michael Conroy Stamford, CT' 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 |
Thread Tools | |
Display Modes | |
|
|