Help with TOP 30 in Query?!?!?!

F

Fletcher

Hi, I'm trying to only view the top 30 entries in an access query. I
put the TOP 30 thing in the SQL statement, but it continues to return
34 entries. I don't know why it's doing it.

Also, it returns 34 entries untill you take it down to TOP 20. Then it
returns 24 entries. And that continues all the way down to the TOP 1
entries.

The data sorted descending by date in the table that the query is
running on, so I don't know why it is not working how I want it to.

Does anyone know what the issue might be? Any help is appreciated.

Fletcher
 
D

Duane Hookom

You should have posted your SQL view of your query. I expect returning
duplicate values from your ORDER BY clause. You need to add a unique field
to the ORDER BY like:

ORDER BY [DateField] DESC, [SomeOtherField];
 
A

Allen Browne

If some of the entries are tied, Access cannot distinguish between them, so
it returns all the ties.

To avoid this, add your primary key to the end of the query grid again.
Choose Ascending, and uncheck the Show box.
Since the primary key is unique, this gives Access a way to decide which are
the top 30 when there are tied results.
 
F

Fletcher

Here is my SQL:


SELECT TOP 30 [FSI_Particle_Data].Date,
[FSI_Particle_Data].[FSI_Number], [FSI_Particle_Data].Pre,
[FSI_Particle_Data].Post, [FSI_Particle_Data].Delta,
IIf([delta]=0,[Post]-[Pre],[Delta]) AS Particles

FROM [FSI_Particle_Data]

WHERE ((([FSI_Particle_Data].[FSI_Number])=1))

ORDER BY [FSI_Particle_Data].Date DESC;
 
F

Fletcher

Would sorting by the primary key throw off the sorting by date? If it
does, would it be possible to now sort it?
 
D

Duane Hookom

We were not suggesting that you remove the date field, just add your primary
key to the sorting.

SELECT TOP 30 [FSI_Particle_Data].Date,
[FSI_Particle_Data].[FSI_Number], [FSI_Particle_Data].Pre,
[FSI_Particle_Data].Post, [FSI_Particle_Data].Delta,
IIf([delta]=0,[Post]-[Pre],[Delta]) AS Particles

FROM [FSI_Particle_Data]

WHERE ((([FSI_Particle_Data].[FSI_Number])=1))

ORDER BY [FSI_Particle_Data].Date DESC, [PrimaryKeyField];
 
F

Fletcher

Okay, I didn't understand what was meant at first. I thought that I
would have to sort the primary key ascending or descending and that
wouldn't work with my date sort at the same time. Thank you for
clarifying that. It worked perfectly.
Duane said:
We were not suggesting that you remove the date field, just add your primary
key to the sorting.

SELECT TOP 30 [FSI_Particle_Data].Date,
[FSI_Particle_Data].[FSI_Number], [FSI_Particle_Data].Pre,
[FSI_Particle_Data].Post, [FSI_Particle_Data].Delta,
IIf([delta]=0,[Post]-[Pre],[Delta]) AS Particles

FROM [FSI_Particle_Data]

WHERE ((([FSI_Particle_Data].[FSI_Number])=1))

ORDER BY [FSI_Particle_Data].Date DESC, [PrimaryKeyField];


--
Duane Hookom
MS Access MVP


Fletcher said:
Would sorting by the primary key throw off the sorting by date? If it
does, would it be possible to now sort it?
Click to expand...
Click to expand...
 
Top