Help with a nested query to select latest records. 1

[Juddy58]

Hi, im trying to run a query on a table "tblTransItem"
tblTransItem is a transaction table for a chemical database it contains the following fields:
fldTransItemID 'Distinct transaction ID Primary key
fldTransItemDate 'Date of transaction
fldTransItemTypeID 'Type of transaction, stock in, stock out, etc
fldChemicalID 'Chemical ID
fldQuantity 'Quantity of each chemical
Im trying to write a query that will show the latest IN transaction for each chemical. An IN transaction has an id of 1 for field fldTransItemTypeID.
I know i need a nested query to show the latest record but i seem to be getting incorrect results when it is run.
What I have:
SELECT A.fldTransItemID, A.fldChemicalID, A.fldTransItemDate, A.fldTransItemTypeID, *
FROM tblTransItem AS A
WHERE (((A.fldTransItemDate)=(Select Max(B.fldTransItemDate) From tblTransItem as B WHERE A.fldTransItemID = B.fldTransItemID)) AND ((A.fldTransItemTypeID)=1));
Im a little lost in regards to the join between table a and b. Im not sure if it should be based on fldTransItemID or fldChemicalID. I have tried both and keep getting incorrect results with both. Is there something im missing.
Any help would be appreciated
Thanks
Justin

 

[PHV]

Perhaps this ?
WHERE A.fldTransItemDate=(Select Max(B.fldTransItemDate) From tblTransItem as B WHERE B.fldChemicalID = fldChemicalID AND B.fldTransItemTypeID=1);


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Juddy58]

Thanks for the reply i was missing the typeid = 1 in the nested part of the query.
Cheers
Justin