Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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.

How to relate 2 entries in main table to 24 entries in sub table.

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?



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.


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.

There is never time to do it right but there is always time to do it over

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close