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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,954
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,954
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,122,437
Messages
5,596,125
Members
414,043
Latest member
thomas Stein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top