Many to Many Relationships - Please Help...

[sanders720]

I am trying to setup the following tables and relate them. * represents keyed fields...


1. FUNCTION TABLE
containing the following fields
*DEVICE
FUNCTION
DEVICE ALARM REFERENCE
HARDWARE SPECIFICATION
VALIDATION
SOFTWARE SPECIFICATION
FUNCTION CODE


2. VALIDATION TABLE
containing the following fields
*DEVICE
VALIDATION
RISK AREA
ACTION
TEST NUMBER


3. DEVICE TABLE
containing the following fields
*DEVICE
*DEVICE CODE REFERENCE
*ALARM CODE REFERENCE
PART NUMBER
PART DESCRIPTION
MANUFACTURER


4. DEVICE CODE TABLE
containing the following fields
*DEVICE CODE REFERENCE
DEVICE CODE


5. ALARMS TABLE
containing the following fields
*ALARM CODE REFERENCE
ALARM



The Function Table relates to the Device as well as the Validation Tables, as there are several validations and devices per function. The Device Code Table relates to the Device Table, as there are several device codes per device. The Alarms Table also relates to the Device table, as there are also many alarms per device.

I realize there is a way to setup tables in the middle, etc. to facilitate my intentions - But everything I've read has been confusing. I would like to set it up so that I can hit the + or - on the table and have the tables step through, prior to creating forms and VB code.

Any help you can provide is greatly appreciated.

 

[Quehay]

A many to many relationship between two tables is implemented through an intersection table. This creates a one to many relationships between the intersection table and each of the two parent tables.

Because you have multiple devices assoc. with a given function, you wouldn't want the DeviceID stored in the Func table, but rather in an intersection table. This allows infinite relationships (you can store additional column data pertinent to the particular combination of Function & Device):

FuncID | DevID | TestDate

44 66 5/5/02
44 47 6/1/02

Because a given Device may have more than one Code assoc. with it, you'll have to implement an intersection table for this relationship too.

Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[OhioSteve]

Its difficult to provide exact instructions through this medium, because we can't ask detailed questions about the fields and what they represent. However, I do have some general comments.

Working with many-to-many relationships is difficult for two reasons. First, they are hard to envision. You seem to be at that stage. But in addition there is another problem. Your junction tables MUST BE UPDATED whenever the things that they link change. For example, lets say you have a DB for a law firm. A given lawyer may have many cases, and a given case may have many lawyers assigned to it. That is a classic eample of a many-to-many relationship. The junction table's key would be every unique combination of lawyer/case that actually exists. Now lets say that a new lawyer gets hired and is assigned to three cases. Now your junction table DOES NOT contain every unique combination of lawyer/case that actually exists. So you must develop a system that constantly refines that junction table.

I have an application that uses junction tables. Whenever someone changes a record in a child table, I have to rebuild my junction table. I use a macro to do this.

I also have another thought for you. You say that you want to set up all of your relationships "prior to creating forms and VB code". Its good that you want to plan things out before starting your application. However, I hope that you understand that these relationships are NOT permanently fixed. You can establish relationships for a certain query, and then then establish different relationships for another query. You don't have to have the same set of relationships throughout the application.

 

[sanders720]

Thankyou -

Still a little vague, can you possibly email me an example of what you are talking about. My email address is sanderson@metromachine.com

 

[Quehay]

The relationships that count are those in the Tools > Relationships GUI (Graphic User Interface) tool. What Access is doing for you behind the scenes is building foreign key constraints. These are persistent, and they're what determines whether or not a given child table must have a viable parent foreign key, and whether or not changes to the parent key result in changes to the child fkey (only pertinent when the key is a meaningful field rather than an autonumber/artificial key) or deletions of a parent with child records will be allowed (causing child records to be deleted as well--"cascade" deletes).

Relationships in the query window are not relationships per se, but rather just GUI devices to create the SQL joins in the query. Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com