Running same report based on multiple queries

[sureshmopl]

Hi all, I have more than 100 select queries in my db. I needs to run a report with same design, separately for each queries. How do I do it without running the report one by one? I can run these queries together and get the output in separate files with the following sample code :

DoCmd.OpenQuery "101"
DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="101", OutputFormat:=acFormatHTML, Outputfile:="H:\Work\Employees\101.html"
DoCmd.Close acQuery, "101", acSaveNo

But reports give me more refined output than DoCmd.OutputTo method. Please help. Thanks

 

[Uncle Gizmo]

See this link HERE:-

https://sites.google.com/site/msacc...iple-copies-of-report-with-different-criteria

Sent from my SM-G925F using Tapatalk

 

[Minty]

If you have a 100 similar queries outputting the same basic information, It sounds as if you haven't organised your data correctly, as you should be able to simply create one query and filter the report to the result you want.

 

[sureshmopl]

If you have a 100 similar queries outputting the same basic information, It sounds as if you haven't organised your data correctly, as you should be able to simply create one query and filter the report to the result you want.

These queries are based on separate values from the same field. For example, its filtering the data based on employee code. So I need separate reports for separate employees based on these queries.

 

[Minty]

No you don't, simply return all the records, then create a filtered report for each employee.
You can then loop around the same report for each employee based on whatever criteria you want. The logic is something like (pseudo code - not real!)

[COLOR="green"]'Create a recordset with all the employees in that have a record in your base query;[/COLOR]
SET RS = "SELECT EMpID From YourQuery Group By EMpID"
[COLOR="Green"]'Then simply loop through the recordset outputting the report [/COLOR]

While Not RS.EOF

   docmd.OpenRreport "yourReport" ,,, "[Empid] =" rs.Fields("EmpID")
   docmd.output acReport, youreport
   Close "yourreport"   
RS.MoveNext   [COLOR="Green"]' Goto to the next employee[/COLOR]
Wend

So you have 1 report and 1 query. If you need to generate a different report based on a different query, you could create a form with them in a list, and feed in the report and query as parameters - making the whole thing very flexible.

 

[sureshmopl]

Thank you. Am not that good at VBA code but will try the methods you have suggested.

 

[Minty]

This is an ideal project to learn the basics. It's not too complicated and diving in with a specific task to achieve is often the best way.
Start with the basics, create the basic query and then report you need, with all the data displayed for every employee, then get into the filtering, then finally the automated output.
One step at a time.