Data Validation

J

James Brust

I am trying to validate an entry in a form field (Quantity) by the sum of the
field from the table it is sending the data to (sum of quantity >=0). The
sum needs to be based on the combination of two fields (Item Number and
Expiration Date).

The bottom line is that I want the user to be notified if they are using
more of a product with a certain expiration date than exists in the system.
 
K

KARL DEWEY

I am not understanding your post. How can you sum Item Number and Expiration
Date?
 
J

James Brust

I am summing the quantity based on the item number and expiration date.

There may be fifty transactions of a particular item number/expiration date
and I need to add up the quantities from each transaction and make sure that
the current transaction is not driving the inventory negative. Jim
 
J

James Brust

I am not sure what you are asking.

There are other fields, but these three are the only ones that matter in
this case. The expiration date field is a date field. The Item number is a
test field. The quantity field is a number field.
 
K

KARL DEWEY

Ok, what is your table structure?

James Brust said:
I am summing the quantity based on the item number and expiration date.

There may be fifty transactions of a particular item number/expiration date
and I need to add up the quantities from each transaction and make sure that
the current transaction is not driving the inventory negative. Jim
Click to expand...
 
K

KARL DEWEY

I ask for you table structure. You did not give me enough information on
your database to work with.
The quanity must be compared with stock level minus previous orders.
There must be stock records the include ITEM, QTY, EXP-DATE, and StockDate.
Also records for indicating the previous orders.
This means at least two tables - STOCK & ORDERS.
 
J

James Brust

That would be nice, however, we only have one table. We are just tracking
inventory in the warehouse. Everybody else (i.e. Production, Planning,
Sales, Accounting, etc.) each have their own, unconnected databases (from an
old accounting system to Excel spreadsheets). Our database only knows about
the order when we tell it that we are taking inventory.

I do have an order number field, and it populates all of the records for
that order from a form after being entered once. I also have an autonumbered
reference number field that populates every record for each transaction (e.g.
order or stock conversions). Each transaction might have 40 item numbers and
each item number might have 10 expiration dates.

I certainly don't have a problem creating another table if you see a
solution that requires another table. Jim
 
Top