Table Relationships help

[member]

Hi,

I am creating a stock control database. Basically, what I want to happen is the database should contain a database of customers, and all the products the store has. When a sale is made, the stock automatically updates. I have set up all the tables and stuff. PLease could you check it out.

THe problems i have is that when i type in a customer ID which doesn't exist in the Sales table i get no error message. And the field Forname & Surname do not link.

Please could someone check out if i have structured the tables and relationships out correctly. If not please could you tell me what to do.

Thanks.

PLease check out the screenshot:

 

[Premy]

Well you could check out the structure in the Northwind example DB. Your Customer Account would correspond to the Orders table there (orders from clients, not suppliers) and Sales would correspond to Order Details.

Now for the automatic stocklevel to work u might want to throw in 2 extra tables (that's how I do it anyway) Supplies and Supplies Details. Supplies and Supplies Details are linked the same to the Products table, as Orders and Order Details are, (i.e SuppliesID and ProductID combine into o compound foreign key linked to ProductsID in Products). Once u have that set up, u provide a routine on your sales form which (on every sale) sums up a given product's qty in Supllies Details, subtracts from the sum of that product in the Orders table, minus the qty just sold. This will give u the up to date stocklevel for that product. So for every product you sell you basically calculate the total qty ever purchased of that product, minus the total qty ever sold, minus the qty being sold right now.

HTH

Premy