Can I have a record within a record

[Rachelle]

I am creating a client database for my business. Each of my clients has
their own database of clients. I need to keep their database of clients for
each of them. Is it possible to creat a contact within a contact

 

[KARL DEWEY]

You would need to make two tables with a one-to-many relationship.

The 'one' table must have a primary key that is unique. Most use an
Autonumber field.

The many table would then use a datatype that matches the primary key field.
If using an autonumber in the 'one' table the use Number - Integer in the
'many' table.

Set the relationship by adding the two tables in the Relations window.
Click on the primary key field and drag to the field of the 'many' table.
Double click on the resulting connector line. Select the first two options -
Referential integerity and cascade update.

Use a form and subform to display and edit the two level of data.

 

[Duane Hookom]

If the structures are nearly the same, you might be able to get by with a
single table. Add a field to store the Primary Key value from the contact's
client.

 

[John Vinson]

I am creating a client database for my business. Each of my clients has
their own database of clients. I need to keep their database of clients for
each of them. Is it possible to creat a contact within a contact

Not in that way, but you can use a "self referential" table. For
example, you could have MasterClient field of the same datatype as
your Clients table Primary Key; this would be NULL for your clients,
and would contain your client's ClientID for *that client's* clients.

John W. Vinson[MVP]

 

[Ken Sheridan]

You'll probably want to allow for the same contact being a client of more
than one of your clients. What this entails is a many-to-many relationship
between the individuals involved. Normally a many to-many relationship is
modelled between two tables by a third table, e.g.

Orders---<ProductsOrdered>----Products

In this relationship each order might be for many products and each product
might be included in many orders. The ProductsOrdered table models this by
having two foreign key columns OrderID and ProductID which reference the
primary keys of the Orders and Products table. The ProductsOrdered table
would also be likely to have other columns such as Quantity and UnitPrice,
which are attributes of the relationship type which the table models.

In your case however, if you have a single table of all people whether they
are your clients or clients of your clients, then the many-to-many
relationship is between two instances of the same table like so:

People---<ClientList>----People

The ClientList table in this case again has two foreign key columns,
ClientOfID and ClientID say, but in this case they both reference the primary
key of the single table People (you can of course give these tables and
columns whatever names you wish). So if someone with a PersonID value of 42
has clients with PersonID values of 99 and 123 the ClientList table would
have the following rows:

ClientOfID ClientID
42 99
42 123

If PersonID 99 is also a client of someone else, PersonID 66, then there
would also be a row:

ClientOfID ClientID
66 99

But PersonID 99 might also have their own clients, say 135 and 177, so
there would be rows:

ClientOfID ClientID
99 135
99 177

If you are only interested in the first level of clientship for each person
there is no problem; you simply join two instances of the people table to the
ClientList table:

SELECT P1.Person, P2.Person AS Contact
FROM People AS P1, People AS P2, ClientList
WHERE P1.PersonID = ClientList.ClientOfID
AND P2.PersonID = ClientList.ClientID;

If you want to drill down through the levels of clientship, however, and
show the 'chain' of clients, sub-clients, sub-sub-clients etc for each
person, it gets trickier as this involves recursion, and queries cannot be
recursive. This is analogous to the classic database problem of a 'bill of
materials' or 'parts explosion'. To achieve recursion through a variable
number of levels is not trivial, but it is possible to achieve the same
result fairly simply for a fixed number of levels by a series of outer joins
in a query. Here's an example using tables Parts and PartStructure which
does this for 9 levels:

SELECT P1.PartName AS Part,
[P2].[PartName]+(" (" & [PS1].[Quantity] & ")") AS Component1,
[P3].[PartName]+(" (" & [PS2].[Quantity] & ")") AS Component2,
[P4].[PartName]+(" (" & [PS3].[Quantity] & ")") AS Component3,
[P5].[PartName]+(" (" & [PS4].[Quantity] & ")") AS Component4,
[P6].[PartName]+(" (" & [PS5].[Quantity] & ")") AS Component5,
[P7].[PartName]+(" (" & [PS6].[Quantity] & ")") AS Component6,
[P8].[PartName]+(" (" & [PS7].[Quantity] & ")") AS Component7,
[P9].[PartName]+(" (" & [PS8].[Quantity] & ")") AS Component8,
[P10].[PartName]+(" (" & [PS9].[Quantity] & ")") AS Component9
FROM Parts AS P1
LEFT JOIN (((((((((((((((((PartStructure AS PS1
LEFT JOIN PartStructure AS PS2 ON PS1.MinorPartNum = PS2.MajorPartNum)
LEFT JOIN PartStructure AS PS3 ON PS2.MinorPartNum = PS3.MajorPartNum)
LEFT JOIN PartStructure AS PS4 ON PS3.MinorPartNum = PS4.MajorPartNum)
LEFT JOIN PartStructure AS PS5 ON PS4.MinorPartNum = PS5.MajorPartNum)
LEFT JOIN PartStructure AS PS6 ON PS5.MinorPartNum = PS6.MajorPartNum)
LEFT JOIN PartStructure AS PS7 ON PS6.MinorPartNum = PS7.MajorPartNum)
LEFT JOIN PartStructure AS PS8 ON PS7.MinorPartNum = PS8.MajorPartNum)
LEFT JOIN PartStructure AS PS9 ON PS8.MinorPartNum = PS9.MajorPartNum)
LEFT JOIN Parts AS P2 ON PS1.MinorPartNum = P2.PartNum)
LEFT JOIN Parts AS P3 ON PS2.MinorPartNum = P3.PartNum)
LEFT JOIN Parts AS P4 ON PS3.MinorPartNum = P4.PartNum)
LEFT JOIN Parts AS P5 ON PS4.MinorPartNum = P5.PartNum)
LEFT JOIN Parts AS P6 ON PS5.MinorPartNum = P6.PartNum)
LEFT JOIN Parts AS P7 ON PS6.MinorPartNum = P7.PartNum)
LEFT JOIN Parts AS P8 ON PS7.MinorPartNum = P8.PartNum)
LEFT JOIN Parts AS P9 ON PS8.MinorPartNum = P9.PartNum)
LEFT JOIN Parts AS P10 ON PS9.MinorPartNum = P10.PartNum)
ON P1.PartNum = PS1.MajorPartNum;

This query was actually written s the RecordSource for a report which shows
the structure of a components in a tree-like format. It was produced, along
with an example of how to produce a parts explosion over a variable number of
levels, for a magazine article some years ago. The file won't be available
on their web site now, but if you are interested I can send you a copy if you
mail me at ken<dot>sheridan<at>dsl<dot>pipex<dot>com.

Ken Sheridan
Stafford, England

 

[idgity]

I have a problem similar to Rachelle's. I understand conceptually the idea
of a many-to-many self-referential relationship, with the need for a second
table. My problem is that I cannot figure out how to create such a situation
in Access 2003, using the typical GUI.

If we continue on with the Client and ClientList Example, starting with a
new DB:
- I create a simple Client Table with a text primary key (say ID).
- I create a ClientList Table with two fields ClientOf and Client (both
text).
- I then enter the access relationship view and add Client and ClientOf
- I make one relationship by dragging ID from client to ClientOf. I select
enforce referential integrity and cascade update. This creates a one-to-many
relationship with one client to many ClientLists.
- I then try to make the second relationship, I drag ID from Client now to
Client within ClientList. First I'm warned that a relationship already
exists, but I click "no" to make a new relationship.
- I select referential integrity and cascade update in the new relationship,
click create.
- Access creates Client_1 with the new relationship from Client_1 to
ClientList

What is this? This new table is not in my list of tables. Does this mean
the relationship has been made as intended?

Furthermore, when I start adding play data into the tables, more and more
Client_X tables appear in my relationship view. I'm not exactly sure about
the logic of how they arrive.

I've tried different combination of things, making one or more of the fields
of ClientList primary keys, and not enforcing referential integery, it
doesn't seem to make a difference.

Cany anyone tell me how to make a many-to-many self-referential table
relationship in Access 2003?

I don't think there is any way to avoid this in my data or table design.

Thanks so much for any help,

Jennifer

You'll probably want to allow for the same contact being a client of more
than one of your clients. What this entails is a many-to-many relationship
between the individuals involved. Normally a many to-many relationship is
modelled between two tables by a third table, e.g.

Orders---<ProductsOrdered>----Products

In this relationship each order might be for many products and each product
might be included in many orders. The ProductsOrdered table models this by
having two foreign key columns OrderID and ProductID which reference the
primary keys of the Orders and Products table. The ProductsOrdered table
would also be likely to have other columns such as Quantity and UnitPrice,
which are attributes of the relationship type which the table models.

In your case however, if you have a single table of all people whether they
are your clients or clients of your clients, then the many-to-many
relationship is between two instances of the same table like so:

People---<ClientList>----People

The ClientList table in this case again has two foreign key columns,
ClientOfID and ClientID say, but in this case they both reference the primary
key of the single table People (you can of course give these tables and
columns whatever names you wish). So if someone with a PersonID value of 42
has clients with PersonID values of 99 and 123 the ClientList table would
have the following rows:

ClientOfID ClientID
42 99
42 123

If PersonID 99 is also a client of someone else, PersonID 66, then there
would also be a row:

ClientOfID ClientID
66 99

But PersonID 99 might also have their own clients, say 135 and 177, so
there would be rows:

ClientOfID ClientID
99 135
99 177

If you are only interested in the first level of clientship for each person
there is no problem; you simply join two instances of the people table to the
ClientList table:

SELECT P1.Person, P2.Person AS Contact
FROM People AS P1, People AS P2, ClientList
WHERE P1.PersonID = ClientList.ClientOfID
AND P2.PersonID = ClientList.ClientID;

If you want to drill down through the levels of clientship, however, and
show the 'chain' of clients, sub-clients, sub-sub-clients etc for each
person, it gets trickier as this involves recursion, and queries cannot be
recursive. This is analogous to the classic database problem of a 'bill of
materials' or 'parts explosion'. To achieve recursion through a variable
number of levels is not trivial, but it is possible to achieve the same
result fairly simply for a fixed number of levels by a series of outer joins
in a query. Here's an example using tables Parts and PartStructure which
does this for 9 levels:

SELECT P1.PartName AS Part,
[P2].[PartName]+(" (" & [PS1].[Quantity] & ")") AS Component1,
[P3].[PartName]+(" (" & [PS2].[Quantity] & ")") AS Component2,
[P4].[PartName]+(" (" & [PS3].[Quantity] & ")") AS Component3,
[P5].[PartName]+(" (" & [PS4].[Quantity] & ")") AS Component4,
[P6].[PartName]+(" (" & [PS5].[Quantity] & ")") AS Component5,
[P7].[PartName]+(" (" & [PS6].[Quantity] & ")") AS Component6,
[P8].[PartName]+(" (" & [PS7].[Quantity] & ")") AS Component7,
[P9].[PartName]+(" (" & [PS8].[Quantity] & ")") AS Component8,
[P10].[PartName]+(" (" & [PS9].[Quantity] & ")") AS Component9
FROM Parts AS P1
LEFT JOIN (((((((((((((((((PartStructure AS PS1
LEFT JOIN PartStructure AS PS2 ON PS1.MinorPartNum = PS2.MajorPartNum)
LEFT JOIN PartStructure AS PS3 ON PS2.MinorPartNum = PS3.MajorPartNum)
LEFT JOIN PartStructure AS PS4 ON PS3.MinorPartNum = PS4.MajorPartNum)
LEFT JOIN PartStructure AS PS5 ON PS4.MinorPartNum = PS5.MajorPartNum)
LEFT JOIN PartStructure AS PS6 ON PS5.MinorPartNum = PS6.MajorPartNum)
LEFT JOIN PartStructure AS PS7 ON PS6.MinorPartNum = PS7.MajorPartNum)
LEFT JOIN PartStructure AS PS8 ON PS7.MinorPartNum = PS8.MajorPartNum)
LEFT JOIN PartStructure AS PS9 ON PS8.MinorPartNum = PS9.MajorPartNum)
LEFT JOIN Parts AS P2 ON PS1.MinorPartNum = P2.PartNum)
LEFT JOIN Parts AS P3 ON PS2.MinorPartNum = P3.PartNum)
LEFT JOIN Parts AS P4 ON PS3.MinorPartNum = P4.PartNum)
LEFT JOIN Parts AS P5 ON PS4.MinorPartNum = P5.PartNum)
LEFT JOIN Parts AS P6 ON PS5.MinorPartNum = P6.PartNum)
LEFT JOIN Parts AS P7 ON PS6.MinorPartNum = P7.PartNum)
LEFT JOIN Parts AS P8 ON PS7.MinorPartNum = P8.PartNum)
LEFT JOIN Parts AS P9 ON PS8.MinorPartNum = P9.PartNum)
LEFT JOIN Parts AS P10 ON PS9.MinorPartNum = P10.PartNum)
ON P1.PartNum = PS1.MajorPartNum;

This query was actually written s the RecordSource for a report which shows
the structure of a components in a tree-like format. It was produced, along
with an example of how to produce a parts explosion over a variable number of
levels, for a magazine article some years ago. The file won't be available
on their web site now, but if you are interested I can send you a copy if you
mail me at ken<dot>sheridan<at>dsl<dot>pipex<dot>com.

Ken Sheridan
Stafford, England

I am creating a client database for my business. Each of my clients has
their own database of clients. I need to keep their database of clients for
each of them. Is it possible to creat a contact within a contact

 

[David F Cox]

I will try to explain. Suppose you have a table Colors with Id_color and
Color as its fields.
In the widgets table you have a foreign key (FK) of Id_color for the color
of the widget.
In the Shelves table you have Id_color for the color of the shelves. You are
wanting to have two records current in the same table. Which color is
Color.Id_color pointing to? You would have two lines on the relationship
diagram meeting at the same field. It would get very confusing.
Access solves this dilemma by having a "virtual" table Colors_1 so that you
can specify the right one for the widget and the shelves. The same idea
applies for table Colors_2 etc for other places this table is used.

Please ask again if I have not made it clear enough.

I have a problem similar to Rachelle's. I understand conceptually the idea
of a many-to-many self-referential relationship, with the need for a
second
table. My problem is that I cannot figure out how to create such a
situation
in Access 2003, using the typical GUI.

If we continue on with the Client and ClientList Example, starting with a
new DB:
- I create a simple Client Table with a text primary key (say ID).
- I create a ClientList Table with two fields ClientOf and Client (both
text).
- I then enter the access relationship view and add Client and ClientOf
- I make one relationship by dragging ID from client to ClientOf. I
select
enforce referential integrity and cascade update. This creates a
one-to-many
relationship with one client to many ClientLists.
- I then try to make the second relationship, I drag ID from Client now to
Client within ClientList. First I'm warned that a relationship already
exists, but I click "no" to make a new relationship.
- I select referential integrity and cascade update in the new
relationship,
click create.
- Access creates Client_1 with the new relationship from Client_1 to
ClientList

What is this? This new table is not in my list of tables. Does this mean
the relationship has been made as intended?

Furthermore, when I start adding play data into the tables, more and more
Client_X tables appear in my relationship view. I'm not exactly sure
about
the logic of how they arrive.

I've tried different combination of things, making one or more of the
fields
of ClientList primary keys, and not enforcing referential integery, it
doesn't seem to make a difference.

Cany anyone tell me how to make a many-to-many self-referential table
relationship in Access 2003?

I don't think there is any way to avoid this in my data or table design.

Thanks so much for any help,

Jennifer

You'll probably want to allow for the same contact being a client of more
than one of your clients. What this entails is a many-to-many
relationship
between the individuals involved. Normally a many to-many relationship
is
modelled between two tables by a third table, e.g.

Orders---<ProductsOrdered>----Products

In this relationship each order might be for many products and each
product
might be included in many orders. The ProductsOrdered table models this
by
having two foreign key columns OrderID and ProductID which reference the
primary keys of the Orders and Products table. The ProductsOrdered table
would also be likely to have other columns such as Quantity and
UnitPrice,
which are attributes of the relationship type which the table models.

In your case however, if you have a single table of all people whether
they
are your clients or clients of your clients, then the many-to-many
relationship is between two instances of the same table like so:

People---<ClientList>----People

The ClientList table in this case again has two foreign key columns,
ClientOfID and ClientID say, but in this case they both reference the
primary
key of the single table People (you can of course give these tables and
columns whatever names you wish). So if someone with a PersonID value of
42
has clients with PersonID values of 99 and 123 the ClientList table would
have the following rows:

ClientOfID ClientID
42 99
42 123

If PersonID 99 is also a client of someone else, PersonID 66, then there
would also be a row:

ClientOfID ClientID
66 99

But PersonID 99 might also have their own clients, say 135 and 177, so
there would be rows:

ClientOfID ClientID
99 135
99 177

If you are only interested in the first level of clientship for each
person
there is no problem; you simply join two instances of the people table to
the
ClientList table:

SELECT P1.Person, P2.Person AS Contact
FROM People AS P1, People AS P2, ClientList
WHERE P1.PersonID = ClientList.ClientOfID
AND P2.PersonID = ClientList.ClientID;

If you want to drill down through the levels of clientship, however, and
show the 'chain' of clients, sub-clients, sub-sub-clients etc for each
person, it gets trickier as this involves recursion, and queries cannot
be
recursive. This is analogous to the classic database problem of a 'bill
of
materials' or 'parts explosion'. To achieve recursion through a variable
number of levels is not trivial, but it is possible to achieve the same
result fairly simply for a fixed number of levels by a series of outer
joins
in a query. Here's an example using tables Parts and PartStructure which
does this for 9 levels:

SELECT P1.PartName AS Part,
[P2].[PartName]+(" (" & [PS1].[Quantity] & ")") AS Component1,
[P3].[PartName]+(" (" & [PS2].[Quantity] & ")") AS Component2,
[P4].[PartName]+(" (" & [PS3].[Quantity] & ")") AS Component3,
[P5].[PartName]+(" (" & [PS4].[Quantity] & ")") AS Component4,
[P6].[PartName]+(" (" & [PS5].[Quantity] & ")") AS Component5,
[P7].[PartName]+(" (" & [PS6].[Quantity] & ")") AS Component6,
[P8].[PartName]+(" (" & [PS7].[Quantity] & ")") AS Component7,
[P9].[PartName]+(" (" & [PS8].[Quantity] & ")") AS Component8,
[P10].[PartName]+(" (" & [PS9].[Quantity] & ")") AS Component9
FROM Parts AS P1
LEFT JOIN (((((((((((((((((PartStructure AS PS1
LEFT JOIN PartStructure AS PS2 ON PS1.MinorPartNum = PS2.MajorPartNum)
LEFT JOIN PartStructure AS PS3 ON PS2.MinorPartNum = PS3.MajorPartNum)
LEFT JOIN PartStructure AS PS4 ON PS3.MinorPartNum = PS4.MajorPartNum)
LEFT JOIN PartStructure AS PS5 ON PS4.MinorPartNum = PS5.MajorPartNum)
LEFT JOIN PartStructure AS PS6 ON PS5.MinorPartNum = PS6.MajorPartNum)
LEFT JOIN PartStructure AS PS7 ON PS6.MinorPartNum = PS7.MajorPartNum)
LEFT JOIN PartStructure AS PS8 ON PS7.MinorPartNum = PS8.MajorPartNum)
LEFT JOIN PartStructure AS PS9 ON PS8.MinorPartNum = PS9.MajorPartNum)
LEFT JOIN Parts AS P2 ON PS1.MinorPartNum = P2.PartNum)
LEFT JOIN Parts AS P3 ON PS2.MinorPartNum = P3.PartNum)
LEFT JOIN Parts AS P4 ON PS3.MinorPartNum = P4.PartNum)
LEFT JOIN Parts AS P5 ON PS4.MinorPartNum = P5.PartNum)
LEFT JOIN Parts AS P6 ON PS5.MinorPartNum = P6.PartNum)
LEFT JOIN Parts AS P7 ON PS6.MinorPartNum = P7.PartNum)
LEFT JOIN Parts AS P8 ON PS7.MinorPartNum = P8.PartNum)
LEFT JOIN Parts AS P9 ON PS8.MinorPartNum = P9.PartNum)
LEFT JOIN Parts AS P10 ON PS9.MinorPartNum = P10.PartNum)
ON P1.PartNum = PS1.MajorPartNum;

This query was actually written s the RecordSource for a report which
shows
the structure of a components in a tree-like format. It was produced,
along
with an example of how to produce a parts explosion over a variable
number of
levels, for a magazine article some years ago. The file won't be
available
on their web site now, but if you are interested I can send you a copy if
you
mail me at ken<dot>sheridan<at>dsl<dot>pipex<dot>com.

Ken Sheridan
Stafford, England

I am creating a client database for my business. Each of my clients
has
their own database of clients. I need to keep their database of
clients for
each of them. Is it possible to creat a contact within a contact

 

[idgity]

Hi, thanks for the reply.

I actually tried making this exact example in Access 2003, and it does not
create a Color_1 or Color_X table when the second relationship is added.
Even when I start adding in data, I only have a single Color Table in the
relationship view.

From my understanding of relational DBs, it's not that color "points" to
Widgets and Shelves, technically Color knows nothing about the things which
related to color, but instead Widgets and Shelves "point" to Color, as they
hold Color_ID as a foreign key. However, I see that that's not the way it
seems to work in Access when you start entering data. Instead of seeing an
"sub-table" for color in Widgets and Shelves, you are asked to pick between
Widgets or Shelves to fill in these "sub-tables" for a Color record.

So I'm not sure this is the same problem as the many-to-many self
referential question (although likely related). However, in a case as above
(and in my real data) I would very much like to be able to link color to both
Widgets and Shelves and make the relational links between Color and both of
these tables when entering data. As the UI for entering color information
makes me pick between Shelves or Widets, and not both, it appears that I
can't do this. Alternatively, I would really like to be able to update the
Color table when I update Widgets or Shelves, but I can't.

Are there any suggestions or workarounds for how to do this (i.e. use a
primary key as a foreign key in multiple tables?)

Thanks again,

Jennifer

I will try to explain. Suppose you have a table Colors with Id_color and
Color as its fields.
In the widgets table you have a foreign key (FK) of Id_color for the color
of the widget.
In the Shelves table you have Id_color for the color of the shelves. You are
wanting to have two records current in the same table. Which color is
Color.Id_color pointing to? You would have two lines on the relationship
diagram meeting at the same field. It would get very confusing.
Access solves this dilemma by having a "virtual" table Colors_1 so that you
can specify the right one for the widget and the shelves. The same idea
applies for table Colors_2 etc for other places this table is used.

Please ask again if I have not made it clear enough.

I have a problem similar to Rachelle's. I understand conceptually the idea
of a many-to-many self-referential relationship, with the need for a
second
table. My problem is that I cannot figure out how to create such a
situation
in Access 2003, using the typical GUI.

If we continue on with the Client and ClientList Example, starting with a
new DB:
- I create a simple Client Table with a text primary key (say ID).
- I create a ClientList Table with two fields ClientOf and Client (both
text).
- I then enter the access relationship view and add Client and ClientOf
- I make one relationship by dragging ID from client to ClientOf. I
select
enforce referential integrity and cascade update. This creates a
one-to-many
relationship with one client to many ClientLists.
- I then try to make the second relationship, I drag ID from Client now to
Client within ClientList. First I'm warned that a relationship already
exists, but I click "no" to make a new relationship.
- I select referential integrity and cascade update in the new
relationship,
click create.
- Access creates Client_1 with the new relationship from Client_1 to
ClientList

What is this? This new table is not in my list of tables. Does this mean
the relationship has been made as intended?

Furthermore, when I start adding play data into the tables, more and more
Client_X tables appear in my relationship view. I'm not exactly sure
about
the logic of how they arrive.

I've tried different combination of things, making one or more of the
fields
of ClientList primary keys, and not enforcing referential integery, it
doesn't seem to make a difference.

Cany anyone tell me how to make a many-to-many self-referential table
relationship in Access 2003?

I don't think there is any way to avoid this in my data or table design.

Thanks so much for any help,

Jennifer

You'll probably want to allow for the same contact being a client of more
than one of your clients. What this entails is a many-to-many
relationship
between the individuals involved. Normally a many to-many relationship
is
modelled between two tables by a third table, e.g.

Orders---<ProductsOrdered>----Products

In this relationship each order might be for many products and each
product
might be included in many orders. The ProductsOrdered table models this
by
having two foreign key columns OrderID and ProductID which reference the
primary keys of the Orders and Products table. The ProductsOrdered table
would also be likely to have other columns such as Quantity and
UnitPrice,
which are attributes of the relationship type which the table models.

In your case however, if you have a single table of all people whether
they
are your clients or clients of your clients, then the many-to-many
relationship is between two instances of the same table like so:

People---<ClientList>----People

The ClientList table in this case again has two foreign key columns,
ClientOfID and ClientID say, but in this case they both reference the
primary
key of the single table People (you can of course give these tables and
columns whatever names you wish). So if someone with a PersonID value of
42
has clients with PersonID values of 99 and 123 the ClientList table would
have the following rows:

ClientOfID ClientID
42 99
42 123

If PersonID 99 is also a client of someone else, PersonID 66, then there
would also be a row:

ClientOfID ClientID
66 99

But PersonID 99 might also have their own clients, say 135 and 177, so
there would be rows:

ClientOfID ClientID
99 135
99 177

If you are only interested in the first level of clientship for each
person
there is no problem; you simply join two instances of the people table to
the
ClientList table:

SELECT P1.Person, P2.Person AS Contact
FROM People AS P1, People AS P2, ClientList
WHERE P1.PersonID = ClientList.ClientOfID
AND P2.PersonID = ClientList.ClientID;

If you want to drill down through the levels of clientship, however, and
show the 'chain' of clients, sub-clients, sub-sub-clients etc for each
person, it gets trickier as this involves recursion, and queries cannot
be
recursive. This is analogous to the classic database problem of a 'bill
of
materials' or 'parts explosion'. To achieve recursion through a variable
number of levels is not trivial, but it is possible to achieve the same
result fairly simply for a fixed number of levels by a series of outer
joins
in a query. Here's an example using tables Parts and PartStructure which
does this for 9 levels:

SELECT P1.PartName AS Part,
[P2].[PartName]+(" (" & [PS1].[Quantity] & ")") AS Component1,
[P3].[PartName]+(" (" & [PS2].[Quantity] & ")") AS Component2,
[P4].[PartName]+(" (" & [PS3].[Quantity] & ")") AS Component3,
[P5].[PartName]+(" (" & [PS4].[Quantity] & ")") AS Component4,
[P6].[PartName]+(" (" & [PS5].[Quantity] & ")") AS Component5,
[P7].[PartName]+(" (" & [PS6].[Quantity] & ")") AS Component6,
[P8].[PartName]+(" (" & [PS7].[Quantity] & ")") AS Component7,
[P9].[PartName]+(" (" & [PS8].[Quantity] & ")") AS Component8,
[P10].[PartName]+(" (" & [PS9].[Quantity] & ")") AS Component9
FROM Parts AS P1
LEFT JOIN (((((((((((((((((PartStructure AS PS1
LEFT JOIN PartStructure AS PS2 ON PS1.MinorPartNum = PS2.MajorPartNum)
LEFT JOIN PartStructure AS PS3 ON PS2.MinorPartNum = PS3.MajorPartNum)
LEFT JOIN PartStructure AS PS4 ON PS3.MinorPartNum = PS4.MajorPartNum)
LEFT JOIN PartStructure AS PS5 ON PS4.MinorPartNum = PS5.MajorPartNum)
LEFT JOIN PartStructure AS PS6 ON PS5.MinorPartNum = PS6.MajorPartNum)
LEFT JOIN PartStructure AS PS7 ON PS6.MinorPartNum = PS7.MajorPartNum)
LEFT JOIN PartStructure AS PS8 ON PS7.MinorPartNum = PS8.MajorPartNum)
LEFT JOIN PartStructure AS PS9 ON PS8.MinorPartNum = PS9.MajorPartNum)
LEFT JOIN Parts AS P2 ON PS1.MinorPartNum = P2.PartNum)
LEFT JOIN Parts AS P3 ON PS2.MinorPartNum = P3.PartNum)
LEFT JOIN Parts AS P4 ON PS3.MinorPartNum = P4.PartNum)
LEFT JOIN Parts AS P5 ON PS4.MinorPartNum = P5.PartNum)
LEFT JOIN Parts AS P6 ON PS5.MinorPartNum = P6.PartNum)
LEFT JOIN Parts AS P7 ON PS6.MinorPartNum = P7.PartNum)
LEFT JOIN Parts AS P8 ON PS7.MinorPartNum = P8.PartNum)
LEFT JOIN Parts AS P9 ON PS8.MinorPartNum = P9.PartNum)
LEFT JOIN Parts AS P10 ON PS9.MinorPartNum = P10.PartNum)
ON P1.PartNum = PS1.MajorPartNum;

This query was actually written s the RecordSource for a report which
shows
the structure of a components in a tree-like format. It was produced,
along
with an example of how to produce a parts explosion over a variable
number of
levels, for a magazine article some years ago. The file won't be
available
on their web site now, but if you are interested I can send you a copy if
you
mail me at ken<dot>sheridan<at>dsl<dot>pipex<dot>com.

Ken Sheridan
Stafford, England

:

I am creating a client database for my business. Each of my clients
has
their own database of clients. I need to keep their database of
clients for
each of them. Is it possible to creat a contact within a contact

 

[David F Cox]

I apologise, I did not explain that at all well, and led you astray.
Unfortunately when you do something for along time it becomes automatic, and
you do not think about it any more. I should have walked through an example
instead of believing I could do it in my head.

The _1 tables are "created" when there is more than one relationship between
tables. The basic idea of there having to be a way of identifying which
record one is referring to is right. I should have used an example where
there is more than one brand or quality of a color perhaps,

In Access the standard way of accessing tables is to build a form based upon
a query based upon that table. Often the query is just there to sort the
data into a convenient order. Related tables can be accessed in a subform,
or using List or Combo box controls. In my examples these would be based on
the color table, or on a query based on the colour table. You might want the
colors in alphabetical order, for example. You can use the table or a query
as an input to one of the control creation wizards. The list and combo box
controls have events associated with them which call up Visual Basic
subroutines. The common one is the Not_in_list event which is commonly used
to add new items, in this case colors.
Forms, subforms and controls give a very powerful way of displaying and
editing related information in multiple tables.It really is advisable to
read a good book, and play with sample databases and use the help files and
search newsgroups and the web.. Everything that you want to do has probably
already been done, and is probably on show somewhere.

HTH

David F Cox

Hi, thanks for the reply.

I actually tried making this exact example in Access 2003, and it does not
create a Color_1 or Color_X table when the second relationship is added.
Even when I start adding in data, I only have a single Color Table in the
relationship view.

From my understanding of relational DBs, it's not that color "points" to
Widgets and Shelves, technically Color knows nothing about the things
which
related to color, but instead Widgets and Shelves "point" to Color, as
they
hold Color_ID as a foreign key. However, I see that that's not the way it
seems to work in Access when you start entering data. Instead of seeing
an
"sub-table" for color in Widgets and Shelves, you are asked to pick
between
Widgets or Shelves to fill in these "sub-tables" for a Color record.

So I'm not sure this is the same problem as the many-to-many self
referential question (although likely related). However, in a case as
above
(and in my real data) I would very much like to be able to link color to
both
Widgets and Shelves and make the relational links between Color and both
of
these tables when entering data. As the UI for entering color information
makes me pick between Shelves or Widets, and not both, it appears that I
can't do this. Alternatively, I would really like to be able to update
the
Color table when I update Widgets or Shelves, but I can't.

Are there any suggestions or workarounds for how to do this (i.e. use a
primary key as a foreign key in multiple tables?)

Thanks again,

Jennifer

I will try to explain. Suppose you have a table Colors with Id_color and
Color as its fields.
In the widgets table you have a foreign key (FK) of Id_color for the
color
of the widget.
In the Shelves table you have Id_color for the color of the shelves. You
are
wanting to have two records current in the same table. Which color is
Color.Id_color pointing to? You would have two lines on the relationship
diagram meeting at the same field. It would get very confusing.
Access solves this dilemma by having a "virtual" table Colors_1 so that
you
can specify the right one for the widget and the shelves. The same idea
applies for table Colors_2 etc for other places this table is used.

Please ask again if I have not made it clear enough.

I have a problem similar to Rachelle's. I understand conceptually the
idea
of a many-to-many self-referential relationship, with the need for a
second
table. My problem is that I cannot figure out how to create such a
situation
in Access 2003, using the typical GUI.

If we continue on with the Client and ClientList Example, starting with
a
new DB:
- I create a simple Client Table with a text primary key (say ID).
- I create a ClientList Table with two fields ClientOf and Client (both
text).
- I then enter the access relationship view and add Client and ClientOf
- I make one relationship by dragging ID from client to ClientOf. I
select
enforce referential integrity and cascade update. This creates a
one-to-many
relationship with one client to many ClientLists.
- I then try to make the second relationship, I drag ID from Client now
to
Client within ClientList. First I'm warned that a relationship already
exists, but I click "no" to make a new relationship.
- I select referential integrity and cascade update in the new
relationship,
click create.
- Access creates Client_1 with the new relationship from Client_1 to
ClientList

What is this? This new table is not in my list of tables. Does this
mean
the relationship has been made as intended?

Furthermore, when I start adding play data into the tables, more and
more
Client_X tables appear in my relationship view. I'm not exactly sure
about
the logic of how they arrive.

I've tried different combination of things, making one or more of the
fields
of ClientList primary keys, and not enforcing referential integery, it
doesn't seem to make a difference.

Cany anyone tell me how to make a many-to-many self-referential table
relationship in Access 2003?

I don't think there is any way to avoid this in my data or table
design.

Thanks so much for any help,

Jennifer

:

You'll probably want to allow for the same contact being a client of
more
than one of your clients. What this entails is a many-to-many
relationship
between the individuals involved. Normally a many to-many
relationship
is
modelled between two tables by a third table, e.g.

Orders---<ProductsOrdered>----Products

In this relationship each order might be for many products and each
product
might be included in many orders. The ProductsOrdered table models
this
by
having two foreign key columns OrderID and ProductID which reference
the
primary keys of the Orders and Products table. The ProductsOrdered
table
would also be likely to have other columns such as Quantity and
UnitPrice,
which are attributes of the relationship type which the table models.

In your case however, if you have a single table of all people whether
they
are your clients or clients of your clients, then the many-to-many
relationship is between two instances of the same table like so:

People---<ClientList>----People

The ClientList table in this case again has two foreign key columns,
ClientOfID and ClientID say, but in this case they both reference the
primary
key of the single table People (you can of course give these tables
and
columns whatever names you wish). So if someone with a PersonID value
of
42
has clients with PersonID values of 99 and 123 the ClientList table
would
have the following rows:

ClientOfID ClientID
42 99
42 123

If PersonID 99 is also a client of someone else, PersonID 66, then
there
would also be a row:

ClientOfID ClientID
66 99

But PersonID 99 might also have their own clients, say 135 and 177,
so
there would be rows:

ClientOfID ClientID
99 135
99 177

If you are only interested in the first level of clientship for each
person
there is no problem; you simply join two instances of the people table
to
the
ClientList table:

SELECT P1.Person, P2.Person AS Contact
FROM People AS P1, People AS P2, ClientList
WHERE P1.PersonID = ClientList.ClientOfID
AND P2.PersonID = ClientList.ClientID;

If you want to drill down through the levels of clientship, however,
and
show the 'chain' of clients, sub-clients, sub-sub-clients etc for each
person, it gets trickier as this involves recursion, and queries
cannot
be
recursive. This is analogous to the classic database problem of a
'bill
of
materials' or 'parts explosion'. To achieve recursion through a
variable
number of levels is not trivial, but it is possible to achieve the
same
result fairly simply for a fixed number of levels by a series of outer
joins
in a query. Here's an example using tables Parts and PartStructure
which
does this for 9 levels:

SELECT P1.PartName AS Part,
[P2].[PartName]+(" (" & [PS1].[Quantity] & ")") AS Component1,
[P3].[PartName]+(" (" & [PS2].[Quantity] & ")") AS Component2,
[P4].[PartName]+(" (" & [PS3].[Quantity] & ")") AS Component3,
[P5].[PartName]+(" (" & [PS4].[Quantity] & ")") AS Component4,
[P6].[PartName]+(" (" & [PS5].[Quantity] & ")") AS Component5,
[P7].[PartName]+(" (" & [PS6].[Quantity] & ")") AS Component6,
[P8].[PartName]+(" (" & [PS7].[Quantity] & ")") AS Component7,
[P9].[PartName]+(" (" & [PS8].[Quantity] & ")") AS Component8,
[P10].[PartName]+(" (" & [PS9].[Quantity] & ")") AS Component9
FROM Parts AS P1
LEFT JOIN (((((((((((((((((PartStructure AS PS1
LEFT JOIN PartStructure AS PS2 ON PS1.MinorPartNum = PS2.MajorPartNum)
LEFT JOIN PartStructure AS PS3 ON PS2.MinorPartNum = PS3.MajorPartNum)
LEFT JOIN PartStructure AS PS4 ON PS3.MinorPartNum = PS4.MajorPartNum)
LEFT JOIN PartStructure AS PS5 ON PS4.MinorPartNum = PS5.MajorPartNum)
LEFT JOIN PartStructure AS PS6 ON PS5.MinorPartNum = PS6.MajorPartNum)
LEFT JOIN PartStructure AS PS7 ON PS6.MinorPartNum = PS7.MajorPartNum)
LEFT JOIN PartStructure AS PS8 ON PS7.MinorPartNum = PS8.MajorPartNum)
LEFT JOIN PartStructure AS PS9 ON PS8.MinorPartNum = PS9.MajorPartNum)
LEFT JOIN Parts AS P2 ON PS1.MinorPartNum = P2.PartNum)
LEFT JOIN Parts AS P3 ON PS2.MinorPartNum = P3.PartNum)
LEFT JOIN Parts AS P4 ON PS3.MinorPartNum = P4.PartNum)
LEFT JOIN Parts AS P5 ON PS4.MinorPartNum = P5.PartNum)
LEFT JOIN Parts AS P6 ON PS5.MinorPartNum = P6.PartNum)
LEFT JOIN Parts AS P7 ON PS6.MinorPartNum = P7.PartNum)
LEFT JOIN Parts AS P8 ON PS7.MinorPartNum = P8.PartNum)
LEFT JOIN Parts AS P9 ON PS8.MinorPartNum = P9.PartNum)
LEFT JOIN Parts AS P10 ON PS9.MinorPartNum = P10.PartNum)
ON P1.PartNum = PS1.MajorPartNum;

This query was actually written s the RecordSource for a report which
shows
the structure of a components in a tree-like format. It was produced,
along
with an example of how to produce a parts explosion over a variable
number of
levels, for a magazine article some years ago. The file won't be
available
on their web site now, but if you are interested I can send you a copy
if
you
mail me at ken<dot>sheridan<at>dsl<dot>pipex<dot>com.

Ken Sheridan
Stafford, England

:

I am creating a client database for my business. Each of my clients
has
their own database of clients. I need to keep their database of
clients for
each of them. Is it possible to creat a contact within a contact

 

[BruceM]

Would the Colors table need to be added twice to the Relationships window,
or does adding the second relationship create it automatically?

I apologise, I did not explain that at all well, and led you astray.
Unfortunately when you do something for along time it becomes automatic,
and you do not think about it any more. I should have walked through an
example instead of believing I could do it in my head.

The _1 tables are "created" when there is more than one relationship
between tables. The basic idea of there having to be a way of identifying
which record one is referring to is right. I should have used an example
where there is more than one brand or quality of a color perhaps,

In Access the standard way of accessing tables is to build a form based
upon a query based upon that table. Often the query is just there to sort
the data into a convenient order. Related tables can be accessed in a
subform, or using List or Combo box controls. In my examples these would
be based on the color table, or on a query based on the colour table. You
might want the colors in alphabetical order, for example. You can use the
table or a query as an input to one of the control creation wizards. The
list and combo box controls have events associated with them which call up
Visual Basic subroutines. The common one is the Not_in_list event which is
commonly used to add new items, in this case colors.
Forms, subforms and controls give a very powerful way of displaying and
editing related information in multiple tables.It really is advisable to
read a good book, and play with sample databases and use the help files
and search newsgroups and the web.. Everything that you want to do has
probably already been done, and is probably on show somewhere.

HTH

David F Cox

Hi, thanks for the reply.

I actually tried making this exact example in Access 2003, and it does
not
create a Color_1 or Color_X table when the second relationship is added.
Even when I start adding in data, I only have a single Color Table in the
relationship view.

From my understanding of relational DBs, it's not that color "points" to
Widgets and Shelves, technically Color knows nothing about the things
which
related to color, but instead Widgets and Shelves "point" to Color, as
they
hold Color_ID as a foreign key. However, I see that that's not the way
it
seems to work in Access when you start entering data. Instead of seeing
an
"sub-table" for color in Widgets and Shelves, you are asked to pick
between
Widgets or Shelves to fill in these "sub-tables" for a Color record.

So I'm not sure this is the same problem as the many-to-many self
referential question (although likely related). However, in a case as
above
(and in my real data) I would very much like to be able to link color to
both
Widgets and Shelves and make the relational links between Color and both
of
these tables when entering data. As the UI for entering color
information
makes me pick between Shelves or Widets, and not both, it appears that I
can't do this. Alternatively, I would really like to be able to update
the
Color table when I update Widgets or Shelves, but I can't.

Are there any suggestions or workarounds for how to do this (i.e. use a
primary key as a foreign key in multiple tables?)

Thanks again,

Jennifer

I will try to explain. Suppose you have a table Colors with Id_color and
Color as its fields.
In the widgets table you have a foreign key (FK) of Id_color for the
color
of the widget.
In the Shelves table you have Id_color for the color of the shelves. You
are
wanting to have two records current in the same table. Which color is
Color.Id_color pointing to? You would have two lines on the
relationship
diagram meeting at the same field. It would get very confusing.
Access solves this dilemma by having a "virtual" table Colors_1 so that
you
can specify the right one for the widget and the shelves. The same idea
applies for table Colors_2 etc for other places this table is used.

Please ask again if I have not made it clear enough.


I have a problem similar to Rachelle's. I understand conceptually the
idea
of a many-to-many self-referential relationship, with the need for a
second
table. My problem is that I cannot figure out how to create such a
situation
in Access 2003, using the typical GUI.

If we continue on with the Client and ClientList Example, starting
with a
new DB:
- I create a simple Client Table with a text primary key (say ID).
- I create a ClientList Table with two fields ClientOf and Client
(both
text).
- I then enter the access relationship view and add Client and
ClientOf
- I make one relationship by dragging ID from client to ClientOf. I
select
enforce referential integrity and cascade update. This creates a
one-to-many
relationship with one client to many ClientLists.
- I then try to make the second relationship, I drag ID from Client
now to
Client within ClientList. First I'm warned that a relationship
already
exists, but I click "no" to make a new relationship.
- I select referential integrity and cascade update in the new
relationship,
click create.
- Access creates Client_1 with the new relationship from Client_1 to
ClientList

What is this? This new table is not in my list of tables. Does this
mean
the relationship has been made as intended?

Furthermore, when I start adding play data into the tables, more and
more
Client_X tables appear in my relationship view. I'm not exactly sure
about
the logic of how they arrive.

I've tried different combination of things, making one or more of the
fields
of ClientList primary keys, and not enforcing referential integery, it
doesn't seem to make a difference.

Cany anyone tell me how to make a many-to-many self-referential table
relationship in Access 2003?

I don't think there is any way to avoid this in my data or table
design.

Thanks so much for any help,

Jennifer

:

You'll probably want to allow for the same contact being a client of
more
than one of your clients. What this entails is a many-to-many
relationship
between the individuals involved. Normally a many to-many
relationship
is
modelled between two tables by a third table, e.g.

Orders---<ProductsOrdered>----Products

In this relationship each order might be for many products and each
product
might be included in many orders. The ProductsOrdered table models
this
by
having two foreign key columns OrderID and ProductID which reference
the
primary keys of the Orders and Products table. The ProductsOrdered
table
would also be likely to have other columns such as Quantity and
UnitPrice,
which are attributes of the relationship type which the table models.

In your case however, if you have a single table of all people
whether
they
are your clients or clients of your clients, then the many-to-many
relationship is between two instances of the same table like so:

People---<ClientList>----People

The ClientList table in this case again has two foreign key columns,
ClientOfID and ClientID say, but in this case they both reference the
primary
key of the single table People (you can of course give these tables
and
columns whatever names you wish). So if someone with a PersonID
value of
42
has clients with PersonID values of 99 and 123 the ClientList table
would
have the following rows:

ClientOfID ClientID
42 99
42 123

If PersonID 99 is also a client of someone else, PersonID 66, then
there
would also be a row:

ClientOfID ClientID
66 99

But PersonID 99 might also have their own clients, say 135 and 177,
so
there would be rows:

ClientOfID ClientID
99 135
99 177

If you are only interested in the first level of clientship for each
person
there is no problem; you simply join two instances of the people
table to
the
ClientList table:

SELECT P1.Person, P2.Person AS Contact
FROM People AS P1, People AS P2, ClientList
WHERE P1.PersonID = ClientList.ClientOfID
AND P2.PersonID = ClientList.ClientID;

If you want to drill down through the levels of clientship, however,
and
show the 'chain' of clients, sub-clients, sub-sub-clients etc for
each
person, it gets trickier as this involves recursion, and queries
cannot
be
recursive. This is analogous to the classic database problem of a
'bill
of
materials' or 'parts explosion'. To achieve recursion through a
variable
number of levels is not trivial, but it is possible to achieve the
same
result fairly simply for a fixed number of levels by a series of
outer
joins
in a query. Here's an example using tables Parts and PartStructure
which
does this for 9 levels:

SELECT P1.PartName AS Part,
[P2].[PartName]+(" (" & [PS1].[Quantity] & ")") AS Component1,
[P3].[PartName]+(" (" & [PS2].[Quantity] & ")") AS Component2,
[P4].[PartName]+(" (" & [PS3].[Quantity] & ")") AS Component3,
[P5].[PartName]+(" (" & [PS4].[Quantity] & ")") AS Component4,
[P6].[PartName]+(" (" & [PS5].[Quantity] & ")") AS Component5,
[P7].[PartName]+(" (" & [PS6].[Quantity] & ")") AS Component6,
[P8].[PartName]+(" (" & [PS7].[Quantity] & ")") AS Component7,
[P9].[PartName]+(" (" & [PS8].[Quantity] & ")") AS Component8,
[P10].[PartName]+(" (" & [PS9].[Quantity] & ")") AS Component9
FROM Parts AS P1
LEFT JOIN (((((((((((((((((PartStructure AS PS1
LEFT JOIN PartStructure AS PS2 ON PS1.MinorPartNum =
PS2.MajorPartNum)
LEFT JOIN PartStructure AS PS3 ON PS2.MinorPartNum =
PS3.MajorPartNum)
LEFT JOIN PartStructure AS PS4 ON PS3.MinorPartNum =
PS4.MajorPartNum)
LEFT JOIN PartStructure AS PS5 ON PS4.MinorPartNum =
PS5.MajorPartNum)
LEFT JOIN PartStructure AS PS6 ON PS5.MinorPartNum =
PS6.MajorPartNum)
LEFT JOIN PartStructure AS PS7 ON PS6.MinorPartNum =
PS7.MajorPartNum)
LEFT JOIN PartStructure AS PS8 ON PS7.MinorPartNum =
PS8.MajorPartNum)
LEFT JOIN PartStructure AS PS9 ON PS8.MinorPartNum =
PS9.MajorPartNum)
LEFT JOIN Parts AS P2 ON PS1.MinorPartNum = P2.PartNum)
LEFT JOIN Parts AS P3 ON PS2.MinorPartNum = P3.PartNum)
LEFT JOIN Parts AS P4 ON PS3.MinorPartNum = P4.PartNum)
LEFT JOIN Parts AS P5 ON PS4.MinorPartNum = P5.PartNum)
LEFT JOIN Parts AS P6 ON PS5.MinorPartNum = P6.PartNum)
LEFT JOIN Parts AS P7 ON PS6.MinorPartNum = P7.PartNum)
LEFT JOIN Parts AS P8 ON PS7.MinorPartNum = P8.PartNum)
LEFT JOIN Parts AS P9 ON PS8.MinorPartNum = P9.PartNum)
LEFT JOIN Parts AS P10 ON PS9.MinorPartNum = P10.PartNum)
ON P1.PartNum = PS1.MajorPartNum;

This query was actually written s the RecordSource for a report which
shows
the structure of a components in a tree-like format. It was
produced,
along
with an example of how to produce a parts explosion over a variable
number of
levels, for a magazine article some years ago. The file won't be
available
on their web site now, but if you are interested I can send you a
copy if
you
mail me at ken<dot>sheridan<at>dsl<dot>pipex<dot>com.

Ken Sheridan
Stafford, England

:

I am creating a client database for my business. Each of my
clients
has
their own database of clients. I need to keep their database of
clients for
each of them. Is it possible to creat a contact within a contact

 

[Ken Sheridan]

Jennifer:

Apologies for the delay in my reply. You are quite right about David's reply
relating to a different logical model to that about which you are asking, so
I'll start at square one.

What you see in the relationships window are two instances of the same
Clients table, one given an alias to distinguish it from the other. These
are merely representations of the same table. To model a self referencing
many-to-many relationship you'd do as follows:

1. Add the Clients table to the relationships window from the Show Table
dialogue.
2. Add the Clients table again from the dialogue. It will now be given an
alias, with a '_1' suffix added to the table name.
3. Add the ClientList table to the relationships window from the Show Table
dialogue.
4. Create the relationship between the first instance of the Clients table
and Client list by dragging form the ID field of Clients to the Clients field
of ClientList. Enforce relational integrity and cascade updates and deletes.
5. Create the relationship between the second instance of the Clients table
and Client list by dragging form the ID field of Clients to the ClientOf
field of ClientList. Enforce relational integrity and cascade updates and
deletes.

You should also make the Client and ClientOf columns of ClientList its
composite primary key, and index the Client and ClientOf columns separately
non-uniquely (duplicates allowed).

You'll have noticed I've referred to a Clients table not a Client table in
the above, The usual convention is for table names to be plural or
collective nouns (so ClientList is fine as it falls in the latter category),
and singular nouns for column names, e.g. ClientName, as each column
represents an attribute of the entity type (Clients) which the table models.
If you stick to these conventions it makes VBA code and (particularly) SQL
more easily written and read.

Ken Sheridan
Stafford, England

 

[idgity]

Ok, I will have to learn more about forms for adding my data.

Thanks,

Jennifer

I apologise, I did not explain that at all well, and led you astray.
Unfortunately when you do something for along time it becomes automatic, and
you do not think about it any more. I should have walked through an example
instead of believing I could do it in my head.

The _1 tables are "created" when there is more than one relationship between
tables. The basic idea of there having to be a way of identifying which
record one is referring to is right. I should have used an example where
there is more than one brand or quality of a color perhaps,

In Access the standard way of accessing tables is to build a form based upon
a query based upon that table. Often the query is just there to sort the
data into a convenient order. Related tables can be accessed in a subform,
or using List or Combo box controls. In my examples these would be based on
the color table, or on a query based on the colour table. You might want the
colors in alphabetical order, for example. You can use the table or a query
as an input to one of the control creation wizards. The list and combo box
controls have events associated with them which call up Visual Basic
subroutines. The common one is the Not_in_list event which is commonly used
to add new items, in this case colors.
Forms, subforms and controls give a very powerful way of displaying and
editing related information in multiple tables.It really is advisable to
read a good book, and play with sample databases and use the help files and
search newsgroups and the web.. Everything that you want to do has probably
already been done, and is probably on show somewhere.

HTH

David F Cox

Hi, thanks for the reply.

I actually tried making this exact example in Access 2003, and it does not
create a Color_1 or Color_X table when the second relationship is added.
Even when I start adding in data, I only have a single Color Table in the
relationship view.

From my understanding of relational DBs, it's not that color "points" to
Widgets and Shelves, technically Color knows nothing about the things
which
related to color, but instead Widgets and Shelves "point" to Color, as
they
hold Color_ID as a foreign key. However, I see that that's not the way it
seems to work in Access when you start entering data. Instead of seeing
an
"sub-table" for color in Widgets and Shelves, you are asked to pick
between
Widgets or Shelves to fill in these "sub-tables" for a Color record.

So I'm not sure this is the same problem as the many-to-many self
referential question (although likely related). However, in a case as
above
(and in my real data) I would very much like to be able to link color to
both
Widgets and Shelves and make the relational links between Color and both
of
these tables when entering data. As the UI for entering color information
makes me pick between Shelves or Widets, and not both, it appears that I
can't do this. Alternatively, I would really like to be able to update
the
Color table when I update Widgets or Shelves, but I can't.

Are there any suggestions or workarounds for how to do this (i.e. use a
primary key as a foreign key in multiple tables?)

Thanks again,

Jennifer

I will try to explain. Suppose you have a table Colors with Id_color and
Color as its fields.
In the widgets table you have a foreign key (FK) of Id_color for the
color
of the widget.
In the Shelves table you have Id_color for the color of the shelves. You
are
wanting to have two records current in the same table. Which color is
Color.Id_color pointing to? You would have two lines on the relationship
diagram meeting at the same field. It would get very confusing.
Access solves this dilemma by having a "virtual" table Colors_1 so that
you
can specify the right one for the widget and the shelves. The same idea
applies for table Colors_2 etc for other places this table is used.

Please ask again if I have not made it clear enough.


I have a problem similar to Rachelle's. I understand conceptually the
idea
of a many-to-many self-referential relationship, with the need for a
second
table. My problem is that I cannot figure out how to create such a
situation
in Access 2003, using the typical GUI.

If we continue on with the Client and ClientList Example, starting with
a
new DB:
- I create a simple Client Table with a text primary key (say ID).
- I create a ClientList Table with two fields ClientOf and Client (both
text).
- I then enter the access relationship view and add Client and ClientOf
- I make one relationship by dragging ID from client to ClientOf. I
select
enforce referential integrity and cascade update. This creates a
one-to-many
relationship with one client to many ClientLists.
- I then try to make the second relationship, I drag ID from Client now
to
Client within ClientList. First I'm warned that a relationship already
exists, but I click "no" to make a new relationship.
- I select referential integrity and cascade update in the new
relationship,
click create.
- Access creates Client_1 with the new relationship from Client_1 to
ClientList

What is this? This new table is not in my list of tables. Does this
mean
the relationship has been made as intended?

Furthermore, when I start adding play data into the tables, more and
more
Client_X tables appear in my relationship view. I'm not exactly sure
about
the logic of how they arrive.

I've tried different combination of things, making one or more of the
fields
of ClientList primary keys, and not enforcing referential integery, it
doesn't seem to make a difference.

Cany anyone tell me how to make a many-to-many self-referential table
relationship in Access 2003?

I don't think there is any way to avoid this in my data or table
design.

Thanks so much for any help,

Jennifer

:

You'll probably want to allow for the same contact being a client of
more
than one of your clients. What this entails is a many-to-many
relationship
between the individuals involved. Normally a many to-many
relationship
is
modelled between two tables by a third table, e.g.

Orders---<ProductsOrdered>----Products

In this relationship each order might be for many products and each
product
might be included in many orders. The ProductsOrdered table models
this
by
having two foreign key columns OrderID and ProductID which reference
the
primary keys of the Orders and Products table. The ProductsOrdered
table
would also be likely to have other columns such as Quantity and
UnitPrice,
which are attributes of the relationship type which the table models.

In your case however, if you have a single table of all people whether
they
are your clients or clients of your clients, then the many-to-many
relationship is between two instances of the same table like so:

People---<ClientList>----People

The ClientList table in this case again has two foreign key columns,
ClientOfID and ClientID say, but in this case they both reference the
primary
key of the single table People (you can of course give these tables
and
columns whatever names you wish). So if someone with a PersonID value
of
42
has clients with PersonID values of 99 and 123 the ClientList table
would
have the following rows:

ClientOfID ClientID
42 99
42 123

If PersonID 99 is also a client of someone else, PersonID 66, then
there
would also be a row:

ClientOfID ClientID
66 99

But PersonID 99 might also have their own clients, say 135 and 177,
so
there would be rows:

ClientOfID ClientID
99 135
99 177

If you are only interested in the first level of clientship for each
person
there is no problem; you simply join two instances of the people table
to
the
ClientList table:

SELECT P1.Person, P2.Person AS Contact
FROM People AS P1, People AS P2, ClientList
WHERE P1.PersonID = ClientList.ClientOfID
AND P2.PersonID = ClientList.ClientID;

If you want to drill down through the levels of clientship, however,
and
show the 'chain' of clients, sub-clients, sub-sub-clients etc for each
person, it gets trickier as this involves recursion, and queries
cannot
be
recursive. This is analogous to the classic database problem of a
'bill
of
materials' or 'parts explosion'. To achieve recursion through a
variable
number of levels is not trivial, but it is possible to achieve the
same
result fairly simply for a fixed number of levels by a series of outer
joins
in a query. Here's an example using tables Parts and PartStructure
which
does this for 9 levels:

SELECT P1.PartName AS Part,
[P2].[PartName]+(" (" & [PS1].[Quantity] & ")") AS Component1,
[P3].[PartName]+(" (" & [PS2].[Quantity] & ")") AS Component2,
[P4].[PartName]+(" (" & [PS3].[Quantity] & ")") AS Component3,
[P5].[PartName]+(" (" & [PS4].[Quantity] & ")") AS Component4,
[P6].[PartName]+(" (" & [PS5].[Quantity] & ")") AS Component5,
[P7].[PartName]+(" (" & [PS6].[Quantity] & ")") AS Component6,
[P8].[PartName]+(" (" & [PS7].[Quantity] & ")") AS Component7,
[P9].[PartName]+(" (" & [PS8].[Quantity] & ")") AS Component8,
[P10].[PartName]+(" (" & [PS9].[Quantity] & ")") AS Component9
FROM Parts AS P1
LEFT JOIN (((((((((((((((((PartStructure AS PS1
LEFT JOIN PartStructure AS PS2 ON PS1.MinorPartNum = PS2.MajorPartNum)
LEFT JOIN PartStructure AS PS3 ON PS2.MinorPartNum = PS3.MajorPartNum)
LEFT JOIN PartStructure AS PS4 ON PS3.MinorPartNum = PS4.MajorPartNum)
LEFT JOIN PartStructure AS PS5 ON PS4.MinorPartNum = PS5.MajorPartNum)
LEFT JOIN PartStructure AS PS6 ON PS5.MinorPartNum = PS6.MajorPartNum)
LEFT JOIN PartStructure AS PS7 ON PS6.MinorPartNum = PS7.MajorPartNum)
LEFT JOIN PartStructure AS PS8 ON PS7.MinorPartNum = PS8.MajorPartNum)
LEFT JOIN PartStructure AS PS9 ON PS8.MinorPartNum = PS9.MajorPartNum)
LEFT JOIN Parts AS P2 ON PS1.MinorPartNum = P2.PartNum)
LEFT JOIN Parts AS P3 ON PS2.MinorPartNum = P3.PartNum)
LEFT JOIN Parts AS P4 ON PS3.MinorPartNum = P4.PartNum)
LEFT JOIN Parts AS P5 ON PS4.MinorPartNum = P5.PartNum)
LEFT JOIN Parts AS P6 ON PS5.MinorPartNum = P6.PartNum)
LEFT JOIN Parts AS P7 ON PS6.MinorPartNum = P7.PartNum)
LEFT JOIN Parts AS P8 ON PS7.MinorPartNum = P8.PartNum)
LEFT JOIN Parts AS P9 ON PS8.MinorPartNum = P9.PartNum)
LEFT JOIN Parts AS P10 ON PS9.MinorPartNum = P10.PartNum)
ON P1.PartNum = PS1.MajorPartNum;

This query was actually written s the RecordSource for a report which
shows
the structure of a components in a tree-like format. It was produced,
along
with an example of how to produce a parts explosion over a variable
number of
levels, for a magazine article some years ago. The file won't be
available
on their web site now, but if you are interested I can send you a copy
if
you
mail me at ken<dot>sheridan<at>dsl<dot>pipex<dot>com.

Ken Sheridan
Stafford, England

:

I am creating a client database for my business. Each of my clients
has
their own database of clients. I need to keep their database of
clients for
each of them. Is it possible to creat a contact within a contact

 

[idgity]

Hello,

Thanks a lot for the help. Your instructions seem to work well for both the
client example and my own DB design. The relationships are behaving as I
would expect.

The only issue I have now is being able to see the related data in order to
add/edit the data. For example, when I open up the Clients table and expand
the "+" for a record, the subdatasheet of Client List does not actually have
any fields in it. Maybe Access is automatically assuming that Client and
ClientOf in ClientList are equal to the ClientID of the Client record that
the ClientList subform is under? Anyway, I think this might relate to
David's comment to me above, I need to learn how to make use of forms and
maybe subtables correctly to be able to view enter my data.

Thanks again for the help,

Jennifer

 

[Ken Sheridan]

Jennifer:

Yes, you should use a form based on the Clients table (or its equivalent in
your own database) and a subform based on the ClientList table. Link the
parent form and subform on the ID and ClientOf fields as the LinkMasterFields
and LinChildChields properties of the subform control. Add a combo box to
the subform with the Client field as its ControlSource and set its RowSource
property to something like:

SELECT ID, ClientName
FROM Clients
ORDER BY ClientName;

Set the combo box's BoundColumn property to 1, its ColumnCount to 2 and its
ColumnWidths to 0cm;8cm (or rough equivalent in inches, but the first
dimension must be zero to hide the ID column so only the name shows).

To add clients of a client is simply a matter of selecting names from the
combo box in the subform, inserting one row unto the subform for each client
of the current client in the parent form.

It is possible to output the sequence of clients of clients of clients and
so on. This is essentially a simplified example of the classic database
problem of a bill of materials. I did publish an Access solution to this
some years ago for a magazine column a contact of mine writes, but the file
has long since fallen off their web site. If you are interested I could mail
you a copy of you contact me at ken<at>ksheridan<dot>orangehome<dot>co<dot>uk.

Ken Sheridan
Stafford, England

 

[idgity]

Hi Ken,

Ok, I will try to set all that up, thanks.
It is possible that I may need to do a recursive "Clients" search in future
projects, if so I will contact you for the example.

Thanks again,

Jennifer