Noob Database Design

I am attempting my very first Access Database to update the system for the print shop I work for, which has been running since 1958 and has everything on card file and job bags.

The problem I have is that I have come to a section where there are many topics (all related) with the same 4 fields. Example

L/P Make-ready QTY COST MARGIN $
L/ Machining QTY COST MARGIN $
Washup QTY COST MARGIN $
Offset Make-Ready QTY COST MARGIN $
Offset Run QTY COST MARGIN $
Washup QTY COST MARGIN $
Cut1 QTY COST MARGIN $
Cut2 QTY COST MARGIN $
and around 80 more


As we will need the Qty Cost Margin and $ to add up at the bottom of each row, I understand they would be the best fields for each job. However if we ever want to put in another feature say above Cut1 all the figures in every job will be incorrect.

Setting up this database will I have to have each section in a individual table or would I just setup tables for QTY COST MARGIN?

The next section I have to ask is there are 4 seperate tables (I think) with similar results (printing Labour, pre-press material costs, pre-press labour costs, and stock costs incurred in each job).....


All 4 of the final figures of these 4 sections will then be added for the Sub Total amount, but they are calculated a little diffferent. Some one time, and some price for the materials, which will then have the GST added for the total cost of each job.

This will list for each job the client has printed.

This will also be used in the Quotation form... and also the possible sales section.

Any help would be muchly appreciated

Thankyou

After reading what I have typed it is more confusing

Sorry

I guess what I am asking is it better to have many different tables, or simplyfy it towards 1 large table somehow.

As far as I can see there are 2 choices.....

1) a table for Qty Cost Margin $

2) a table for each requested field (example)

Stock Qty Cost Margin $
Cover Stock Qty Cost Margin $
Ink Qty Cost Margin $

Stripping Time $
Plate Making Time $
Nylo Making Time $
Nylo Mount Time $
Composing Time $
L/P Make-Ready Time $
etc

I can understand that 1 table would be more rational.... however, 1 table would have to be setup as follows (example)

StockQty
StockCost
StockMargin
StockAmount
CoverStockQty
CoverStockCost
CoverStockMargin
CoverStockAmount

As some of these fields will have to have relations with other sections of the database (so I guess I had better give you the big picture of the overall database)



The complete list of tables I have come up with


The Job Bags and work tickets would be setup like this
Customer Details - will have a relationship with sales, quotes, and have the job description in a submenu
Job Description - will have a relationship with the sales, quote, and costings database
Print Instructions
Stock Used - this will have a relationship with the suppliers database (type and cost of stock)
Delivery Details
Costing of Job - this is listed above and needs to be broken down into each individual section

The other databases that have to be linked with this one is as follows
Sales - So the database can tell our sales girl which custome needs to be contact, reviewed, and list the details of each meeting (sales leads)
Stock - There are over 3000 different paper stocks we use of all different gradings (80gsm, 180gsm, 350gsm, etc) and all sorts of colours, textures, etc.. has to be linked to several different tables
Quotation - to speed up the process from the office to the customer, or the sales girl can enter what she needs for the client and it works out the price for her (to be printed)


The job description will eventually be married up with the actual job stored on the computer....

As you can see I have taken on a very large situation..... but it HAS to be better than 50 years of old bags lieing around, work tickets, card files and customer slips everywhere. There is over 80m2 of floor space used for this old system.....

I said this in another thread, and I'll say it in this one -- spend the time and effort to get your db design as solid and normalized as possible before you start coding anything. It's tough and time-consuming, but it'll pay off later.

As far as efficiency goes, generally speaking a number of smaller tables is better, because your data will be better normalized and indexed.

As for your specifics, I still don't think I understand your data needs enough to make any recommendations at this point. I would suggest, though, before you do anything else, to Google/igrep up a couple of tutorials on normalization -- they often use sales-type db setups as examples for an added bonus.