Linking Records within a Table

[Guest]

What is the best way to linked records within a table. For example I have a
table with 1000 records. Out of those I want to link together those who are
related.

 

[Rick Brandt]

What is the best way to linked records within a table. For example I
have a table with 1000 records. Out of those I want to link together
those who are related.

What fields in the table contain data that relate them?

Normally one relates records in different tables. While there are a few cases
where a table relates to itself this is pretty unusual. What kind of data is in
the table? You might have a flawed design.

In a query you can add the table twice (Access will add _1 to the end of the
second instance's name) and then join them just as if they were two separate
tables. Is that what you are looking for?

 

[John W. Vinson]

What is the best way to linked records within a table. For example I have a
table with 1000 records. Out of those I want to link together those who are
related.

For a one to many relationship (classic example is a personnel database, where
all but the top boss's record contains a SupervisorID foreign key to the
EmployeeID of the supervisor) just include a field of the same datatype as the
primary key as a link to the other record.

For a many to many relationship - e.g. in a family or geneology database,
where each person may be related to many others, by different kinds of
relationships - you may want an additional Relationships table with three
fields: FromID, ToID, and Relationship. For example, if PersonID 316 is Fred
Flintstone and 294 is Wilma Flintstone, you'ld have records

316; 294; Husband
294; 316; Wife


John W. Vinson [MVP]

 

[KarenW]

Rose - Did you ever get an answer to your question? I am also trying to link
two or more records in the same table. I have individual addresses that use
the same service line and would like to link together all of the addresses
tied to that service line.
Karen

 

[Evi]

Rose's letter was just too vague for anyone to have any idea what related
her records - does she mean they belong to the same family and are literally
related? Are they related by attending the same church?.
For your's, Karen, the solution will be clearer after a few questions.
1. Can an individual address have more than one service line at the same
time? If the answer is no, then you need to add a ServiceLine Table to
record the different service lines available. The Primary Key field of the
ServiceLine table needs to be the Foreign Key field in your Addresses table.
So if the Autonumber Primary Key Field is called ServID then your Address
Table with have a Long Integer number field called ServID. In the form based
on your Address Table you will add a combo box based on your ServiceLine
table and having ServID and the field which best allows you to recognise the
different service lines.

You can then group addresses easily in queries and reports using ServID

2.Or do you either need to keep a record of the service lines which
addresses had in the past
or can an address have more than one service line at the same time

If the answer to either of these is yes then you need a third table
ServiceAddress which has its own primary key and then the Primary Key of the
AddressTable as a Foreign Key Field and ServID from the ServiceLine table.

This time your form will have 2 combo boxes, one for the Address table and
one for the ServiceLine table so that you can match Address to ServiceLine.
You can also have a Single Main Form based on either ServiceLine or Address
then then, in Design View drag your ServiceAddress table into this form so
that it becomes a Subform.

Evi

 

[KarenW]

Evi - Here's an example and I hope it makes sense:

There are five houses on a street; two of the houses have individual service
lines that tie into a gas main and the other three houses share a service
line that ties into the same main. If the gas flow is terminated at 100 Smith
St (first house on the shared service line), it would also terminate flow to
100-A Smith St and 102 Smith St. Each service has its own record and I would
like to set it up so that if a technician pulls up 100 Smith St, they will
see a link for the other two houses. We currently have comments at the bottom
of the service cards but I want to create some sort of "flag" that pops up to
let them know.

Karen

 

[Evi]

So that's what you mean by a service line!
Is this the sort of thing you want to happen?

As each house only has one service line and you don't need to keep a record
of past service lines then you could use the first database design which I
gave you. You can put a combo box into an unbound form. It will be based on
your Address table but include ServID as its third column. When the
technician goes to an address, he chooses the address with the combo, the
value of the combo box's ServID column is used to automatically filter and
open a form based on your address table so that all Addresses with the same
ServID are shown (ie all the houses sharing the same service line). (If the
house is the only one with that Service Line then it will be the only one
displayed on that form)

Evi

 

[KarenW]

Thanks for your help!! I will give it a try.
Karen