Use Field as Input

[Guest]

I have a file with 2 columns. One is a column of zip codes, and the other is
a column with random numbers in it. I would like to run a query that matches
up the zip code in this small file, with zip codes in a large database to
pull out the matching zip codes. The catch is that I can only pull out the
number of records that the column of random numbers state. For example:

[n] zip code
1 13027
2 02115
3 94561

....Draw links between zip code field in reference file and large database
and return:
13027
02115
02115
94561
94561
94561

The large database contains more records than the random number column
states. Basically I need to write a query that says "Pick out [n] number of
records that match [zip code]"

Any help would be appreciated.

Thank you,
Sean Heckathorne

 

[[MVP] S.Clark]

In VBA, I would create a procedure that opens the small table and loops
through it and dynamically creates a sql statement, for each record, to
append the desired records, from the large table, to a temp table.

For example:

dim rs as recordset
set rs=db.openrecordset("SmallTable")
with rs
do while not .eof
strSQL = "INSERT INTO ztblZips SELECT TOP " & !n & _
" * from LargeTable WHERE zip = '" & !ZipCode & "'"
db.execute strSQL
.movenext
loop
end with
rs.close
set rs=nothing