Hi,

I've just had a request from a user who's running on of my DBISam databases
for inventory.

The current picture: They currently warehouse items of other clients, and
track stock not only on a product basis, but a product basis per client.
Stock comes in, and out at a very frequent basis.

I track this information in a table called StockTrans. (Stock Transactions).
The table has a structure similar to the following:

CLIENT
PRODUCT
QTY                (Positive figure for inwards, negative for outloads)
DATETIME    (Stamp to show when transaction occurred).

I simply add a new entry for the client, insert the product and QTY
(negative figure for outloads), along with the date to keep track of stock
movements. To get a current stocktake report - I simply to a sum of the
total stock (which has positives for receivals, and negatives for outloads)
to show me the current figure. (To get a previous days stock balance, I
simply put a where clause in for the date/time field).

The crunch: They have asked for a report that shows them a list of client /
product combinations, and a date/time showing when clients last had a zero
balance of any particular stock items. (As stock may go out in the morning,
and more stock arrive in the afternoon - it needs to be taken to a date/time
stamp, and not just a date).

Does anyone know how (if possible) I could achieve a query to give me the
date/times of when the stock would have equaled zero from this list? I've
been pondering it over, but can't think of anything decent.

Thanks & Regards

Adam.