How to create a looping macro that will generate multiple tables

[nalbi]

I'm trying to generate multiple tables using a query based on the unique SiteID, I have 32 sites in total and I would like to generate a table for each site (which I eventually would like to export all together into a single excel workbook).

So currently the query I've set up looks like this:
SELECT [Updated ANPR Analysis Site ID].New_SiteID AS Site_ID, [Updated ANPR Analysis Site ID].Base_class AS Class, [Updated ANPR Analysis Site ID].Corrected_vrn AS RegNo, [Updated ANPR Analysis Site ID].Timestamp AS [Time] INTO Site17
FROM [Updated ANPR Analysis Site ID]
WHERE ((([Updated ANPR Analysis Site ID].New_SiteID)=17))
The variable bits are obviously the Site_ID. Help!

[twinnyfo]

nalbi,

Welcome to Bytes!

A few things:

First, when posting Code or SQL strings, please use the proper Code Tags in your formatting.

Second, I think you will want to use VBA instead of a macro to accomplish this.

Third, what have you tried to actually create these Tables on your own. We are glad to troubleshoot your problems, but we are not in the habit of creating solutions.

Fourth, as I read your post, I would recommend against creating separate Tables for each of your sites. You would be better off creating one table, with the Site ID as one of the Fields. This is simply better DB structure.

Obviously, based on what you provided, our understanding of your problem is a bit limited. However, we are glad to provide guidance along the way to move you toward a solution.

Hope this hepps!

[nalbi]

Hi twinny,

my knowledge of vba/macros is extremely limited, I've been browsing similar topics/questions asked here but couldn't find an answer that I thought fitting.

I've been creating those tables manually by running the query over and over and changing the criteria to suit the SiteId I'm after. Unfortunately I need separate tables for each site for an independent bit of comparative work in excel. I was hoping I can automate this process as much as possible, but I don't know where to start!

[twinnyfo]

nalbi,

You can still (I believe) accomplish what you are trying to do using one Table.

Theoretically, your code would reflect the following processes:

1. Identify the list of Site IDs
2. Import the data for each Site ID into the Table

There is no need to create new tables each time you import data. If the Table structure remains in tact, this saves on creating, recreating and deleting tables, which is a process that easily and quickly creates bloated DBs which can crash easily.

To export each Site ID to a spreadsheet, the processes are similar:

1. Identify the list of Site IDs
2. Export the data for each Site ID into a spreadsheet

This is not your solution, but guidance as to how to approach this solution. This does require knowledge and understanding of VBA, but you will benefit from the advantages of learning about proper DB design and execution.