Selects show Top 10 records, but shows all records?

[Ultraviolet47]

Hi

I have a query to return only last ten records added, made with wizard:


SELECT TOP 10 Tbl_Sponsors.SponsorID, Tbl_Sponsors.Title,
Tbl_Sponsors.FirstName, Tbl_Sponsors.LastName, Tbl_Sponsors.Ad1,
Tbl_Sponsors.Ad2, Tbl_Sponsors.Ad3, Tbl_Sponsors.Ad4, Tbl_Sponsors.Ad5,
Tbl_Sponsors.Ad6, Tbl_Sponsors.Email, Tbl_Sponsors.Rat,
Tbl_Sponsors.Postal, Tbl_Sponsors.[Welcome Email Sent],
Tbl_Sponsors.[Welcome Pack Sent], Tbl_Sponsors.[Date Joined],
Tbl_Sponsors.[Date Expire]
FROM Tbl_Sponsors
ORDER BY Tbl_Sponsors.[Date Expire] DESC;

Properties shows Top Values=10
Now for some reason, this worked fine before, but now it is returing 92
records, which is even weirder because there is 101 records in the db?
I can't figure out what it's excluding, as
there's no criteria. It is from the right table and getting the right
fields, just returning more than it should?

Any help would be appeciated, thank you!

 

[Pinchas Tishyn]

Does cloning of the query help?

 

[Klatuu]

The TOP predicate will return the number of records requested based on how
the query is ordered except...
If there are fewer records than requested, you will get the number of
records in the underlying table/query.
If there are ties for the TOP based on ordering, it will present all the
ties as 1. So in your case there are a lot of ties in the ordering that make
the records of equal value.

I know you don't have any criteria; however, criteria only play a part in
filtering out unwanted records.

 

[Ultraviolet47]

Hi

As I understand it, it would return the last ten dates details were
entered, as it was ordered by Date of Expiry. So if one of the last ten
dates details were entered has 3 records added, it would return 12
records the way I think it works.

Thanks guys, made me realise that wasn't the best way to do the query,
I have changed it to <DateAdd("d",-30,Date()) to return all records
from the last month, which is probably a better way to return recent
records.

The TOP predicate will return the number of records requested based on how
the query is ordered except...
If there are fewer records than requested, you will get the number of
records in the underlying table/query.
If there are ties for the TOP based on ordering, it will present all the
ties as 1. So in your case there are a lot of ties in the ordering that make
the records of equal value.

I know you don't have any criteria; however, criteria only play a part in
filtering out unwanted records.

Hi

I have a query to return only last ten records added, made with wizard:


SELECT TOP 10 Tbl_Sponsors.SponsorID, Tbl_Sponsors.Title,
Tbl_Sponsors.FirstName, Tbl_Sponsors.LastName, Tbl_Sponsors.Ad1,
Tbl_Sponsors.Ad2, Tbl_Sponsors.Ad3, Tbl_Sponsors.Ad4, Tbl_Sponsors.Ad5,
Tbl_Sponsors.Ad6, Tbl_Sponsors.Email, Tbl_Sponsors.Rat,
Tbl_Sponsors.Postal, Tbl_Sponsors.[Welcome Email Sent],
Tbl_Sponsors.[Welcome Pack Sent], Tbl_Sponsors.[Date Joined],
Tbl_Sponsors.[Date Expire]
FROM Tbl_Sponsors
ORDER BY Tbl_Sponsors.[Date Expire] DESC;

Properties shows Top Values=10
Now for some reason, this worked fine before, but now it is returing 92
records, which is even weirder because there is 101 records in the db?
I can't figure out what it's excluding, as
there's no criteria. It is from the right table and getting the right
fields, just returning more than it should?

Any help would be appeciated, thank you!