Trying to create a simple Order Form

[leeli67]

Hi all , Hope someone can help.

im trying to put together a small simple database for my local bakers.

I've made 3 tables

Customers , Products and Orders

My problem is i cant seem to generate an order form to input orders using data from customer table and data from the products table

In an ideal world i would like a drop down box to select the customer, then be able to input orders at the bottom of the form by way of a datasheet subform using drop down combo box for products, im getting close but cant seem to get the prices of order lines to auto generate.
im sure my relations are all correct.
very hard to explain everything without doing a wall of text, is there a tutorial that i could follow that is based on customers orders and forms that anyone can recommend ?

my setup

CustomerTable
---------------
PK CustomerID
company
contact
address
postcode and so on

Products Table
--------------
PK ProductsID
product
price
catergory

Order Table
------------
PK OrderID


CustomerID FK
ProductID FK
Quantity
Price
Total

should i have another table for order entry so it could be a subform....please help

[orange]

There is a data model here that also shows Orders and OrderDetails. It has additional info that may not be within your " business scope", and you can ignore parts as you wish.
http://www.eclipse.org/birt/phoenix/...lsDBSchema.pdf

[leeli67]

cheers , I will take a look

[leeli67]

assuming that all works well , would i be best to use a form or a query to input orders into my database

[orange]

I think a Form would be most appropriate.

Good luck.

[leeli67]

and would this form take the shape of customer form with OrderDetails subform as a pose to Orders Subform ?

[orange]

Customer Order and Order Details

If you are interested, in the videos below, the instructor goes through a Customer, Order, OrderDetails, Products example.

You might wish to watch these free videos. There are other videos, these are just a sample.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2
You can go back and watch these as often as you want.

[leeli67]

ok its going better thanks to a few templates and videos, can anyone tell me the formula for taking Quantity x Price to give me an end of line subtotal, i have tried

=[Quantity]*[Price]

in the Default value box on the property sheet for the Subtotal column, hmm some things not right,

UPDATE ,,,, Managed to sort the total price thingy but now need formula for getting the price of a product which is in the current orderSubForm

[orange]

Wouldn't the Price of a Product be stored in the Product Table?

You could store the price in a lineItem table as well, since in the lineItem table the price will be the price when the Order was made. Whereas the Price in the Product table is the current Price -- and the Price will likely change with time.

[leeli67]

heres my tables and relationsAttachment 6432Trouble is I cant seem to create a good subform for my order form, i thought that the idea was to for instance select a customer from a drop down combo box in the top of my order form then go down to my subform data sheet style and from a combobox select a product and have the ProductID , Price fields auto populate , suffice to say i am struggling.

I create my order form by using wizard and using OderTable for main and OrderDetails for sub(datasheet style)

[orange]

I've taken a quick look but I think:
You don't need ProductName and Total in OrderDetails
You don't need CustomerName in Orders
You don't need CustomerId or OrderDetailsId in INvoices

[leeli67]

Thank you, I will delete those fields and see if i can get further

but wont this mean that i will have to remember all the ID for my products when entering orders into a form, I have 300 or so products so that might not work.

[orange]

Hold on, I said I don't think you need them.
Gather up some test data and see what you need to answer queries you would expect to have in your "operations".
Don't delete anything just because I said maybe...

Make a backup of your database. Have a naming/numbering scheme so you can go back if you have to.

Can you save and post a copy of your database in Acc2003 format (mdb)?

[leeli67]

Thats ok im not going to delete my Customers and product stuff , idont have alot of data anyway


Attachment 6433

[orange]

No. That's not the point. When you are building a database, it is good practice to do it in stages or versions. Whenever there is a significant change, in structure or in data (or forms/queriw..) you should backup your database into a known folder and record what exactly it represents.
eg. version 2.1 Jan 25/2012 -changes in tables(a,b,c) bla bla bla. If you ever have a major screw up, you can go back to a "version that you have backed up", instead of starting from scratch.
There are some backup routines that you can access from a form or procedure to do a backup with date and time parameters.

You might look for backup procedure for MS ACCESS on google. As I recall Helen Feddema has written a routine.