How to incorporate SQL in OutputToQuery

[RobinHood786]

Hi,

I want to reduce the number of queries I have in my database as the whole thing is looking a little bloated. (In excess of 70 queries.) Previously I’ve been using queries to as the source for exporting data to excel which works well e.g.

DoCmd.OutputTo acQuery, "Qry_CaseloadCount", "MicrosoftExcel(*.xls)", "", True

The SQL for the above example query looks like this;

SELECT Caseload.Caseworker, Count(Caseload.ProjectID) AS CountOfProjectID
FROM Caseload
GROUP BY Caseload.Caseworker;

I was wondering how to incorporate this SQL code within the OutputTo acQuery. I suspect I’ll have to do something like;

DoCmd.RunSQL "SELECT Caseload.Caseworker, Count(Caseload.ProjectID) AS CountOfProjectID " & _
"FROM Caseload " & _
"GROUP BY Caseload.Caseworker"

But I’m stuck on how to I replace the "Qry_CaseloadCount" part with this code. (If indeed that’s the way I should be approaching the issue).

All thoughts welcome.

 

[PHV]

Have a look at QueryDef

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RobinHood786]

Thanks for the pointer PHV. Having looked through the help files and doing a ggogle search I'm still a little lost. Well quite a bit lost to be honest.

Could you possibly elaborate for me.