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

  • Thread starter Thread starter Fletcher
  • Start date Start date
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
 
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];
 
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.
 
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]-
,[Delta]) AS Particles

FROM [FSI_Particle_Data]

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

ORDER BY [FSI_Particle_Data].Date DESC;
 
Would sorting by the primary key throw off the sorting by date? If it
does, would it be possible to now sort it?
 
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]-
,[Delta]) AS Particles

FROM [FSI_Particle_Data]

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

ORDER BY [FSI_Particle_Data].Date DESC, [PrimaryKeyField];
 
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]-
,[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...
 
Back
Top