New to Access and RDBM - How would you structure?

DRWonoski

Board Regular
Joined
Mar 20, 2014
Messages
99
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!
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,876
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

Board Regular
Joined
Mar 20, 2014
Messages
99
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

Well-known Member
Joined
Jun 17, 2014
Messages
1,876
the order would need another subTbl: tOrderStages
StageID (auto), OrderID, StartDate,EndDate

then sum up the elapsed times.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,732
Messages
5,446,184
Members
405,390
Latest member
RafalKowalski

This Week's Hot Topics

Top