spliting up a table into multiple tables

[rpatel18]

I'm trying to split a table into multiple tables. The code I'm using is the following but I keep getting a syntax error in querey expression.

SELECT [2004 Retail].NAIC, [2004 Retail].[ZIP Code], [2004 Retail].[Total Establishments], [2004 Retail].[1-4], [2004 Retail].[5-9], [2004 Retail].[10-19], [2004 Retail].[20-49], [2004 Retail].[50-99], [2004 Retail].[100-249], [2004 Retail].[250-499], [2004 Retail].[500-999], [2004 Retail].[1000 or more] INTO 441120
FROM [2004 Retail]
WHERE (([2004 Retail]![NAIC]="441120 Used car dealers") AND ([2004 Retail]![NAIC]="441120 Used car dealers") AND ([2004 Retail]![NAIC]="441120 Used car dealers") AND ([2004 Retail]![NAIC]="441120 Used car dealers") AND ([2004 Retail]![NAIC]="441120 Used car dealers") AND ([2004 Retail]![NAIC]="441120 Used car dealers") AND ([2004 Retail]![NAIC]="441120 Used car dealers") AND ([2004 Retail]![NAIC]="441120 Used car dealers") AND ([2004 Retail]![NAIC]="441120 Used car dealers") AND ([2004 Retail]![NAIC]="441120 Used car dealers") AND ([2004 Retail]![NAIC]="441120 Used car dealers") AND ([2004 Retail]![NAIC]="441120 Used car dealers"))

SELECT [2004 Retail].NAIC, [2004 Retail].[ZIP Code], [2004 Retail].[Total Establishments], [2004 Retail].[1-4], [2004 Retail].[5-9], [2004 Retail].[10-19], [2004 Retail].[20-49], [2004 Retail].[50-99], [2004 Retail].[100-249], [2004 Retail].[250-499], [2004 Retail].[500-999], [2004 Retail].[1000 or more] INTO 441210
FROM [2004 Retail]
WHERE (([2004 Retail]![NAIC]="441210 Recreational vehicle dealers") AND ([2004 Retail]![NAIC]="441210 Recreational vehicle dealers") AND ([2004 Retail]![NAIC]="441210 Recreational vehicle dealers") AND ([2004 Retail]![NAIC]="441210 Recreational vehicle dealers") AND ([2004 Retail]![NAIC]="441210 Recreational vehicle dealers") AND ([2004 Retail]![NAIC]="441210 Recreational vehicle dealers") AND ([2004 Retail]![NAIC]="441210 Recreational vehicle dealers") AND ([2004 Retail]![NAIC]="441210 Recreational vehicle dealers") AND ([2004 Retail]![NAIC]="441210 Recreational vehicle dealers") AND ([2004 Retail]![NAIC]="441210 Recreational vehicle dealers") AND ([2004 Retail]![NAIC]="441210 Recreational vehicle dealers") AND ([2004 Retail]![NAIC]="441210 Recreational vehicle dealers"))

SELECT [2004 Retail].NAIC, [2004 Retail].[ZIP Code], [2004 Retail].[Total Establishments], [2004 Retail].[1-4], [2004 Retail].[5-9], [2004 Retail].[10-19], [2004 Retail].[20-49], [2004 Retail].[50-99], [2004 Retail].[100-249], [2004 Retail].[250-499], [2004 Retail].[500-999], [2004 Retail].[1000 or more] INTO 441221
FROM [2004 Retail]
WHERE (([2004 Retail]![NAIC]="441221 Motorcycle dealers") AND ([2004 Retail]![NAIC]="441221 Motorcycle dealers") AND ([2004 Retail]![NAIC]="441221 Motorcycle dealers") AND ([2004 Retail]![NAIC]="441221 Motorcycle dealers") AND ([2004 Retail]![NAIC]="441221 Motorcycle dealers") AND ([2004 Retail]![NAIC]="441221 Motorcycle dealers") AND ([2004 Retail]![NAIC]="441221 Motorcycle dealers") AND ([2004 Retail]![NAIC]="441221 Motorcycle dealers") AND ([2004 Retail]![NAIC]="441221 Motorcycle dealers") AND ([2004 Retail]![NAIC]="441221 Motorcycle dealers") AND ([2004 Retail]![NAIC]="441221 Motorcycle dealers") AND ([2004 Retail]![NAIC]="441221 Motorcycle dealers"))

SELECT [2004 Retail].NAIC, [2004 Retail].[ZIP Code], [2004 Retail].[Total Establishments], [2004 Retail].[1-4], [2004 Retail].[5-9], [2004 Retail].[10-19], [2004 Retail].[20-49], [2004 Retail].[50-99], [2004 Retail].[100-249], [2004 Retail].[250-499], [2004 Retail].[500-999], [2004 Retail].[1000 or more] INTO 441222
FROM [2004 Retail]
WHERE (([2004 Retail]![NAIC]="441222 Boat dealers") AND ([2004 Retail]![NAIC]="441222 Boat dealers") AND ([2004 Retail]![NAIC]="441222 Boat dealers") AND ([2004 Retail]![NAIC]="441222 Boat dealers") AND ([2004 Retail]![NAIC]="441222 Boat dealers") AND ([2004 Retail]![NAIC]="441222 Boat dealers") AND ([2004 Retail]![NAIC]="441222 Boat dealers") AND ([2004 Retail]![NAIC]="441222 Boat dealers") AND ([2004 Retail]![NAIC]="441222 Boat dealers") AND ([2004 Retail]![NAIC]="441222 Boat dealers") AND ([2004 Retail]![NAIC]="441222 Boat dealers") AND ([2004 Retail]![NAIC]="441222 Boat dealers"))

SELECT [2004 Retail].NAIC, [2004 Retail].[ZIP Code], [2004 Retail].[Total Establishments], [2004 Retail].[1-4], [2004 Retail].[5-9], [2004 Retail].[10-19], [2004 Retail].[20-49], [2004 Retail].[50-99], [2004 Retail].[100-249], [2004 Retail].[250-499], [2004 Retail].[500-999], [2004 Retail].[1000 or more] INTO 441229
FROM [2004 Retail]
WHERE (([2004 Retail]![NAIC]="441229 All other motor vehicle dealers") AND ([2004 Retail]![NAIC]="441229 All other motor vehicle dealers") AND ([2004 Retail]![NAIC]="441229 All other motor vehicle dealers") AND ([2004 Retail]![NAIC]="441229 All other motor vehicle dealers") AND ([2004 Retail]![NAIC]="441229 All other motor vehicle dealers") AND ([2004 Retail]![NAIC]="441229 All other motor vehicle dealers") AND ([2004 Retail]![NAIC]="441229 All other motor vehicle dealers") AND ([2004 Retail]![NAIC]="441229 All other motor vehicle dealers") AND ([2004 Retail]![NAIC]="441229 All other motor vehicle dealers") AND ([2004 Retail]![NAIC]="441229 All other motor vehicle dealers") AND ([2004 Retail]![NAIC]="441229 All other motor vehicle dealers") AND ([2004 Retail]![NAIC]="441229 All other motor vehicle dealers"))

SELECT [2004 Retail].NAIC, [2004 Retail].[ZIP Code], [2004 Retail].[Total Establishments], [2004 Retail].[1-4], [2004 Retail].[5-9], [2004 Retail].[10-19], [2004 Retail].[20-49], [2004 Retail].[50-99], [2004 Retail].[100-249], [2004 Retail].[250-499], [2004 Retail].[500-999], [2004 Retail].[1000 or more] INTO 441310
FROM [2004 Retail]
WHERE (([2004 Retail]![NAIC]="441310 Automotive parts & accessories stores") AND ([2004 Retail]![NAIC]="441310 Automotive parts & accessories stores") AND ([2004 Retail]![NAIC]="441310 Automotive parts & accessories stores") AND ([2004 Retail]![NAIC]="441310 Automotive parts & accessories stores") AND ([2004 Retail]![NAIC]="441310 Automotive parts & accessories stores") AND ([2004 Retail]![NAIC]="441310 Automotive parts & accessories stores") AND ([2004 Retail]![NAIC]="441310 Automotive parts & accessories stores") AND ([2004 Retail]![NAIC]="441310 Automotive parts & accessories stores") AND ([2004 Retail]![NAIC]="441310 Automotive parts & accessories stores") AND ([2004 Retail]![NAIC]="441310 Automotive parts & accessories stores") AND ([2004 Retail]![NAIC]="441310 Automotive parts & accessories stores") AND ([2004 Retail]![NAIC]="441310 Automotive parts & accessories stores"))

SELECT [2004 Retail].NAIC, [2004 Retail].[ZIP Code], [2004 Retail].[Total Establishments], [2004 Retail].[1-4], [2004 Retail].[5-9], [2004 Retail].[10-19], [2004 Retail].[20-49], [2004 Retail].[50-99], [2004 Retail].[100-249], [2004 Retail].[250-499], [2004 Retail].[500-999], [2004 Retail].[1000 or more] INTO 441320
FROM [2004 Retail]
WHERE (([2004 Retail]![NAIC]="441320 Tire dealers") AND ([2004 Retail]![NAIC]="441320 Tire dealers") AND ([2004 Retail]![NAIC]="441320 Tire dealers") AND ([2004 Retail]![NAIC]="441320 Tire dealers") AND ([2004 Retail]![NAIC]="441320 Tire dealers") AND ([2004 Retail]![NAIC]="441320 Tire dealers") AND ([2004 Retail]![NAIC]="441320 Tire dealers") AND ([2004 Retail]![NAIC]="441320 Tire dealers") AND ([2004 Retail]![NAIC]="441320 Tire dealers") AND ([2004 Retail]![NAIC]="441320 Tire dealers") AND ([2004 Retail]![NAIC]="441320 Tire dealers") AND ([2004 Retail]![NAIC]="441320 Tire dealers"))


and oh yeah....thanks for all the help I've gotten on this forum, I love the prompt replies that I keep getting. THANX GUYS! AND GALS!

 

[Crowley16]

can you run those insert statements individually so we can see which one is causing problems?

--------------------
Procrastinate Now!

 

[rpatel18]

they all run fine individually but i dont feel like running 74 individual queries.

 

[Crowley16]

you have 74 queries like that?

it could be because the query is too complicated...

would it not be possible to setup individual queries, then just use a simple loop to run all the queries?

--------------------
Procrastinate Now!

 

[rpatel18]

all of them aren't the same, they are slightly different. I have 74 subcategories in one table, and I need to split all those 74 subcatogories into their own tables.

 

[Crowley16]

it shouldn't matter that the queries aren't the same, as long as they are stored as individual queries, then they'll run fine...

access will try to parse any saved queries and will re-format it automatically, however this does mean if the query is too large, then access will have trouble.

if you create a set of queries, with a prefix of say, "split_", then you can easily use a function to loop through all your queries and run them individually, that way access won't get confused...

--------------------
Procrastinate Now!

 

[PHV]

I just wonder why repeating the same criteria on NAIC in each make table query ?

 

[rpatel18]

I just used the build querey feature in access and edited it. I have limited knowledge about access and sql. I'm only a intern and a college sophmore trying to compile databases and create reporting tools for the company I work for.

 

[PHV]

I need to split all those 74 subcatogories into their own tables
Why ?
You may simply use ONE parametized query to get all the data of each subcatogory you want, so again, WHY ?

 

[rpatel18]

how would I loop 74 different queries?

 

[rpatel18]

well, each subcat has all the zip codes in the US with certain information regarding that zip code. All the other data has a primary key of zipcodes, splitting the table apart would let me link to those sub cats.

 

[PHV]

So, seems you need only TWO tables ...
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Crowley16]

you can loop through queries using various methods, like looping through the querydefs collection, or using a recordset to bring back the relevant queries from MSysObjects, or just hard coding in a set of query names...

I'd suggest using the querydefs collection since that's the easiest to learn...

something like:

dim qry as variant
for each qry in currentproject.querydefs
if qry.name like "split_*" then
qry.execute
endif
next qry

this code is not tested!

--------------------
Procrastinate Now!

 

[rpatel18]

I dont see how I would only need two tables with 74 categories all containing 30,000+ zip codes. The 74 categories also have to be linked to IRS data and census data both with the primary key of zipcodes.

 

[PHV]

So, again, read this:

http://r937.com/relational.html

and this:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rpatel18]

so should I just use a one to many relationship?

 

[PHV]

Probably.
Parent table with ZipCode as PK and child table with (ZipCode,SubCat) as combined PK and Zipcode as FK.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rpatel18]

So I can just keep the table with all the 74 cats in them(column 1) and make the zip code primary on that table(column2) and just make a one to many relationship? If this is the case, i feel like an idiot then....lol