Retrieve "up to #" records in a select query

P

Peddy

I have a select query that I need to retrieve up to 7500 records. When I
sort one field by ascending, it pulls more than 7500 records. Any help is
appreciated!
 
K

KARL DEWEY

Sorting has nothing to do with number of records being pulled in almost all
cases.
Post your query SQL by opening in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.

Post some sample data.
 
F

fredg

I have a select query that I need to retrieve up to 7500 records. When I
sort one field by ascending, it pulls more than 7500 records. Any help is
appreciated!
Click to expand...

Gee, it would have been nice had you included the actual query SQL.
I'll hazard a guess that your query is a Top 7500 query.
If so, and there is a tie for the last position according to the Sort
used, Access will display all of those tied records. How could it know
which of the tied records to display and which to ignore?
 
J

John Spencer

You might post the actual query. Menu: VIEW: SQL, then copy and paste.

GUESSING that you are using the TOP property to limit the number of records
returned. If you want to get NO more than 7500 records, then make your last
sort based on the primary key (or primary keys if multiple tables involved)

SELECT TOP 7500 *
FROM SomeTable
WHERE SomeField Like "A*"
ORDER BY SomeField, SomeTable.PrimaryKey

That should return the first 7500 records unless there are fewer than 7500
records where the field starts with A.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
P

Peddy

Here you go. I guess this is what you are asking for. :) Thanks for your
help.

SELECT TOP 7500 ZipCodeMaster.Priority, ProspectAuditRecords.SequenceNumber,
ProspectAuditRecords.BR, ProspectAuditRecords.Profile,
ProspectAuditRecords.Beacon, IIf([ProspectAuditRecords]![Previous Mailing] Is
Null,"N",[ProspectAuditRecords]![Previous Mailing]) AS [Previous Mailing],
ProspectAuditRecords.BNI, ProspectAuditRecords.JointAccts,
ProspectAuditRecords.Homeowner, ProspectAuditRecords.FinCoAccts,
ProspectAuditRecords.IncomePredictor, ProspectAuditRecords.Zip,
ZipCodeMaster.City
FROM AuditBR, ProspectAuditRecords INNER JOIN ZipCodeMaster ON
(ProspectAuditRecords.BR = ZipCodeMaster.[Branch #]) AND
(ProspectAuditRecords.Zip = ZipCodeMaster.[Zip Code])
WHERE (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="AOJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="BOJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="COJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="AOSY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="BOSY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="COSY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="AOJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing])="Y")) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="BOJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing])="Y")) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="COJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing])="Y")) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="AOSY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing])="Y")) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="BOSY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing])="Y")) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="COSY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing])="Y")) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="BOJN") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="COJN") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="BOSN") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="COSN") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="AOJN") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="AOSN") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="ARJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="BRJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null))
ORDER BY ZipCodeMaster.Priority;

Sample Data:
Priority SequenceNumber BR Profile Beacon Previous
Mailing BNI JointAccts Homeowner FinCoAccts IncomePredictor Zip City
1 47027117082 73 BOSY 597 N 226 0 Y 1 27 37127 Murfreesboro
1 47027670904 73 AOJN 741 N 276 2 Y 0 25 37127 Murfreesboro
1 47024442196 73 AOSN 713 N 289 1 Y 0 23 37127 Murfreesboro
1 47025895317 73 AOJY 711 N 341 2 Y 2 26 37127 Murfreesboro
1 47023417953 73 COJN 575 N 149 2 Y 0 30 37127 Murfreesboro
1 47025955525 73 AOJY 652 N 194 5 Y 2 20 37127 Murfreesboro
 
P

Peddy

You are correct. The sort field is 1 - 39 and the last number it is choosing
is 6 which has tons of records. How do I fix the problem?
 
V

vanderghast

Add the primary key in the ORDER BY clause, at the end of the list of
fields/expression, that will break any 'equality'. If you don't have a
primary key field, use a field with no duplicated values, or, least
prefered, a random value computed using a numerical field as argument:

ORDER BY ..., RND( numericalFieldHere )


Sure, again, if you use the primary key, no need to add such computed
expression.


Vanderghast, Access MVP
 
F

fredg

You are correct. The sort field is 1 - 39 and the last number it is choosing
is 6 which has tons of records. How do I fix the problem?
Click to expand...

There is no problem. Access is returning the records correctly.
Your expectations are incorrect.
Let's assume you have 100 records. Among the 100 are Albert (1
record), Adam (1), Anthony (1) Alan (3), and Allen (1). All the rest
are "B" names or higher.

If you wanted (out of 100 records) only the Top 5 records sorted on
Name in ascending order, the query would correctly return

Adam
Alan
Alan
Alan
Albert

Which are the top 5 records according to the sort and the number of
records for each name. Notice the tie for the second position.

Now if you change the number of names in the records to
Adam (1), Anthony (3) Alan (1), and Allen (1)

The records returned are:
Adam
Alan
Albert
Allen
Anthony
Anthony
Anthony

More than 5 records, true, but that's because there is a tie for the
5th position.

See SQL help on
ALL, DISTINCT, DISTINCTROW, TOP Predicates
 
K

KARL DEWEY

I think the problem is that AuditBR is not joined to the other tables in the
FROM statement. You get a Cartesian effect. The number of records in one
set is multiplied by the number of records in the other set.

You probably need a left join of
FROM AuditBR LEFT JOIN ProspectAuditRecords ON
ProspectAuditRecords.BR=[AuditBR].[Branch] ....
The easy way is to do it in design view by clicking on [Branch] in the
[AuditBR] table and dragging to [BR] of ProspectAuditRecords table. Then
double click the connecting line and selecting option of all records from
[Branch] and only those from [BR] that match.

--
Build a little, test a little.


Peddy said:
Here you go. I guess this is what you are asking for. :) Thanks for your
help.

SELECT TOP 7500 ZipCodeMaster.Priority, ProspectAuditRecords.SequenceNumber,
ProspectAuditRecords.BR, ProspectAuditRecords.Profile,
ProspectAuditRecords.Beacon, IIf([ProspectAuditRecords]![Previous Mailing] Is
Null,"N",[ProspectAuditRecords]![Previous Mailing]) AS [Previous Mailing],
ProspectAuditRecords.BNI, ProspectAuditRecords.JointAccts,
ProspectAuditRecords.Homeowner, ProspectAuditRecords.FinCoAccts,
ProspectAuditRecords.IncomePredictor, ProspectAuditRecords.Zip,
ZipCodeMaster.City
FROM AuditBR, ProspectAuditRecords INNER JOIN ZipCodeMaster ON
(ProspectAuditRecords.BR = ZipCodeMaster.[Branch #]) AND
(ProspectAuditRecords.Zip = ZipCodeMaster.[Zip Code])
WHERE (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="AOJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="BOJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="COJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="AOSY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="BOSY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="COSY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="AOJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing])="Y")) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="BOJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing])="Y")) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="COJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing])="Y")) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="AOSY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing])="Y")) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="BOSY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing])="Y")) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="COSY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing])="Y")) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="BOJN") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="COJN") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="BOSN") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="COSN") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="AOJN") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="AOSN") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="ARJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null)) OR (((ProspectAuditRecords.BR)=[AuditBR]![Branch]) AND
((ProspectAuditRecords.Profile)="BRJY") AND
((ProspectAuditRecords.Beacon)<=750) AND ((ProspectAuditRecords.[Previous
Mailing]) Is Null))
ORDER BY ZipCodeMaster.Priority;

Sample Data:
Priority SequenceNumber BR Profile Beacon Previous
Mailing BNI JointAccts Homeowner FinCoAccts IncomePredictor Zip City
1 47027117082 73 BOSY 597 N 226 0 Y 1 27 37127 Murfreesboro
1 47027670904 73 AOJN 741 N 276 2 Y 0 25 37127 Murfreesboro
1 47024442196 73 AOSN 713 N 289 1 Y 0 23 37127 Murfreesboro
1 47025895317 73 AOJY 711 N 341 2 Y 2 26 37127 Murfreesboro
1 47023417953 73 COJN 575 N 149 2 Y 0 30 37127 Murfreesboro
1 47025955525 73 AOJY 652 N 194 5 Y 2 20 37127 Murfreesboro


KARL DEWEY said:
Sorting has nothing to do with number of records being pulled in almost all
cases.
Post your query SQL by opening in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.

Post some sample data.
Click to expand...
Click to expand...
 
P

Peddy

Thanks to all of your answers. All I had to do was put the sequence number
field in ascending order too. Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a totals per row in query 8
Update Query clearing previous results 3
Recover Outlook calendar entry 1
Sumproduct is returning irregular values 11
Random Function for Selecting Records 6
using "Last" in query desgin 3
Adding data to MS Access from csv file 0
Mail Merge skipping records/sending one at a time 0

Top