I have built a database that shows the purchase of items that have serial numbers, and so are unique. The database shows the purchase oe each item, and subsequent sale, including "Date In" and Date Out".
Each "item" however has a unique transaction reference (Stock No.)
I would like to be able to show what items are currently in stock, and therein lies my problem.
I am able to use a select count to find all the instances where the stock number there are two stock numbers (ie In and then Out), but have been unable to find a way to filter the records in a query, to show the stock currently held.
Can anyone give me a pointer as to what terms I should use to these, so that only Single instances of the "Stock No." appear in a table, as that would show the current stock held.
I have tried numerous ways to achieve this but I have reached a dead end. I am not experienced in writing with SQL, any advice would be appreciated.
Each "item" however has a unique transaction reference (Stock No.)
I would like to be able to show what items are currently in stock, and therein lies my problem.
I am able to use a select count to find all the instances where the stock number there are two stock numbers (ie In and then Out), but have been unable to find a way to filter the records in a query, to show the stock currently held.
Can anyone give me a pointer as to what terms I should use to these, so that only Single instances of the "Stock No." appear in a table, as that would show the current stock held.
I have tried numerous ways to achieve this but I have reached a dead end. I am not experienced in writing with SQL, any advice would be appreciated.