If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Stumped on design
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 |
#2
|
|||
|
|||
Stumped on design
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 "mattsmom" wrote in message ... 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 |
#3
|
|||
|
|||
Stumped on design
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? -- Build a little, test a little. "mattsmom" wrote: 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 |
#4
|
|||
|
|||
Stumped on design
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 "Jeff Boyce" wrote: 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 "mattsmom" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|