Help with tables

[sliz]

Hi guys, I'm hoping somebody can help me out.

I am looking to design an Order Tracking database for work where we supply sunrooms to a small selection of company-owned franchises. Currently we store information in three or four separate spreadsheets, which involves duplication of many fields, to produce Specification Sheets, Manufacturing Schedules, Order Logs, etc. so a database seems the logical way to cut down on inputting the same information more than once and produce the necessary reports.

I'm struggling a little wondering how to structure my tables though. I'll obviously have a Customers table, detailing Customer codes, addresses, etc, but I'm not sure what would be the best way to structure the Order table/s and how to link them. Each job needs the following information adding to it's record at various times in the process:

Order Number (a unique number we assign to each order)
Customer Code (which of our customers placed the order)
Customer Reference (our customer's customer name)
Contract Number (a unique number our customers assign to their orders)
Date Received
Date Confirmation Sent
Date Confirmation Received
Manufacture Date
Delivery Date
Installation Date
Sunroom Model
Colour
Roof Glazing Type (i.e. glass roof or solid roof)
Glazing Depth
Glazing Material (glass spec. or solid panel spec.)
Frame Glazing
Frame Beading
Door Handing (left opens first/right opens first)
Handle Colour
Patterned Glass Option
Decorative Glass Option
Nett Cost
Total Contract Value

This is the information that we generally input to populate a Confirmation Sheet that the customer signs off, where some of the fields have to be completed (Sunroom Model, Colour...) and some are, depending on the customer's requirements (Patterned/Decorative glass options).

Once we recieve a copy of the signed confirmation we then generate a Manufacturing Sheet which includes many of the fields mentioned above, plus others detailing actual window frame styles and dimensions.

I'm a little confused as to where to start as each single job requires so much information to be input throughout the course of the Order Process. Any help or advice would be gratefully received.

Thanks in advance,

Andy

 

[llkhoutx]

You're asking for a solution. You should ask questions.

 

[Arvin]

This is just a suggestion .....but have you reviewed the MS Access sample of Northwinds?

that might be a good starting point .....its not a solution but it should give you a general idea .... I know that in the past I have used it and it has helped me.

 

[sliz]

You're asking for a solution. You should ask questions.

Sorry about that.

Essentially each new order will contain around 40 fields-worth of data, most of which is listed in the post above. These field won't be filled in all at once, but in groups of maybe 7 or 8 fields at a time at different stages (i.e. order is received, order is confirmed, order processed for manufacture, etc.) along the order's lifetime.

What I'm wondering is would it be better to create a single Orders Table with 30 or so fields (some would be left empty depending on the job) that can be amended at the previous-mentioned steps, or would I be better creating a series of tables (maybe one for each step of the process)?

I'm wondering whether I may have answered my own question!

 

[gemma-the-husky]

but to answer your specific point

if all the fields are generally part of the same table, i would definitely just have a single table