Stumped on design

[mattsmom]

I’m working on a db and I’m stumped on how to design. There are approx 1800
structure numbers that are inspected at least every other year. Each
structure has varying levels; one structure can have 3 levels, the next 18
and the next can have 122 etc. During the inspection, measurements are taken
on the left and right of one item (Item A) and the left and right of another
item (Item B). Most structures will have a measurement for Item A OR Item B,
not both; however, some will have a measurement for both items. (All of the
inspection data is kept, but only the Original, Last and Current is used in a
report.)
Currently, I have a table Struct_tbl which contains the following fields
StructID, Location, Levels and another table Insp_tbl which contains
StructID, InspDate, Inspctr. From here I don’t know which way to go. Any
help is greatly appreciated!


Example:

Note: Structure 1244 has four levels, in the 1980 inspection both Item A and
Item B had measurements taken.

Original Inspection
Structure # 1244
2/10/80 Level 1 Item A/ Left =13 Right = 12 Item B/Left=12 Right=13
2/10/80 Level 2 Item A/ Left =13 Right = 11 Item B/Left=13 Right=11
2/10/80 Level 3 Item A/ Left =14 Right = 10 Item B/Left=13 Right=10
2/10/80 Level 4 Item A/ Left =18 Right = 14 Item B/Left=18 Right=14

Last Inspection
Structure # 1244
2/1/07 Level 1 Item A/ Left =0 Right = 0 Item B/Left=15 Right=13
2/1/07 Level 2 Item A/ Left =0 Right = 0 Item B/Left=23 Right=11
2/1/07 Level 3 Item A/ Left =0 Right = 0 Item B/Left=13 Right=8
2/1/07 Level 4 Item A/ Left =0 Right = 0 Item B/Left=18 Right=16

Current Inspection
Structure # 1244
2/15/09 Level 1 Item A/ Left =13 Right = 13 Item B/Left=0 Right=0
2/15/09 Level 2 Item A/ Left =23 Right = 10 Item B/Left=0 Right=0
2/15/09 Level 3 Item A/ Left =14 Right = 10 Item B/Left=0 Right=0
2/15/09 Level 4 Item A/ Left =20 Right = 12 Item B/Left=0 Right=0

 

[Jeff Boyce]

I didn't catch the relationship between "Items" (you mention Item A, Item B)
and other objects (?structures, ?levels, ...).

Can there ever be ONLY two "Items", or could there be more?

You need to get the data down first, before moving to reports...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

Your table Insp_tbl needs --
InSpecID, StructID, InspDate, Inspctr
Table InSpecDetail needs --
InspDetailID, InSpecID, Level, Item, Left, Right

You did not say what the data is used for such as comparing orignal to
current or last to current.
Will you use the data base for scheduling? Based on last schedule or last
inspection?

 

[mattsmom]

Thanks for your response Jeff!

Management is looking for the output (report), but I know I need to get the
foundation set (tables & relationships) before moving on. It just seems so
mind boggling.
In answer to your question there will never more than two items. Item A is
storm measurements and Item B is normal measurements. The Levels are the
locations where the measurements are taken on the structure.

HTH,
Debi