Stock Control using sales, lineage & stock db's

[mattlight]

I hope this is a better explaination

There is 7 databases

CLIENTS DATABASE - This database stores Client ID, Name, Address, etc.

SUPPLIERS DATABASE - This database stores information on Suppliers like

Supplier Name, Contact Name, Address, Telephone, Fax, Account No,E-mail & Web site and looks up from Stock database what products are purchased from this supplier.

STOCK CONTROL DATABASE - This is a line entry database which includes Prod Code, Stock Avail, Re-order level, Stock Room qty, Allocated, Supplier (From the Suppliers Database), Retail Price, Cost Price, Description, Catagory of goods. It includes a flag to let you know if

Stock Avail < Re-order Level.

SALES DATABASE - This is a line entry database which includes Invoice No,

Inv Date, Prod Code, Qty, Price, Salesperson, payment method, etc.

POINT OF SALE DATABASE - This database generates an invoice number, also links sale to salesperson logged on from Staff database. There is a portal that writes into the SalesDatabase the product code which then looks up description and retail price from the Stock Control Database.

It also displays Stock Available from the Stock Database. The problem is that I cannot write into the Stock Database a new allocated figure. This database also gets information from the Client Database from Client ID the name, address, etc.

STAFF DATABASE - list of salespersons linked to sales database giving a list of the sales by salesperson's name giving commission earned figure as

well.

PURCHASE DATABASE - This database generates a purchase order number, When supplier name is entered, shows on a clipboard what stock is in serious need to be order due to flag in the Stock Control Database.

There will also be a -

DISPATCH DATABASE - for goods sold to go out the door

DELIVERY DATABASE - for goods received from supplier.

The problems that I have are below:-

When in POINT OF SALE DATABASE, an invoice number is generated along with date. I then go to the Purchasing Portal (linked to SALES DATABASE) and type in the product codes that correspond to the STOCK CONTROL DATABSE which creates a new record for every product coded entry. Up pops the description, price and stock available and then I can enter in the Required quantity for the order.

I need to be able to make the quantity become the ALLOCATED STOCK figure in the STOCK CONTROL DATABASE.

ie: STOCK CONTROL::Allocated = STOCK CONTROL::Allocated + Qty

no matter how many ways I try to do this it doesn't do it!!

I've tried, in the SALES DATABASE, to make a calculation field that looks-up STOCK CONTROL::Allocated and adds QTY SOLD to the figure but then cannot paste the result into STOCK CONTROL::Allocated without physically going into the STOCK CONTROL database and doing it manually.

Any way you can think of doing it?

In an ideal world I would like to be writing into 2 portals - one being the Sales database and the other being the result of the calculation into the stock control - but, cannot find a way to do this!!

Help! Thank you for reading all of this, I hope you're laughing at the problem because it's probably so easy.

Cheers,

Matt

c/o [email protected]

[Kurt Knippel]

You seem to be just entering the sales data directly into the invoices portal. This is fine in most situations, except where you need a cascade of things to happen (like an updating of existing product quantity). In that case you should script the procedure.

Setup a script that mimics your existing procedure and follows that with an updating of the Stock Control fields. Attach this script to a button on the invoices screen.