Query doesn't take records with the oldest Date

[Fede]

Hello everyone,

I am writing for show my issue with a query.

I have two tables : "Order" & "Stock".

Table Order have "Code Number"

Table Stock have "Code Number", "Serial Number", "Date"


I'd like to create a query that takes the serial number that belong to the Code Number, and specifically the serial number with the date of entry oldest.


To be more clear than serial number belongs to a code number.

I already tried to do the query but it returns more than one serial number for the same code; i try to input filter but the query doesn't return the serial numbers corresponding to other code number in the Orders table

TY
Fede

 

[boostbyslinky]

Can you post the "SQL" code for your query?

 

[Mr Smin]

Something like

Select Top 1 SerialNumber from TableStock
Where TableOrder.CodeNumber = TableStock.CodeNumber
Order By Date DESC;

Caution: Above may be faulty and/or contain syntax errors so double check and test before going with it!
The idea is that it takes the oldest item with a given CodeNumber.

Also consider: If your serial numbers are truely serial and numeric then similar approach but taking the lowest serial value for a given CodeNumber should help you to use up old stock first.

 

[Fede]

Thank you both.

Unfortunately, the serial numbers do not correspond to the oldest part, so I can only use the date recorded.

Now I try to use what you've written, and I'll let you know

Here I report the code that I wrote but that does not return only a serial number corresponding to a code number.
It returned for a Code Number restoring all associated serial number.

But put them in order, from oldest to most recent date.

SELECT DISTINCT Stock.[Code Number], Stock.[Serial Number], Min(Stock.[Date]) AS [MinOfDate]
FROM [Order] INNER JOIN Stock ON [Order].[Code Number] = Stock.[Code Number]
GROUP BY Stock.[Code Number], Stock.[Serial Number]
ORDER BY Min(Stock.[Date]);



TY

 

[Fede]

Nobody can give me good advice?
I write below is an example maybe understand me better.
Basically I have to apply the logic of stock FIFO (First in First Out)

Table Orders :
Part Number
A
B
A
C



Table Stock :
Part Number - Serial Number - Date
A - 1 - 25/03/2011
A - 2 - 25/02/2011
A- 3 - 25/01/2011
B- 25 - 14/02/2011
B - 11 - 18/07/2010
C - 45 - 11/09/2010
D - 12 -11/08/2009


the query should return :

A - 3 - 25/01/2011
A - 1 - 25/03/2011
B - 11 - 18/07/2010
C - 45 - 11/09/2010



and instead returns :

A - 1 - 25/03/2011
A - 2 - 25/02/2011
A - 3 - 25/01/2011
B - 25 - 14/02/2011
B - 11 - 18/07/2010
C - 45 - 11/09/2010



I hope someone can help me

Thanks to all