trying to run multiple queries(slightly different)

[rpatel18]

I'm trying to run multiple make table queries each with are slightly different(just the new table name and the criteria from the Sector column are different). The SQL code in access is

SELECT ZB1999.ZIP, ZB1999.PNAC, ZB1999.TOT_ESTB, ZB1999.EMP1_4, ZB1999.EMP5_9, ZB1999.EMP10_19, ZB1999.EMP20_49, ZB1999.EMP50_99, ZB1999.E100_249, ZB1999.E250_499, ZB1999.E500_999, ZB1999.EMP1000 INTO [99 Sector 62]
FROM ZB1999
WHERE (([ZB1999]![SECTOR]="62----") AND ([ZB1999]![SECTOR]="62----") AND ([ZB1999]![SECTOR]="62----") AND ([ZB1999]![SECTOR]="62----") AND ([ZB1999]![SECTOR]="62----") AND ([ZB1999]![SECTOR]="62----") AND ([ZB1999]![SECTOR]="62----") AND ([ZB1999]![SECTOR]="62----") AND ([ZB1999]![SECTOR]="62----") AND ([ZB1999]![SECTOR]="62----") AND ([ZB1999]![SECTOR]="62----") AND ([ZB1999]![SECTOR]="62----"));

which works fine. I want to create this statement about 30 times and run them all at once. I dont want to create 30 different saved queries because there are 5-6 years too, 1999, 2000, 2001. etc..

The VB code I'm trying, not to familiar is the following(just a test)....

DoCmd.RunSQL = "SELECT ZB1999.ZIP, ZB1999.PNAC, ZB1999.TOT_ESTB, ZB1999.EMP1_4, ZB1999.EMP5_9, ZB1999.EMP10_19, ZB1999.EMP20_49, ZB1999.EMP50_99, ZB1999.E100_249, ZB1999.E250_499, ZB1999.E500_999, ZB1999.EMP1000 INTO [99 Sector 62]" & _
"FROM ZB1999" & _
"WHERE ZB1999.[SECTOR] = '62----' And ZB1999.[SECTOR] = '62----' And ZB1999.[SECTOR] = '62----' And ZB1999.[SECTOR] = '62----' And ZB1999.[SECTOR] = '62----' And ZB1999.[SECTOR] = '62----' And ZB1999.[SECTOR] = '62----' And ZB1999.[SECTOR] = '62----' And ZB1999.[SECTOR] = '62----' And ZB1999.[SECTOR] = '62----' And ZB1999.[SECTOR] = '62----' And ZB1999.[SECTOR] = '62----';"

but I get a new macro window then.
anyhelp will be greatly appreciated.

 

[lark_tilly]

Take a look at the attached. It works by having two tables where you put the years(1999,2000,etc) and the Sectors(62----,etc). The code iterates through each table and creates the SQL to make each table using the possible variations provided by the Year and Sector table values. Right now the years are 1999 & 2000 and the sectors 62---- and 63---. If this will work for you all you'll have to do is update those values and the code will do everything else. ZB1999 and ZB2000 have limited fields. We can flesh that out if you think this will work. To use it, open the form and click the button. The tables will be created. You will have to manually delete the created tables before each test at this time. I didn't put any code in there to do that but it can be done if that is needed. Also, if you have the table tab as the current tab when you run this, you'll have to click on some other tab and then click on the table tab again so Access refreshes the view and you can see the tables. Take a look and see if this looks like it will work.