How to relate 2 entries in main table to 24 entries in sub table.
How to relate 2 entries in main table to 24 entries in sub table.
(OP)
I am using Access 97.
Anyone have any idea how to properly set up my table relationships for the following data:
There are two main entries:
Type Name System Location IP address
OPC primary: 0100P AA48 1.2.3 11.22.3
OPC backup: 0100B BC48 1.2.4 7.8.22
Then, for that set above, there are 24 sub entries:
NE NE# Building ID Type Comments
1 100 BAL BB48 LTE
2 119 ZBB RE LTE
3 113 AW DD LTE
...
24 231 BD GR LTE
There will always be a primary and a backup and they will always be named xxxxP and xxxxB.
The 24 entries related to the primary and the backup above are related to both entries. In other words, it is NOT like the first record relates to the primary and the second relates to the backup. You have a primary and a backup OPC and it contains ALL 24 entries.
Any ideas how to establish my relationships?
Thanks,
Ruth
Anyone have any idea how to properly set up my table relationships for the following data:
There are two main entries:
Type Name System Location IP address
OPC primary: 0100P AA48 1.2.3 11.22.3
OPC backup: 0100B BC48 1.2.4 7.8.22
Then, for that set above, there are 24 sub entries:
NE NE# Building ID Type Comments
1 100 BAL BB48 LTE
2 119 ZBB RE LTE
3 113 AW DD LTE
...
24 231 BD GR LTE
There will always be a primary and a backup and they will always be named xxxxP and xxxxB.
The 24 entries related to the primary and the backup above are related to both entries. In other words, it is NOT like the first record relates to the primary and the second relates to the backup. You have a primary and a backup OPC and it contains ALL 24 entries.
Any ideas how to establish my relationships?
Thanks,
Ruth
Talk To Other Members
RE: How to relate 2 entries in main table to 24 entries in sub table.
One ide could be to create a "main OPC-table" relating to both your "primary/bcup" table and to your "24-entry-table"
newTable(newID (PK), etc)
tableOPC(OPCID(PK), newID(FK), Type, Name, System, Location, IP address)
tableTwo(TwoID(PK), newID(FK), NE, NE#, Building, ID, Type, Comments)
It's a bit difficult to read table-names and Keys in your description, but it's a try. If it doesn't work, perhaps someone else has more ideas.
Good Luck, Roy-Vidar
RE: How to relate 2 entries in main table to 24 entries in sub table.
If you understood the previous post from Roy Vidar, you may want to just ignore this.
Otherwise.
What Roy is doing is creating an "intermediate" table. The existing tables exhibit a "many-to-many" relationship, which is not (driectly) supported. Loosely translated, this means that any item in either table may relate to more than one item in the other table. The classic resoloution is to generate a new 'intermediate' table with sufficient information to uniquely identify a one to many relation ship between it and both of the existing tables. In this instance, Roy is suggesting that you would need to generate this table, and modify the existing tables to insert foregin Keys (FK) to relate to the intermediate table.
So, now I think you might be able to do the relationship w/o the intermediate table.
In the 'main' table, seperate the 'Name' field into two fields 'Name' retains the numeric part ("0100") while the new field ("PriBu"?) which will contain the "B" or the "P" (we could make this a boolean (Yes/No) where True ~~ "P" and False ~~"B", but the text w/ P/B is O.K.). Now, add a field to the 'sub' table which has the (New) name field (e.g. "0100"). Now, the relationship between the two tables is just one-to-many on the "name" field, with PriBu acting to collect/Filter the primary and backup information in the 'sub' field.
MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over