Jump to content

Lookup problem with Min Calculation!

This topic is 6611 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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


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?


Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6611 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.