Access 2010 Subquery

[Tlewman]

SELECT [tblData].[Store], [tblData].Category, [tblData].[Item], [tblData].[Velocity], [Data].[sales]
FROM [tblData]
ORDER BY [tblData].[Store], [tblData].Category DESC , [tblData].[52 Week Velocity] DESC;


From this, I want to return the top 5 items per store, per category based on velocity.
I know I need a subquery, but after a couple of unsuccessful attempts am posting in hopes someone can assist.
Thank you!

 

[HiTechCoach]

SELECT [tblData].[Store], [tblData].Category, [tblData].[Item], [tblData].[Velocity], [Data].[sales]
FROM [tblData]
ORDER BY [tblData].[Store], [tblData].Category DESC , [tblData].[52 Week Velocity] DESC;


From this, I want to return the top 5 items per store, per category based on velocity.
I know I need a subquery, but after a couple of unsuccessful attempts am posting in hopes someone can assist.
Thank you!

TL,

You need to include the Primary key field for tblData.

See: Microsoft Access tips: Subquery basics


TOP n records per group

You want the three most recent orders for each client. Use a subquery to select the 3 top orders per client, and use it to limit which orders are selected in the main query:

SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID IN
   (SELECT TOP 3 OrderID                            
   FROM Orders AS Dupe                              
   WHERE Dupe.CustomerID = Orders.CustomerID        
   ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC) 
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;

Points to note:

• Since we have two copies of the same table, we need the alias.
• Like EXISTS in the first example above, there is no problem with the subquery returning multiple records. The main query does not have to show any value from the subquery.
• Adding the primary key field to the ORDER BY clause differentiates between tied values.

 

[Tlewman]

Thank you... I had read this article prior to posting and it did not solve my issue.
I have included a copy of my query below... I had modified slightly. Everything runs fine, but it still returns more than 5 records per store.
Any suggestions?


SELECT T1.[Store Nbr], T1.City, T1.State, T1.Category, T1.[Item Nbr], T1.UPC, T1.[Item Desc 1], T1.[Size Desc], T1.[52 Week Velocity], T1.[POS Qty]
FROM [tblCurrent Week ZS] AS T1
WHERE (((T1.[52 Week Velocity]) In (SELECT TOP 5 [T2].[52 Week Velocity]
FROM [tblCurrent Week ZS] AS [T2 ]
WHERE [T2].[Store Nbr] = [T1].[Store Nbr] AND [T2].[Item Nbr]=[T1].[Item Nbr]
ORDER BY [T2].[52 Week Velocity] DESC, [T2].[Store Nbr]ASC)))
ORDER BY T1.[Store Nbr], T1.Category DESC , T1.[52 Week Velocity] DESC;

 

[HiTechCoach]

TIP: Avoid using spaces in object names. Especially table names and field names.

Hard top spot the issue with knowing more about the databases and the data.

The way this works is that the sun query returns only the primary keys for desired TOP records. The Priamty key for the main query is used to see if is is IN() the list of primary keys returned by the sub query. .

As far as I can till you still are retrieving the primary key field with the sub query. what is the primary key field for the table [tblCurrent Week ZS]

How I do is to first test the sub query's SQL by running it stand alone for a single record.

I would expect your sub query to be something like this:

SELECT TOP 5 [T2].[PRIMARY_KEY_FIELD_NAME_HERE] FROM [tblCurrent Week ZS] AS [T2 ] ...

The main query would use the same primary key field like this:

.. WHERE (((T1.[PRIMARY_KEY_FIELD_NAME_HERE]) In (SELECT TOP 5 [T2].[PRIMARY_KEY_FIELD_NAME_HERE] FROM [tblCurrent Week ZS] AS [T2 ] ...

replace the PRIMARY_KEY_FIELD_NAME_HERE with your actual primary key field name.