New to Access and RDBM - How would you structure?

[DRWonoski]

Hello

Trying to develop a new database and I'm kind of stuck on how I would set up the tables in access.

I have customer orders with a slew of information (Purchase Order, Part Number, Required Date etc) and internal orders once they're in our system (Sales Orders, receipt date, shipped date, location of warehouse etc). Does it make sense to have these in one table or two?

I also have steps that I need to track the progress on of each purchase order. Let's say there are 10 steps that an order may bounce around between. Step 1 - Receipt, Step 2 - Inspection, Step 3 - Repair etc.... A unit may go through all steps or may only go through some of them. They may also go into the same step/stage multiple times through out the process. My initial thought was to set up a table linked to the purchase order in the first table, and have 3 records (Step number, start date, end date). As an order progresses one record would get updated with an end date and a new created with a start date. Is that the best approach? Eventually I'd like to be able to report how long each step takes.

Sorry if none of this makes sense - like I said - new to this!

 

[ranman256]

tCustomer table
tOrder table
tOrderDetail table

tOrder is the master info of the 'purchase': who, date,
tOrderDetails is the shopping cart of all the items for this 1 order.

tOrder.Status : 1-Receipt, 2-Inspection...
or can the status work on each individual Detail in the tOrderDetail?

 

[DRWonoski]

tOrder.Status : 1-Receipt, 2-Inspection...
or can the status work on each individual Detail in the tOrderDetail?

I need to be able to have a full history of how long units sit in a particular stage. Maybe painting the picture is better?

Order A: Goes step 1 for 1 day, step 2 for 3 days, step 3 for 1 day, then back to step 2 for 8 days.
Order B: Goes step 1 for 3 days, Step 2 for 3 days, step 3 for 2 days, then step 4 for 1 day.

I need to be able to see that Order A spent a total of 11 days in step 2 (3 & 8) with an average of 5.5 days per time.
I also need to be able to see that between all orders Step 2 took an average of 4.6 days.

 

[ranman256]

the order would need another subTbl: tOrderStages
StageID (auto), OrderID, StartDate,EndDate

then sum up the elapsed times.

 

[jackd]

Status? Solution?