Select X Number of Records

[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]

I would do this with VBA, such that you could loop through the little table,
and create a dynamic SQL statement to perform:

Select TOP [n] from BigTable, LittleTable Where BigTable.ZipCode =
LittleTable.ZipCode

 

[Guest]

That's part of it... but will that require me to enter in my own [n]? I
would like it to read from the column [n] in the small table, because it is a
dynamic number of records that I will be selecting from BigTable. Also,
where do I apply this code, in the SQL view of a query, or in VBA itself?
Would the code read like this with the change:
Select TOP LittleTable.[n] from BigTable, LittleTable Where BigTable.ZipCode
= LittleTable.ZipCode
?

Thanks,
Sean Heckathorne

I would do this with VBA, such that you could loop through the little table,
and create a dynamic SQL statement to perform:

Select TOP [n] from BigTable, LittleTable Where BigTable.ZipCode =
LittleTable.ZipCode

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting

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

 

[Guest]

Mr. Clark,

I've made that changes that you requested, but I'm getting an error message
that reads: "the SELECT statements includes a reserved word or argument name
that is misspelled or missing, or the punctuation is incorrect."

Here is what I have written in SQL view:

SELECT TOP [Test].Orders, [Div-SA_2005-09].ID, [Div-SA_2005-09].Row,
[Div-SA_2005-09].[Carrier DetailCarrier Location Name],
[Div-SA_2005-09].[Origin DetailOrigin Postal Code],
[Div-SA_2005-09].[Destination DetailDestination Postal Code],
[Div-SA_2005-09].[Service Type], [Div-SA_2005-09].[Billed Weight Quantity],
[Div-SA_2005-09].[Ship Date], [Div-SA_2005-09].[Carrier Pro Number],
[Div-SA_2005-09].[Paid Amount]
FROM [Div-SA_2005-09] INNER JOIN Test ON [Div-SA_2005-09].[Destination
DetailDestination Postal Code] = Test.[Dest Zip];

to break down my logic:
1) "SELECT TOP" from the code you provided me, then [Test].orders is where I
want the query to read [n] from in your code. This is the part that access
highlights when it give me the error message.

2) The rest are fields to include in the table

3) FROM the big table, which is joined with small table "test" to link like
zip codes.

Can you tell me what I'm doing wrong?

Thanks,
Sean Heckathorne

I would do this with VBA, such that you could loop through the little table,
and create a dynamic SQL statement to perform:

Select TOP [n] from BigTable, LittleTable Where BigTable.ZipCode =
LittleTable.ZipCode

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting

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