show last date for a material

[rwn]

Below is my SQL for an Access Query, I'm trying to show the last time a pack list ship date was created for each Material. But it shows all.

SELECT Material.Material AS Material, Material_Location.Location_ID AS Location, Sum(Material_Location.On_Hand_Qty) AS Qty
FROM Material INNER JOIN Material_Location ON Material.Material = Material_Location.Material
GROUP BY Material.Material, Material_Location.Location_ID;

 

[r937]

SELECT Material.Material AS Material, Material_Location.Location_ID AS Location, Sum(Material_Location.On_Hand_Qty) AS Qty
[red], MAX(Material.PackListShipDate) AS last_date[/red]
FROM Material INNER JOIN Material_Location ON Material.Material = Material_Location.Material
GROUP BY Material.Material, Material_Location.Location_ID;


r937.com | rudy.ca

 

[rwn]

Thanks. When I use this SQL in the Access and do the RUN, it comes up as ask for the "Enter Parameter Value....Packlist_Header.Packlis_Date" prior to running. I need it to run and show all Last dates for each material.

I hop ethi smakes sense.

 

[r937]

Packlist_Header.Packlis_Date refers to a table which is not mentioned in your FROM clause

r937.com | rudy.ca

 

[rwn]

So sorry, I should have included that SQL thus causing the problem on my end. Here is the SQL that really needs to just show that last packlist header date for each material_location.material.

SELECT Material.Material AS Material, Sum(Material_Location.On_Hand_Qty) AS Qty, Packlist_Header.Packlist, Max(Packlist_Header.Packlist_Date) AS MaxOfPacklist_Date
FROM (Material INNER JOIN Material_Location ON Material.Material = Material_Location.Material) INNER JOIN ((Packlist_Header INNER JOIN Packlist_Detail ON Packlist_Header.Packlist = Packlist_Detail.Packlist) INNER JOIN SO_Detail ON Packlist_Detail.SO_Detail = SO_Detail.SO_Detail) ON Material.Material = SO_Detail.Material
GROUP BY Material.Material, Packlist_Header.Packlist;

 

[r937]

that query looks okay

if it's not returning the correct data, then there's a problem with your table relationships

r937.com | rudy.ca

 

[rwn]

I think I found the issue. It now shows only the last Pack List date for a material. Is there a way to show the sum of the material qty on hand from all locations it resides?

Thanks for the guidence!!

SELECT Max(Packlist_Header.Packlist_Date) AS MaxOfPacklist_Date, Material_Location.Material, Material.Description, Material_Location.On_Hand_Qty, Material.Type, Material_Location.Location_ID
FROM (Material INNER JOIN Material_Location ON Material.Material = Material_Location.Material) INNER JOIN (Packlist_Header INNER JOIN (Packlist_Detail INNER JOIN SO_Detail ON Packlist_Detail.SO_Detail = SO_Detail.SO_Detail) ON Packlist_Header.Packlist = Packlist_Detail.Packlist) ON Material_Location.Material = SO_Detail.Material
GROUP BY Material_Location.Material, Material.Description, Material_Location.On_Hand_Qty, Material.Type, Material_Location.Location_ID
ORDER BY Max(Packlist_Header.Packlist_Date) DESC;

 

[r937]

if i understood your table relationships, i'm sure i could come up with a way

r937.com | rudy.ca