Best Option To Create Order Form?

[Garindan]

Hi Everyone, firstly, thanks to all for your help, if it wasn't for you many of us would struggle for ages. Sorry if this post is a bit long but i wanted to give a clear picture of where i'm messing up

I am building a customer details and orders database for a friend. So far i have built the tables, relationships, normalized the database and made a customer details form based on a select query. I'm very happy with all of the above, the tables are good (i think ) and the form i have made works perfectly for viewing and entering data (although its pretty basic, but i'm pleased with it). Here's a pic showing my tables, fields and relationships:-

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=5862&stc=1

The trouble is, i'm having a problem with the Orders form. I have been trying for about two weeks to get something which works and i'm not doing too well , i feel like i've been going in circles! I'm trying to make the database as simple and straightforward to use as possible as my friend hasn't had much experience, so i have the CustomerNumber field in tblCustomers as an Autonumber, aswell as PurchaseNumber in tblCustomerPurchases and PurchasedItemID in tblCustomerPurchasedItems.

My tblCustomerPurchases is as follows:-

PurchaseNumber (Autonumber)
CustomerNumber (Long Integer - linked to CustomerNumber in tblCustomers)
DateOfPurchase
Season (Not really needed but he wants it, go figure )
TotalCost

I have TotalCost in this table as he regularly changes his prices so instead of having a set price for products and then a calculated total cost he just wants to be able to store the cost of each order.

My tblCustomerPurchasedItems is like this:-

PurchasedItemID (Autonumber - simply so the table has a primary key)
PurchaseNumber (Long Integer - links to PurchaseNumber in tblCustomerPurchases)
Product
Quantity

I wanted to have a well laid out form to enter purchase date, total cost, etc, and be able to select products and quantities to go with the orders. Also, i hoped to make it easy for him to find customers orders easily. I know it should be simple, but after struggling for a while i'm now completely lost . I was wondering whether anybody could help me out, or suggest a way of laying out/building my form which might work well? I'm not sure what would be the best/most effective way to do it. I'm not even sure what i'd need to put in a query to base it on anymore
Does anybody have any suggestions as to a good way to do it? I will put my database up aswell in case anyone wants to have a look. Many many thanks to anyone that can help

 

[Garindan]

Any ideas anyone?

 

[Garindan]

Or does anybody know somewhere where i could find some good examples of ways to 'lay out' an order form?

Thanks people.

 

[Pat Hartman]

You need to remove TotalCost from the Purchases table and add UnitCost to the details table. Season should be calculated in a query rather than stored.

Take a look at Northwind or Orders. They are both basic order entry applications.

 

[Garindan]

Thanks alot

Strangely i never knew about those sample databases that come with Access, they look good! I'll take a look and have a play

Thanks again.

 

[Pat Hartman]

Here's a link to a post I made last year that lists links to some of the best samples that Microsoft has created. They are all worth reviewing.

http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=172177#post172177

 

[Garindan]

Thanks again mate!