SQL error

[Prescott]

I am trying to get my query to only show results for the most recent
inventory of each item and I keep getting unfathomable errors. Any
suggestions?


SELECT MasterList.Material, MasterList.Size, MasterList.Reliablecode,
MasterList.Critinventory, MasterList.MiniInv, partdesc.inhousedesc,
[Inventory List].inventoryidnumber, [Inventory List].invdate, [Inventory
List].itemidnumber, [Inventory List].inventoryamount
FROM (MasterList INNER JOIN [Inventory List] ON MasterList.ID = [Inventory
List].itemidnumber) AS INV1
WHERE [Inventory List].invdate = SELECT MAX( [Inventory List].invdate
FROM (MasterList INNER JOIN [Inventory List] ON MasterList.ID = [Inventory
List].itemidnumber) AS INV2
WHERE INV2.[Inventory List].invdate = INV1.[Inventory List].invdate);
INNER JOIN partdesc ON MasterList.ID = partdesc.partidnumber
ORDER BY [Inventory List].inventoryidnumber;

 

[Ken Sheridan]

Your SQL statement is so messed up that its difficult to make out quite
what's what, but the essential point is that the subquery should be
correlated with the outer query on the item. I'm assuming that itemidnumber
does this, so I hope this is right:

SELECT MasterList.Material, MasterList.Size,
MasterList.Reliablecode, MasterList.Critinventory,
MasterList.MiniInv, partdesc.inhousedesc,
[Inventory List].inventoryidnumber, [Inventory List].invdate,
[Inventory List].itemidnumber, [Inventory List].inventoryamount
FROM (MasterList INNER JOIN [Inventory List] AS INV1
ON MasterList.ID = INV1.itemidnumber)
INNER JOIN partdesc ON MasterList.ID = partdesc.partidnumber
WHERE INV1.invdate =
(SELECT MAX( invdate)
FROM [Inventory List] AS INV2
WHERE INV2. itemidnumber = INV1.itemidnumber)
ORDER BY [Inventory List].inventoryidnumber;

If you compare this with your own you should be able to see the many places
where you have gone wrong.

Ken Sheridan
Stafford, England

 

[Prescott]

Thanks for the Assist, turns out that I was over complicating it.
The correctcode goes like this:

SELECT MasterList.Material, MasterList.Size, MasterList.Reliablecode,
MasterList.RelDesc, MasterList.Relprice, *
FROM INVLIST AS I1 INNER JOIN MasterList ON I1.itemidnumber=MasterList.ID
WHERE (((I1.invdate)=(SELECT MAX([INVDATE]) FROM [INVLIST] AS I2 WHERE
I2.[ITEMIDNUMBER] = I1.[ITEMIDNUMBER])));


Now I need to make a report that only uses the most recent inventory.

Your SQL statement is so messed up that its difficult to make out quite
what's what, but the essential point is that the subquery should be
correlated with the outer query on the item. I'm assuming that itemidnumber
does this, so I hope this is right:

SELECT MasterList.Material, MasterList.Size,
MasterList.Reliablecode, MasterList.Critinventory,
MasterList.MiniInv, partdesc.inhousedesc,
[Inventory List].inventoryidnumber, [Inventory List].invdate,
[Inventory List].itemidnumber, [Inventory List].inventoryamount
FROM (MasterList INNER JOIN [Inventory List] AS INV1
ON MasterList.ID = INV1.itemidnumber)
INNER JOIN partdesc ON MasterList.ID = partdesc.partidnumber
WHERE INV1.invdate =
(SELECT MAX( invdate)
FROM [Inventory List] AS INV2
WHERE INV2. itemidnumber = INV1.itemidnumber)
ORDER BY [Inventory List].inventoryidnumber;

If you compare this with your own you should be able to see the many places
where you have gone wrong.

Ken Sheridan
Stafford, England

I am trying to get my query to only show results for the most recent
inventory of each item and I keep getting unfathomable errors. Any
suggestions?


SELECT MasterList.Material, MasterList.Size, MasterList.Reliablecode,
MasterList.Critinventory, MasterList.MiniInv, partdesc.inhousedesc,
[Inventory List].inventoryidnumber, [Inventory List].invdate, [Inventory
List].itemidnumber, [Inventory List].inventoryamount
FROM (MasterList INNER JOIN [Inventory List] ON MasterList.ID = [Inventory
List].itemidnumber) AS INV1
WHERE [Inventory List].invdate = SELECT MAX( [Inventory List].invdate
FROM (MasterList INNER JOIN [Inventory List] ON MasterList.ID = [Inventory
List].itemidnumber) AS INV2
WHERE INV2.[Inventory List].invdate = INV1.[Inventory List].invdate);
INNER JOIN partdesc ON MasterList.ID = partdesc.partidnumber
ORDER BY [Inventory List].inventoryidnumber;