Hey, i am doing Database design in access as part of my A-Level course. Im creating a system which manages products and customers, as well as orders.
So far i have a number of tables, Customer, Payment, Order, Products, Type and manufacturer
In the Order table i need the TotalCost field to update once an order is placed. I created a query which automatically generated a totalcost by multiplying the product price by the quantity desired, however it wouldnt then update to tblOrder.TotalCost . I am also unaware of how to make it so that someone can order more than one product at a time without having a huge number of fields in the Order table (product1, quantity1, product2, quantity2 etc) ... please help as much as possible this has to be in tomorrow ive been working on it all week, thought it was sorted, but my ict teacher told me it was all wrong basically on friday
in summary what i need is:
1) to be able to order more than 1 product per order
2) for the TotalCost field in Orders to be updated after an order is placed
3) for the stock level to be updated
Sorry if it is asking alot
If it helps ive attached my database to the topic.