Top N subquery with unique records (1 Viewer)

[Indigo]

Hoping someone can help me out with this one. I am using Access 2010 and trying to create a query to show the last records for 12 unique foreign keys in a table.

I am tracking sealer inventory for 12 pumps in a shop. Each pump can be changed multiple times in a week. I need a query that will show the last sealer barrels on these 12 pumps. I thought that a Top N subquery would do the trick, but I am not getting the results I need:

SELECT t.InventoryID, t.PumpID, t.SealerType, t.ExpiryDate, t.LotNo
FROM Inventory AS t
WHERE (((t.[PumpID]) In (SELECT TOP 12 PumpID
FROM Inventory
WHERE PumpID = t.PumpID
ORDER BY InventoryID DESC, PumpID)))
ORDER BY t.ExpiryDate, t.InventoryID DESC , t.PumpID;

The Inventory table records the sealer inventory changes. The Pump Table has the list of the 12 Sealer pumps in the shop.

Can anyone offer some advice on how I can get the desired results? Am I over thinking this? Thank you.

 

[jdraw]

I suggest you show readers some sample data; and from that sample show the expected output you want.
We don't know about pumps and sealer barrels, so tell us in very simple terms --plain English-- and show us the samples.

What was the result that you "didn't desire"? What should they be?

Good luck.

 

[Indigo]

The results I am getting are:

InventoryID PumpID SealerType ExpiryDate Lot
13 12 A 05-06-2016 123
12 11 A 05-12-2016 114
11 10 B 05-31-2016 145
10 9 B 05-17-2016 115
9 8 C 05-13-2016 005
8 7 C 05-14-2016 421
7 6 A 05-12-2016 114
6 5 A 05-22-2016 541
5 4 D 05-18-2016 345
4 3 D 06-01-2016 221
3 2 C 05-05-2016 985
15 7 C 04-30-2016 621
2 1 C 05-01-2016 443
14 1 C 05-07-2016 378

I do not need the duplicate records for pumps 1 and 7 and need to see only the latest records for these pumps.

 

[jdraw]

Indigo,

You are showing the result which is a summary of records. You haven't given a clear description of what you are trying to accomplish-- you material is still jargonese. Try simple English.

I am using Access 2010 and trying to create a query to show the last records for 12 unique foreign keys in a table.

What exactly do you mean by last 12 unique foreign keys?

You also mention Pump Table

The Pump Table has the list of the 12 Sealer pumps in the shop.

but Pump table isn't in your query SQL --was it suppose to?

If you can't tell us in plain English what a sealer barrel is and how it relates to SealerType, and what exactly you are trying to accomplish, then I suggest you try harder.

We are offering to help, but we need some input from you.
What is the raw data in your inventory table?

 

[Indigo]

Okay, I will try to make this clearer. The inventory table is used to track the sealer that comes in barrels and is used in the shop at the company I work for. We get several different types of Sealer and there are 12 pumps in the shop to which these sealer barrels are put on. The Sealer has a short life and we are trying to set up a system in a database to monitor and ensure that the sealer barrels are changed out prior to expiration.

There is a pump table with the list of the 12 pumps in the shop. There is a one-to-many relationship between this table and the Inventory table. The inventory table also has an auto number primary key field, a Sealer type field (text), an expiration date/time field and a lot number field for the barrels (text).

I am trying to generate a query that will show the last inventory records for the 12 pumps as I only want to see the current inventory for the 12 pumps. I do not want to simply see the last 12 records in the inventory table because that may not include all 12 pumps as expiration dates will vary from sealer type to sealer type.

So I need a query that will only generate one record each, the last record, for the 12 pumps. Twelve records total.

 

[jdraw]

??
Here is a query that will show the Expiry Date of each SealerType and Lot

SELECT DISTINCT PumpSealer.SealerType & PumpSealer.Lot AS [SealerTypeAndLot], PumpSealer.expiryDate
FROM PumpSealer
WHERE ((([PumpSealer].[SealerType] & [PumpSealer].[Lot]) In (SELECT  PumpSealer.SealerType & PumpSealer.Lot 
FROM PumpSealer INNER JOIN PumpSealer AS PumpSealer_1 ON (PumpSealer.SealerType = PumpSealer_1.SealerType) AND (PumpSealer.Lot = PumpSealer_1.Lot )
Group by PumpSealer.sealerType & pumpsealer.lot)))
 ;

 

[Indigo]

thank you for your assistance but this is still not what I am looking for.

 

[jdraw]

this is still not what I am looking for.

Ok, then show readers the details and some sample data showing before records and the expected results.

 

[Indigo]

I have continued to dig and think I have found what I need... but initially had a little trouble with the syntax.

I found this link: http://www.geeksengine.com/article/get-single-record-from-duplicates.html

and the query is what I want but it's for an Oracle database. I have managed to get it to work in Access:

SELECT a.InventoryID, b.PumpID, b.Type, a.ExpiryDate
FROM Inventory AS a
INNER JOIN Pump as b on a.PumpID=b.PumpID
WHERE (((a.InventoryID In (SELECT MAX([InventoryID]) FROM Inventory
GROUP BY PumpID)))
ORDER BY b.PumpID;

and I get the results that I want.

 

[jdraw]

I created a Pump table and updated my sql based on your latest post

SELECT a.InventoryID, b.PumpID, a.SealerType, a.ExpiryDate
FROM PumpSealer AS a
INNER JOIN Pump as b on a.PumpID=b.PumpID
WHERE a.InventoryID In 
				(SELECT MAX([InventoryID]) FROM pumpSealer
				GROUP BY PumpID)
ORDER BY b.PumpID;

and get the following result

InventoryID	PumpID	SealerType	  ExpiryDate
14	1	C	07/05/2016
3	2	C	05/05/2016
4	3	D	01/06/2016
5	4	D	18/05/2016
6	5	A	22/05/2016
7	6	A	12/05/2016
15	7	C	30/04/2016
9	8	C	13/05/2016
10	9	B	17/05/2016
11	10	B	31/05/2016
12	11	A	12/05/2016
13	12	A	06/05/2016

Hope it's helpful.