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!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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?
 
Upvote 0
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.
 
Upvote 0
the order would need another subTbl: tOrderStages
StageID (auto), OrderID, StartDate,EndDate

then sum up the elapsed times.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

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
Back
Top