[AccessD] Appending in a module

I thought I could do this on my own, but it has been a long time since I
have had a chance to do any programming in Access.  Anyway here is my
problem and I would greatly appreciate any help.  

 

I have 35 tables all with the same basic name except for a number --
dist_35_INDV0607.  The 35 can be anything from 01 to 35.  What I would
like to do is loop through these tables and append 4 fields to a table
that has just those fields in it.  Here is a sample of the code I was
using:

 

    For i = 10 To 35

    DoCmd.RunSQL "INSERT INTO [tblPercentBlack-Hisp] ( Dist, Schol, sid,
race )  SELECT dist_" & i & "_INDV0607.distenrl, dist_" & i &
"_INDV0607.schlenrl_corrected, dist_" & i & "_INDV0607.sid, dist_" & i &
"_INDV0607.Race  FROM dist_" & i & "_INDV0607;  ", -1

    Next i

 

My ultimate goal is to find the percent black and percent Hispanic for
the combined tables.  Some of you may be wondering why I have 35
separate tables.  The reason is that I am working with a flat file
crated in SAS with over 2 million records and 200 fields. I tried
bringing in the whole table and Access balked.

 

It has just been too long since I have done any programming, and I am
having trouble getting back on the bike.  Especially since the bike
version is now 2003 and the last version I used for programming was 97.


 

 

Susan Klos

Evaluation and Reporting

325 W. Gaines Street

Tallahassee, FL  32399

850-245-0708

susan.klos at fldoe.org
 

Please take a few minutes to provide feedback on the quality of service you received from our staff. The Department of Education values your feedback as a customer. Commissioner of Education Jeanine Blomberg is committed to continuously assessing and improving the level and quality of services provided to you.Simply use the link below.  Thank you in advance for completing the survey.
 

http://data.fldoe.org/cs/default.cfm?staff=Susan.Klos@fldoe.org|07:43:04%20Tue%2007%20Aug%202007