Lookup problem with Min Calculation!

[Robert Collins]

I have a database which I use in my shop for POS. There are 3 main files

Invoices

Products (holds prices, product descriptions)

Stock (holds all products booked in . inc serial number for each record)

I have set it up so that when a products serial number is entered (in an Invoice), it checks to see if the serial number is one that has been booked in.If it is not, then a dialog box pops up and tells the user to try again)

I would like to go one stage further and not let the user sell a product with a serial number if there is an older one thats in stock.

I have tried creating a field in "Invoices" that looks up a calculated result using the Min function which gives me a date(from the Stock file) - but the date it gives is when the first of that product was booked in - not the oldest one thats not yet sold.

Sorry if I haven't explained it clearly , but does anyone have any ideas?

thanks

[transpower]

You want the minimum serial number of a product in Stock. So, use Min on the serial number. If a product is sold, that serial number must be removed from Stock. (I assume the serial numbers are segregated somehow by product.)

[Robert Collins]

Ok... the serial numbers do not follow sequentially - they are serial numbers already allocated from the manufacturer. (mobile phones in this case).

So the fields I have in my stock file are as follows

In date (record creation date)

Product code

Product description (looked up via the product code to the Product file

Date out (looked up from the Invoice File - if blank then it is not sold)

Serial number (entered via a barcode already on the product)

Cost Price (looked up from Product file)

My guess is a validation calculation on the serial number field in the Invoice file but I'm just not sure.