Please, please help? :/ How to do this in MS Access?On 02.07.10, In article
I need to make a database that will be used for a hamper company. (It’s a type of gift basket, basically.) There are standard, pre-made hampers, and there are "bespoke" hampers, which are custom-made. In the "Items" table, I need to have both standard hampers and individual items for the bespoke hampers. It sounded weird, [...] Leave a Comment2 Responses to “Please, please help? :/ How to do this in MS Access?”Leave a Reply |
WAS 
€5.12 
€6.40 
€2.56 
€1.28
€1.28 
Original Likas Papaya Skin Whitening Herbal Soap by Trinidad Cosmetics Laboratory - 135 grams €4.72 |
Norwegian 
French 
Bulgarian 
German 
Spanish 
English
Russian 
Persian 
Portuguese
My suggestion would be to create a one to many relationship in the relationship window
One customer to many orders
So you would have your form with the customer and a subform for all their orders
Start over. A hamper has a hamper ID.
In the contents table you have an entry for each for each item’s ID (a standard hamper would be an item) in that particular hamper ID.
You have a table of standard hampers and their prices.
You have a table of content items and their prices (and ID numbers).
You have a customer table – name, address, charge card info, if you want to keep it. And a customer ID.
An order table – customer ID, hamperID, date of order, date picked up, amount paid (if one payment for 3 hampers, each order will get the full payment). And order ID, of course.
A payment table – date, method of payment, amount of payment, hamperID the payment is for (if one payment is made for 3 hampers, this entry gets the payment for the hamper it’s paying for). And paymentID. This table also gets updated if an order is returned – the payment is negative.
Cost (of a hamper, of an order) is calculated when needed – you don’t keep data in the database that can be calculated. You calculate it when a payment is made (to print the receipt, showing the balance still due, or a zero balance), when an order is placed (to create the receipt showing the price), etc.
You also have all the data you need for your ledger and accounts receivable programs (and inventory and ordering).