Multiple Orders

[jimd1768]

I am new to access and have a form set for processing orders. How do I process multiple orders i.e a customer orders an onion and a pepper. Currently I have to create 2 orders and would like 1 order with 2 items. I am sure this is very simple. Hope someone can help

 

[David Eagar]

First you must create two tables, one for the Purchase Order Details (Who, when etc) and Purchase Order Lines table to store the what & how much per order

A 1 to many link between the tables

 

[jimd1768]

Thanks

Thanks for the advice but i need to clarify (sorry for being a pain):

table 1:
Order No
Customer ID
Items ordered
Qty
Cost
etc

Table 2:
Product ID
Product
Cost
VAT
Total

Is this what you mean? Thanks again

 

[neileg]

You need a field to link the two tables. Order No is the one to use.

table 1:
OrderNo
Customer ID
Items ordered
Qty
Cost
etc

Table 2:
Product ID
OrderNo
Product
Cost
VAT
Total

 

[GaryPanic]

what you need to a have is a contious form to operate on this basis
so that you can have a list of items ordered, will will also need an order number system so that you can have an historic record ,

so customer x order apples (order no 12) pears (order number 12) and coconuts (order number 12)
then
customer x order number 12 comprises of
apples, pears and coconuts
so you need an extra table called orderhistory or history (possible another table called order set up in a contious format

either an append option or an add option to copy info from 1 table to another

tables required

customer
name add etc
Product table - apples , pears coconuts
order table
historytable

first 2 tables easy -

3rd table will be set up to have multiple entries in it so a contious form set up with a combo box drop down to select the products - you enter the amount require (4 apples) and this will *4 the price per apple at that point -
- now its up to you to either store the price or recalculate it each time you look at it - ( this is a big discussion on the form as to whether to store or calculate- the purests say calculate - the realists probably say store it )
if we take apples as an example

apples in August are cheaper than in January - so i would be one for storing the following in my history table
apples (product id number ) cost 12 pence (or 1 euro) whatever * 4 = xx
and storing all of this info, means that should apples rise in price say in June you can change the price of produict apples and know from that point on the right price will be charged and this will not effect your historic info

history table will be a copy of your order table -

so when our order is complete you click a button and it either appends the info into a history table or add's it-
(need a time/date stamp ont his as well )

 

[GaryPanic]

Neil - you beat me to this --


same sort of line -different approach ..
g

 

[jimd1768]

Thank you very much.