Select only Latest entries

[aladrach]

I have a database full of Time Entries, linked to other tables, to show all information in one location. The "Time Table" stores the "Part Number", which is linked to "ItemRef_FullName" in the other table. When I enter an Order Number, I want it to return a list of parts, with only the latest time entry for each part. However, currently, it returns all the parts with all the entries for each part. I'm sure it's something simple, I just can't seem to figure it out. Here's my SQL code:

SELECT dbo_salesorderlinedetail.ClassRef_FullName, dbo_salesorderlinedetail.ItemRef_FullName, Min([Time Table].[Start Time]) AS [MinOfStart Time], dbo_salesordera.RefNumber, dbo_salesorderlinedetail.Desc, dbo_salesorderlinedetail.Quantity, dbo_salesorderlinedetail.Invoiced, [dbo_salesorderlinedetail].[Quantity]-[Invoiced] AS Expr1, First([Time Table].[Part Number]) AS [FirstOfPart Number], [Time Table].Location, [Time Table].Quantity
FROM (dbo_salesordera INNER JOIN dbo_salesorderlinedetail ON dbo_salesordera.TxnID = dbo_salesorderlinedetail.IDKEY) LEFT JOIN [Time Table] ON dbo_salesordera.RefNumber = [Time Table].[Order Number]
GROUP BY dbo_salesorderlinedetail.ClassRef_FullName, dbo_salesorderlinedetail.ItemRef_FullName, dbo_salesordera.RefNumber, dbo_salesorderlinedetail.Desc, dbo_salesorderlinedetail.Quantity, dbo_salesorderlinedetail.Invoiced, [dbo_salesorderlinedetail].[Quantity]-[Invoiced], [Time Table].Location, [Time Table].Quantity
HAVING (((dbo_salesordera.RefNumber)=[Order Number?]) AND ((dbo_salesorderlinedetail.Desc) Is Not Null And (dbo_salesorderlinedetail.Desc)<>"Discount Applied") AND ((dbo_salesorderlinedetail.Quantity) Is Not Null) AND ((First([Time Table].[Part Number]))=[ItemRef_FullName]))
ORDER BY dbo_salesorderlinedetail.ClassRef_FullName, Min([Time Table].[Start Time]), dbo_salesorderlinedetail.Desc;

Thanks for your help!

 

[Bryan]

Take a look at THIS THREAD. It addresses the same issue.

 

[aladrach]

I see that thread, but can't seem to make it work in my scenario...

 

[Bryan]

I think what I was suggesting in general is that you have to MAX the [Time Table] so that you only get the latest record and then JOIN (probably LEFT) that to everything else you've got going on in that query.

 

[Brianwarnock]

Bryan is correct the principle is the same whenever you are attempting this sort of selection, however I notice that you select the earliest start time in your query, I suspect that the reason that that is not effective is that your grouping is so extensive that each record is rendered unique.

You need to use max and part number only to get the latest part number request and then take it from there.

Brian

 

[aladrach]

Bryan is correct the principle is the same whenever you are attempting this sort of selection, however I notice that you select the earliest start time in your query, I suspect that the reason that that is not effective is that your grouping is so extensive that each record is rendered unique.

You need to use max and part number only to get the latest part number request and then take it from there.

Brian

Well what am I doing wrong? I tried to set it up like that and still getting multiple entries:

SELECT Max([Time Table].[Start Time]) AS [MaxOfStart Time], [Time Table].[Order Number], dbo_salesordera.IsFullyInvoiced, [Time Table].[Part Number], dbo_salesorderlinedetail.ItemRef_FullName, dbo_salesorderlinedetail.Desc, [Time Table].Location, [Time Table].Quantity
FROM ([Time Table] LEFT JOIN dbo_salesordera ON [Time Table].[Order Number] = dbo_salesordera.RefNumber) LEFT JOIN dbo_salesorderlinedetail ON dbo_salesordera.TxnID = dbo_salesorderlinedetail.IDKEY
GROUP BY [Time Table].[Order Number], dbo_salesordera.IsFullyInvoiced, [Time Table].[Part Number], dbo_salesorderlinedetail.ItemRef_FullName, dbo_salesorderlinedetail.Desc, [Time Table].Location, [Time Table].Quantity
HAVING ((([Time Table].[Order Number])=[Order Number?]) AND ((dbo_salesorderlinedetail.ItemRef_FullName)=[Part Number]) AND (([Time Table].Quantity) Is Not Null));

EDIT: I see. Any guidance on how to set that up and still receive the same results?

 

[aladrach]

Got it now. 2 Queries: one query with Order Number, Start Time, and Part Number, with MAX on Start Time, and the rest in the other query.

 

[aladrach]

OK so now that I have the proper items selected, I would like it to give me a SUM of those items with "SHIPPED" in the location field of the "Time Table". Any way to do this?

 

[Bryan]

Have never tried this, but you might be able to use DSUM using a Shipped = criteria.