Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

show last date for a material

Status
Not open for further replies.

rwn

Technical User
Joined
Dec 14, 2002
Messages
420
Location
US
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;
 
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
 
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.
 
Packlist_Header.Packlis_Date refers to a table which is not mentioned in your FROM clause

r937.com | rudy.ca
 
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;
 
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
 
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;
 
if i understood your table relationships, i'm sure i could come up with a way

r937.com | rudy.ca
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top