Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drymchaser
Aged Yak Warrior
552 Posts |
 Posted - 2003-09-12 : 16:19:40
|
I know this is not the Access forum but this has more to do with table design than Access specific.I am building a work request tracking database in MS Access.I have a business requirement to collect information about level of effort for requests.Each request could impact between 1 and 5 OperationsEach Operation currently has 2 Areas of impact(Operations and/or IT)Each Area could be impacted out of a selection of 14 choices (in ranges).Example.Request 1 impacts Operation 1 - Operations Area - 200-400 HoursRequest 1 impacts Operation 1 - IT Area - 1500 - 2000 HoursRequest 1 impacts Operation 2 - Operations Area - PendingRequest 1 impacts Operation 2 - IT Area - No ImpactRequest 1 impacts Operation 3 - Operations Area - No ImpactRequest 1 impacts Operation 3 - IT Area - 400 - 600and so on... I had thought I would have a Request Table, Operations Table, Area Table, and Impact Range table, but how best to capture and relate the various combinations to a request in an efficient manner using Access is my challenge.I would also like the structure to be able to handle changes to Operations or Areas or Impact Ranges, if (more likely when) needed.I do not have a choice of platform - Access is it.Thanks  |
|
|
ann_sqlteam
Starting Member
8 Posts |
Posted - 2003-09-12 : 16:50:59
|
| How about creating a subtable (1-many relationship to Requests) of Requests called RequestData (linked to the main table by requestId). The RequestData table will have foreign keys for OperationId, AreaId and ItemRangeId. These ids can be integers that link to corresponding fields in three reference tables. Each reference table being of the type:OperationId, OperationNamewhere OperationId is the primary key. |
 |
|
|
|
|
|
Table Design