MS Access Forum / New Users / November 2006
Can I have a record within a record
|
|
Thread rating:  |
Rachelle - 18 Oct 2006 03:31 GMT 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 - 18 Oct 2006 06:25 GMT 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.
> 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 Duane Hookom - 18 Oct 2006 06:28 GMT 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.
 Signature Duane Hookom MS Access MVP
> You would need to make two tables with a one-to-many relationship. > [quoted text clipped - 18 lines] >> for >> each of them. Is it possible to creat a contact within a contact John Vinson - 18 Oct 2006 06:45 GMT >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 - 18 Oct 2006 18:09 GMT 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 - 27 Nov 2006 00:43 GMT 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 [quoted text clipped - 103 lines] > > 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 - 27 Nov 2006 01:17 GMT 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 [quoted text clipped - 172 lines] >> > clients for >> > each of them. Is it possible to creat a contact within a contact idgity - 27 Nov 2006 02:13 GMT 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. [quoted text clipped - 186 lines] > >> > clients for > >> > each of them. Is it possible to creat a contact within a contact David F Cox - 27 Nov 2006 11:37 GMT 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. > [quoted text clipped - 252 lines] >> >> > clients for >> >> > each of them. Is it possible to creat a contact within a contact BruceM - 27 Nov 2006 14:28 GMT 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, [quoted text clipped - 302 lines] >>> >> > clients for >>> >> > each of them. Is it possible to creat a contact within a contact idgity - 27 Nov 2006 23:01 GMT 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 [quoted text clipped - 282 lines] > >> >> > clients for > >> >> > each of them. Is it possible to creat a contact within a contact Ken Sheridan - 27 Nov 2006 18:39 GMT 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
> 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 [quoted text clipped - 37 lines] > > Jennifer idgity - 27 Nov 2006 23:07 GMT 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
> Jennifer: > [quoted text clipped - 77 lines] > > > > Jennifer Ken Sheridan - 28 Nov 2006 10:15 GMT 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
> Hello, > [quoted text clipped - 14 lines] > > Jennifer idgity - 28 Nov 2006 18:54 GMT 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
> Jennifer: > [quoted text clipped - 45 lines] > > > > Jennifer
|
|
|