create new tables from query on another table.

[Richard U]

I have a table that contains multiple entries for each country in the world.

I need to break this up to one table for each country. I'm very rusty at this. I know what I have to do, just can't remember how.

I know that I need to have a query to return the country codes...

Select distinct Country_Code 
from Country_Table

which will give me the list of countries I need.

I know I need to wrap a statement around it...

Select into NewTable....

But that's where I get lost. I know I need to iterate down the results of the country codes query, but I've forgotten how to do that.

 

[Joe4]

Why do you need a separate table for each country?
Most of the time, if you can get what you want from a query, there is no reason to create a table. And doing so often destroys the dynamic nature of the database.

 

[Richard U]

The individual tables will be exported, deleted, and then never seen again. They will be picked up by Excel, right now the main table is too big to port over to Excel, we are not permitted to use Access.

This is a stopgap measure until we can get SQL Server. I'm taking refresher courses to get back up to speed and just need to get this done for right now, and breaking out these tables is a dependency for the next phase of the project

 

[Norie]

You don't need to create tables to do that, you can just export a query for each country.

 

[Joe4]

we are not permitted to use Access.

You posted this question in the Access forum. If you are not using Access or SQL, what are you using?

The individual tables will be exported, deleted, and then never seen again.

I still think they are unnecessary. You can export your data from a Query just as easily as you can from a Table. As matter as fact, creating and deleting tables can slow down efficiency and bloat database sizes (in Access you need to Compact and Repair the database to keep it from bloating, as deleting records/tables by itself will not reduce the size of the database until you do that).

 

[Richard U]

The main table is over a million records, I'd like to automate it.

 

[Joe4]

The main table is over a million records, I'd like to automate it.

The automation for doing it is actually easier to do from a query than it is from a table, since you do not need to create or delete any objects.
You would just have a query that returns all your different company codes.
Then, in VBA, loop through all the records in this query, and use that to update the SQL Code of the query that you are exporting the records from, export it, and move on to the next one.

Of course, this method I have used in Access and VBA, and you said that you cannot use Access, which leads me back to my previous question, in response to you saying you are not allowed to use Access.

You posted this question in the Access forum. If you are not using Access or SQL, what are you using?

 

[Richard U]

Okay, sorry for any confusion.

We need the data that currently resides in an MS access file:
We are getting SQL Server in a few months
We cannot use MS Access in the final product, but we can use it to spit out the files into Excel
The current MS Access table is far too big for a single tab in Excel
We need to break the big table out into smaller pieces, so that it will fit in Excel
Breaking out by country is the most useful way to do this.

We **Will** be using SQL Server in the final product, but cannot use Access in the mean time.
Orders are to get the info out of access and into Excel until further notice.

There are over 400 country codes.

I would like to know if there is some way to iterate through each distinct country code and then export the information

 

[Joe4]

I would like to know if there is some way to iterate through each distinct country code and then export the information

Yes, by using the technique I described.
I typically do that by incorporating recordsets (I usually use DAO) within VBA, and loop through the records of the query holding the country codes, to build the SQL code to apply to the other query and export.

 

[Richard U]

I'm sorry. I don't understand how to do this, it's been a near fatal stroke and ten years since I've worked on access or Access vba. I can't figure out the looping behavior, so I guess I'm stuck.