Top 3 is not working

[jyellis]

I'm working in Access 2010. This query does not work. It returns all the records; not the top three in curFreight field
What am I doing wrong?

[rpeare]

What are you trying to accomplish with this query? Get the top three values of curFreight regardless of customer by the ShipVIA key?

[jyellis]

I'm trying to get the top 3 curfreight within each group of Country/ShipVIa

[rpeare]

This is the test data I used:

tblCustomers

pkeyCustomerID  strCountry
1              Mexico
2              France
3              USA
4              Spain

tblOrders

pkeyOrderID  fkeyShipVia  curFreight  fkeyCustomerID
1            1            500         1
2            2            1000        1
3            3            1500        1
4            4            2000        1
5            1            2500        2
6            2            2000        2
7            3            1500        2 
8            4            1000        2

This was the result I got as from your query

strCountry  fkeyShipVia  curFreight
France      4            1000
France      3            1500
France      2            2000
Mexico      1            500
Mexico      2            1000
Mexico      3            1500

This seems to be giving what you are asking for.

[jyellis]

Below are three tables...the first is raw data. Second is the sorted data just to help you visualize it....the third the rows highlighted are the desired result. The top 3 recent entries in each group. I hope this helps.

[rpeare]

the IN statement of your query is the problem I think you are only taking the top 3 based on the CUSTOMERID when it needs to be based both on the customerID and on the shippedviaID. The SQL statement in that IN clause would need to include the table that has the pkeyShipVia with a valid link statement and field definitions. (sorry I don't have time to look at it more in depth today)

[peterr89]

Hello, get the top three values of curFreight regardless of customer by the ShipVIA key?
http://www.centplay.com/affiliate/id_139/

[rpeare]

SELECT tblOrders.fkeyCustomerID, tblCustomers.strCountry, tblShipVia.ShipViaName, tblOrders.curFreight
FROM (tblOrders LEFT JOIN tblShipVia ON tblOrders.fkeyShipVia = tblShipVia.pkeyShipVia) LEFT JOIN tblCustomers ON tblOrders.fkeyCustomerID = tblCustomers.pkeyCustomerID
WHERE (((tblOrders.curFreight) In (Select Top 3 [curFreight] From tblOrders Where [fkeyshipvia]=[tblshipvia].[pkeyshipvia] AND [fkeycustomerID] = [tblcustomers].[pkeycustomerid] Order By [curfreight] Desc)))
ORDER BY tblCustomers.strCountry, tblShipVia.ShipViaName, tblOrders.curFreight DESC;

I used the same data you showed in three tables

tblShippedVia
pkeyShippedVia
ShipViaName (FedEx, UPS)

tblCustomers
pkeyCustomerID
strCountry (france, england)

tblOrders
pkeyOrderID
fkeyCustomerID
fkeyShippedVia
curFreight

I setup the data to exactly mimic your spreadsheet screen shot and got the results you were after.