selecting criteria

[Azad,DH, BD]

Dear Experts,

I'm badly in need of your help. pls, help me. I've a table like:
1.item code, 2.Item Serial, 3.Transaction Seril,4.Transc cod . 5. Amt
chr 001 001 prchs
1000
chr 001 002 dprc
200
chr 001 003 sold
500
chr 002 001 prchs
800
chr 002 001 prchs
1000
chr 003 001 prchs
1000



the 1st serial no of chair at 1st transaction was purchased, at 2nd
transaction depreciated and at 3rd transaction it was sold. Now I'd like show
the unsold items in a query but as the sold items 1st two transaction was
prchs and depreciation, it also shows the 1st two transaction of that item
that is already sold. If any item is sold, its recors will be in the table
but how can i deselect the 1st two transaction of that item that is already
sold.




How can I do that?
pls help.

Waiting for your response.


Thanks.

 

[Ken Snell [MVP]]

Create a query that returns all the "sold" records, name it qrySold:

SELECT * FROM YourTableName
WHERE YourTableName.[Transc cod] = "sold";


Then use the new query in the query that will give you the desired results:

SELECT YourTableName.[item code],
YourTableName.[Item Serial], YourTableName.[Transaction Seril],
YourTableName.Amt
FROM YourTableName
LEFT JOIN qrySold
ON YourTableName.[item code] = qrySold.[item code],
AND YourTableName.[Item Serial] = qrySold.[Item Serial] AND
YourTableName.[Transaction Seril] = qrySold.[Transaction Seril]
WHERE qrySold.[item code] IS NULL
GROUP BY YourTableName.[item code],
YourTableName.[Item Serial], YourTableName.[Transaction Seril],
YourTableName.Amt;