Automating report construction from queries

[arthurpenske]

I have a very large query that includes order data for about 100 clients. There are two smaller queries that take data from this one (to group by a "job code" and by a "job category"). I'm looking for a way to automate the process of running the series of three queries and producing an output (table or report) that gives the relevant data I need. I want to run this process for all ~100 clients, so I don't want to manually edit each query and copy and paste the results every time, but I'm having trouble creating a macro that will accomplish this. Any advice would be greatly appreciated.

[June7]

I don't use macros, only VBA, so any code help I offer would be VBA. I don't really understand the issue. Do you want to provide project for analysis? Without knowing your data structure and relationships hard to advise. You should be able to design a report that would group and sort the data by clients and print so each client starts a new page. Don't know why you have two queries but if this is necessary, are the queries relatable?

[arthurpenske]

the two queries are very similar (one organizes by a job code number, and one by the type of job), and both sum the hours but it would be nice to have both included.

the data structure at this level is pretty simple, my real problem is being able to easily change the WHERE clause in both queries to run both ~100 without having to update each clause individually, and then having that data automatically entered into a report. I think VBA code would work better than a macro, but I'm not quite sure how to write something like this.

[June7]

I still don't have enough information to advise. I need to see data, SQL statements, table relationships. I don't know why you need to 'manually edit each query and copy and paste the results'. The copy and paste part really baffles me. What exactly has to be changed? Information can be entered on a form and queries can reference the form as criteria source.

[rpeare]

As june said without an example database the code you are going to get is very generic and not suited to your project (you'd have to adapt it to your tables/queries etc) but I suspect I would not use your existing queries at all but build the query that runs the reports you want to export in the VB script and change the RECORD SOURCE of the report when it goes to the printer. Here's an example that takes a portion of a larger query using a date criteria and exports a separate file for each invoice. and each invoice date. Look at the data before you attempt to print as well if you only want to print one page choose 1/1/2011 or 1/3/2011.

[arthurpenske]

Thanks for the sample database. I can put up a similar database that is more similar to mine in a few hours, but for now I'll go down a different line of thought. If I wanted to create that same type of report that you did, but instead of entering the range of dates each time in a form, I wanted the data to come automatically from a table where each record was a range of dates.

[arthurpenske]

I still don't have enough information to advise. I need to see data, SQL statements, table relationships. I don't know why you need to 'manually edit each query and copy and paste the results'. The copy and paste part really baffles me. What exactly has to be changed? Information can be entered on a form and queries can reference the form as criteria source.

By copy and paste I mean copy and paste the resulting tables into excel, but I gather there is a better way to do this using reports (I don't have much experience using reports- I'm trying to figure all that out today).

[rpeare]

If I wanted to create that same type of report that you did, but instead of entering the range of dates each time in a form, I wanted the data to come automatically from a table where each record was a range of dates.

Why do you have a table with date ranges in it? is my first question. If you want to generate a list of dates that you had orders on that can be done from existing tables most likely with no need to generate a date list table. (shooting in the dark here because I don't exactly know what you're getting at)

Reports can be exported to Excel and so can Queries. Either way if you want to export data to individual files for individual customers/orders/whatever it can be done. with the same type of methodology, but I would likely do it a bit differently than you started to.

[arthurpenske]

Why do you have a table with date ranges in it? is my first question. If you want to generate a list of dates that you had orders on that can be done from existing tables most likely with no need to generate a date list table. (shooting in the dark here because I don't exactly know what you're getting at)

Reports can be exported to Excel and so can Queries. Either way if you want to export data to individual files for individual customers/orders/whatever it can be done. with the same type of methodology, but I would likely do it a bit differently than you started to.

sorry for not clarifying- i only used the example of a date list table in reference to the file arthurpenske.mdb, as a substitute for entering the values into the form.

How would you go about that?

[rpeare]

I don't understand the question. There is only one date in my database, that's the Order date. Though I've not built any forms this is typically something that would be entered by a data entry person at the time an order is established. You can set the default value to date() in your table and suppress that field from your data entry if you don't want to enter it but I'm not entirely sure what you're asking for. Are you asking for the start and end date to default to the most recent order date or something?

[dreday]

sorry for not clarifying- i only used the example of a date list table in reference to the file arthurpenske.mdb, as a substitute for entering the values into the form.

How would you go about that?

If you want to use values from a table in your SQL statement use DLookup