Display items by location by most recent date

[majordog]

Hi all,
Hopefully someone can help me. I have a table called Inventory. Within this table I have descriptive information. Connected to this table is the Location table. The location table can have mulitple entries for one item. The way to filter is by the most recent date. IE/ as items are moved, the user determines where its current location is by the entry with the most recent date. I know I have to somehow use a formula to get this to work but I am stumped! I tried to set a formula that would calculate what the max date was:
Maximum ({Bottle_Location.Date}) AND {Bottle_Location.BottleID} = {Bottle_Inventory.BottleID}
But the above is incorrect. To tell you the truth I am not sure how to organize this to make sense. If you even know how I should approach this problem theoretically, please comment!

Below I am including the SQL query as it stands now, this returns all the locations, with the bottles at that location - The problem is, it doesn't check the dates inthe Bottle_location table and so just displays the bottle at the first location it hits - :

SELECT Gas_Types."Name",Bottle_Inventory."BottleID", Bottle_Inventory."Date Received", Bottle_Inventory."Bar Code", Bottle_Inventory."Calibration Bottle", Bottle_Inventory."Full", Bottle_Inventory."InService", Bottle_Inventory."Pressure",BottleNominalConcentration."Global Nominal Con", ERMD_Locations."Name"
FROM
{ oj ((("ERMD"."dbo"."Gas_Types" Gas_Types INNER JOIN "ERMD"."dbo"."Bottle_Inventory" Bottle_Inventory ON
Gas_Types."GasID" = Bottle_Inventory."Gas_ID&quot
INNER JOIN "ERMD"."dbo"."BottleNominalConcentration" BottleNominalConcentration ON Bottle_Inventory."NominalConcentrationID" = BottleNominalConcentration."BottleID&quot
INNER JOIN "ERMD"."dbo"."Bottle Location" Bottle_Location ON
Bottle_Inventory."BottleID" = Bottle_Location."BottleID&quot
INNER JOIN "ERMD"."dbo"."ERMD_Locations" ERMD_Locations ON
Bottle_Location."Loc_ID" = ERMD_Locations."LocationID"}
WHERE
Bottle_Inventory."Full" = 1
ORDER BY
ERMD_Locations."Name" ASC,
Bottle_Inventory."Bar Code" ASC

 

[pgtek]

hi try this
SELECT Gas_Types."Name",Bottle_Inventory."BottleID", Bottle_Inventory."Date Received", Bottle_Inventory."Bar Code", Bottle_Inventory."Calibration Bottle", Bottle_Inventory."Full", Bottle_Inventory."InService", Bottle_Inventory."Pressure",BottleNominalConcentration."Global Nominal Con", ERMD_Locations."Name"
FROM
{ oj ((("ERMD"."dbo"."Gas_Types" Gas_Types INNER JOIN "ERMD"."dbo"."Bottle_Inventory" Bottle_Inventory ON
Gas_Types."GasID" = Bottle_Inventory."Gas_ID&quot
INNER JOIN "ERMD"."dbo"."BottleNominalConcentration" BottleNominalConcentration ON Bottle_Inventory."NominalConcentrationID" = BottleNominalConcentration."BottleID&quot
INNER JOIN "ERMD"."dbo"."Bottle Location" Bottle_Location ON
Bottle_Inventory."BottleID" = Bottle_Location."BottleID&quot
INNER JOIN "ERMD"."dbo"."ERMD_Locations" ERMD_Locations ON
Bottle_Location."Loc_ID" = ERMD_Locations."LocationID"}
WHERE
Bottle_Inventory."Full" = 1
ORDER BY
ERMD_Locations."Name" ASC,
Bottle_Inventory."Bar Code" ASC,
Bottle_Inventory."Date Received" ASC

 

[lyanch]

Try this. You will need to paste the bold part in in "view SQL query"

SELECT Gas_Types."Name",Bottle_Inventory."BottleID", Bottle_Inventory."Date Received", Bottle_Inventory."Bar Code", Bottle_Inventory."Calibration Bottle", Bottle_Inventory."Full", Bottle_Inventory."InService", Bottle_Inventory."Pressure",BottleNominalConcentration."Global Nominal Con", ERMD_Locations."Name"
FROM
{ oj ((("ERMD"."dbo"."Gas_Types" Gas_Types INNER JOIN "ERMD"."dbo"."Bottle_Inventory" Bottle_Inventory ON
Gas_Types."GasID" = Bottle_Inventory."Gas_ID&quot
INNER JOIN "ERMD"."dbo"."BottleNominalConcentration" BottleNominalConcentration ON Bottle_Inventory."NominalConcentrationID" = BottleNominalConcentration."BottleID&quot
INNER JOIN "ERMD"."dbo"."Bottle Location" Bottle_Location ON
Bottle_Inventory."BottleID" = Bottle_Location."BottleID&quot
INNER JOIN "ERMD"."dbo"."ERMD_Locations" ERMD_Locations ON
Bottle_Location."Loc_ID" = ERMD_Locations."LocationID"}
WHERE
Bottle_Inventory."Full" = 1
AND Bottle_Location.Date = (Select Max(bl.Date) From Bottle_Location bl where bl.BottleID = Bottle_Inventory.BottleID)
ORDER BY
ERMD_Locations."Name" ASC,
Bottle_Inventory."Bar Code" ASC

Lisa

 

[majordog]

It keeps telling me that the object name Bottle_Inventory is invalid with the new code addition....

 

[majordog]

I worked out the naming issue and it works beautifully - THANK YOU SO MUCH!!!!!!