Running the same 8 queries but making a different set of tables each time based on 1 diff criteria

[Emkretsch]

Hello- Unfortunately, I understand very little about VBA (copy, paste, & run- is the extent of my knowledge), but I’ve been trying to find a solution to my issue by using Queries, Macro’s or Forms. It may be impossible to do without VBA, but figured I’d ask. I’m working in Access 2013.



I have an Access DB where I’ve created queries that do everything I need. What I’m doing works, but I feel there has to be a better/quicker way. Here is the data I’m working with:

-I have 10 company divisions.
-8 manufacturers in each Division.

-At this point, I am queried down to my final 8 make tables (1 for each Manufacturer), with the results being a number of customer names & part #’s (which change each time I run the report), with the identifying manufacturer name & Division #, as 2 of the fields.

-I have all of my queries in a macro to pull the top 20 records, for 8 manufacturers, (1 division at a time- see below)based on a total $ amount, and a total count of items. This is where my question comes into play….

Currently, to keep from having 80+ additional queries, I pull each Division’s final top 20 data, with a form I created with a dropdown button where I can pick which Division I want to use as the criteria in the final 8 make table queries. That table data is then exported to the corresponding Excel report (as tables) for reporting purposes. This was the only way I knew to do it. The problem is, I have to open the Access DB, open the form, pick the Division, run the macro to create the 8 tables for 1 division, close the DB, open the Excel Report, refresh the data, & save it. Then repeat the whole process for each of the 10 divisions & reports.

There has GOT to be a more efficient way to do this….I hope? ANY help or ideas are very much appreciated!!!

[aytee111]

This sounds like something that should be done from Excel, not Access. And those queries you have, they do not need to be make-table queries, just plain select queries. The data will look the same - but maybe there is some other reason? Then there would be a refresh button in Excel which pulls the latest data from Access.

Or doing it in Access, you would write a VBA routine to read thru the divisions and export the queries to Excel one at a time, in a loop.

[CJ_London]

not sure why you have a table for each manufacturer - I would expect one table with an additional column to indicate the manufacturer

[ssanfu]

Does it have to be exported to Excel?
Couldn't you create the reports in Access, and save the reports as PDFs?

[Micron]

Yes, more info on why the interaction with Excel. If it's for charting, then I agree because IMHO, Access charting stinks. Making so many tables doesn't sound right - especially over and over. Let's see a pic of your table relationships if you set them. Will tell a lot about your design. Read your post at least 2x and wonder if you have the requisite table structure. Even if you do, there shouldn't be a need for make table queries beyond first use i.e. for fast table design. After that, append and delete, not recreate. That's a recipe for bloat at best, and corruption at worst. Might even be that you should be linking Excel back to Access for charting. If not charts, the maybe even Access reports are good enough. Based on your limited info, I can envision a report as being able to present the info you seem to need to assemble.