Create New Table

[coley]

Hi

Is there a quick way to split up a table into many other tables? I want to split the table by Client Number, so all the Clients with 1234, for example, are in a table called Client1234

I know the very long winded way, creating a ‘Create New Table’ query, inputting the Client Code criteria each time.

Is there a better more efficient way?

Thanks

coley

 

[Pat Hartman]

This is a very bad idea. What are you trying to accomplish?

 

[coley]

i agree its a bad idea, but that is not the point, it still needs to be done as its what the 'Business' wants. Seperate tables, that can then be exported.

So is there a quick way of doing this, as i have over 100 tables to create?

 

[Fizzio]

It is still a bad idea as you can export the data via parameter queries. Just use either a lookup table to identify which record groups you want to filter on or hard code it. A lookup table would be better.

 

[modest]

Is there a quick way to split up a table into many other tables? I want to split the table by Client Number, so all the Clients with 1234, for example, are in a table called Client1234

Bad Idea.

I know the very long winded way, creating a ‘Create New Table’ query, inputting the Client Code criteria each time.

Is there a better more efficient way?

What the heck does that mean? Do you want Access to already have it done? The database can't just read your mind and split certain records into different tables.

However, if I were you, I'd set up a one-field table with all the Client Numbers that you want to pull out. Loop through the list and use a Create Table query against your main table that matches that temporary one-field table.

Example:

Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDB
Set rs = db.OpenRecordset("Your Temp Table", dbOpenDynaset)

rs.MoveLast
Do Until rs.BOF
    strSQL = "SELECT [Main Table].FieldName, [Main Table].[Other Field Name] INTO [CLIENT" & rs.Fields(0).Value & "] FROM [Main Table]"
    db.Execute strSQL
    rs.MovePrevious
Loop

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

It should work, but didn't test it so there might be syntax errors. If you want to do it for all records, to make the Temp Table, use a query:

SELECT DISTINCT [Client ID Field] INTO [Temp Table] FROM [Main Table]

 

[KenHigg]

Why can't you export query results?