Table/relationship question 2

[ZOR]

I have a one to many child table related to its parent. However in the child table there is a field for a persons name entry. However the users have been entering multiple names. I am therfore considering putting in another related table to the existing child table. Having found a way to split the names up in the existing field I have found many duplications amounting to 9k+ names. With a distinct query there are 3k+ names. I have exported 9k+ names into another table but how can I introduce another permanent table within the existing two tables thatwill have I think have a many to many relationship.

Table 1
ID1 Primary Key

Table 2
ID1 Foreign Key
ID2 Primary Key.

Many thanks

 

[fneily]

You seem to be trying to fix bad table design. Even if you fix it, it won't stop the users from entering multiple names.
So the users are entering IN ONE TEXTBOX names like this:
Joe, Barb, Steve instead of just one name? Or are they entering both first and lastnames? This doesn't conform to the first normal form.
Maybe if you provide some sample data, it would be easier to see what you want.
However, redesigning the tables following the protocols of normalization will solve your problem.

 

[ZOR]

Thanks. Yes it is a fix, and the textbox will get modified to prevent future inputs. The names are all corrected in a temprary table. My concern was If I added a third table in a one to many relationship it would allow me to modify the form for users to add further names per record, however a great deal of duplicated names would get put in the table. The existing child table contains data on sports events, the names in each record are sportspeople names. The same names can get entered in several records, and I thought this to be wrong practice. I am therefore trying to find how I can have a table in which the names appear once, but maintain some relationship to the records they are applicable to. Hope that makes sense. Thanks

 

[fneily]

If I understand, you want to match a sportevent to the sportpeople participating in it? So a one-to-many relationship. Now, are you sure a sportsperson can't participate in more then one sportingevent? Such as Dion Sanders? Football/Baseball. Then you'd have a many-to-many relationship.
You may try this. You would have a table with the sportingevents info, so some sporteventID primary key. You'd then have a table with sportspeoples name and info, so some sportpersonID. You could then have a third table that has a primary key, say EventPersonID, and two other fields which are the primary keys of the two tables, sporteventID and sportpersonID. You can then connect the two "main" tables through this one. So for a particular event, you can find all the people. Or find all the events for a single person. Etc.
So a person's name would only appear once in its' table.
Also, this way to populate the "junction" table, you would have a form which would generate the EventPersonID and two comboboxes to select the sportingevent and sportperson.

 

[ZOR]

Many thanks, I think I am with you, just a quick check with you.

Exisitng Child Table 2
ID1 - Foreign Key to Parent table
ID2 - Primary Key
Other data, including Name field

New Table 3
SportsPersonID - Primary Key
Sportspeople Names

Junction Table 4
ID2 - Foreign Key to existing child table 2
SportsPersonID - Foreign Key to New Table 3

Does that look correct. The next major question would be how do I get the 9k+ records data into the New Table 3

Thanks again

 

[fneily]

New Table 3 wouldn't contain the 9K+ names. It would contain the 3K+ DISTINCT names. You stated that you already have a query that contains the 3K names. Try changing it to a "make table" query, then add the primary key.
Also, your junction table should have its' own primary key.

 

[MajP]

I may be making this harder than it needs to be, but this is how I would do it.

1. you need to make a table of the 9,000 names with ID2 included. You need this information to populate your junction table. I will call this tbl3A. You may have done this already. My field and table names may be different than yours. But as you see Jones and smith are related to more than one ID2.


tbl3A
autoPersonID strName ID2
1 Jones 1
2 Jones 2
3 Smith 1
4 Smith 3
5 Brown 1
6 Black 5

2. Next make a query that returns only the top one of each name. In your case 3k of names.

SELECT A.autoPersonID, A.strname, A.ID2
FROM tbl3A AS A
WHERE (((A.autoPersonID) In (Select top 1 B.autoPersonID from tbl3A as B where B.strname = A.strname ORDER BY B.autoPersonID)));

qryTop3A
autoPersonID strname ID2
1 Jones 1
3 Smith 1
5 Brown 1
6 Black 5

eventually this will become your table of names once you populate your junction table.

3. Now read through all of tbl3A and populate your junction table, tbl4. The trick is when you find a name that is used more than once you only populate tbl4 with the top value. When you find a Jones, you will always populate with autoPersonID 1, and for Smith 3 the top value from qryT.

Public Sub makeNew()
Dim rsTbl3A As DAO.Recordset
Dim rsTbl4 As DAO.Recordset
Dim rsQryTop3A As DAO.Recordset
Set rsTbl3A = CurrentDb.OpenRecordset("tbl3A", dbOpenDynaset)
Set rsTbl4 = CurrentDb.OpenRecordset("tbl4", dbOpenDynaset)
Set rsQryTop3A = CurrentDb.OpenRecordset("qryTop3A", dbOpenDynaset)
Do While Not rsTbl3A.EOF
rsTbl4.AddNew
rsTbl4.Fields("ID2") = rsTbl3A.Fields("ID2")
rsQryTop3A.FindFirst ("strName = '" & rsTbl3A.Fields("strName") & "'")
rsTbl4.Fields("intPersonID") = rsQryTop3A.Fields("autoPersonID")
rsTbl3A.MoveNext
rsTbl4.Update
Loop
End Sub

Your junction table becomes

tbl4

ID2 intPersonID
1 1
1 3
1 5
2 1
3 3
5 6

4. Now make your table of names using a make table query from your qryTop3A. This will now be a table with only the top name and an ID that matches the ID in the junction table.

 

[MajP]

actually now that I think of it, for step 2 any select distinct will work. I was originally thinking of a different approach. All you need is a list like

qryTop3A
autoPersonID    strname    
 1               Jones    
 2               Smith    
 3               Brown    
 4               Black

 

[ZOR]

Many thanks for the help Majp, sorry for delay getting back. Ref 3 Do I use this query as a make table query to producee tbl4, as if I run the code makenew, as if run it reports not finding tbl4. Thanks

 

[MajP]

Tbl4 is your junction table. Just build a new junction table with two fields. I called this new table tbl4.

tbl4
intPersonID
intID2

Then run the code to populate this table "tbl4".


Once you are done, tbl3A can go away. The select distinct query that returns the 3k names should be used to make a table. This table needs an auto ID field.

To summarize
1. Make a table with 9k names and associated ID2 foriegn key "tbl3A".
2. Make a new table using a select distinct and return 3k names. I called this query "qryTop3A". But lets call it just "tblPersonnel". Ensure to add an auto number field to this table. Make sure the code reflects whatever you call the table.
3. Make your junction table, "tbl4"
4. Run the code. It reads every one of the 9k records in "tbl3A", but uses the single ID for each name from the 3k "tblPersonnel" table
5. Delete "tbl3A".
6. Now you can link "tbl2" to "tbl4" to "tblPersonnel" in a one to many by the person ID and the ID2

 

[ZOR]

Sorry again in the slowness getting back. When I run the code I get an error, Syntax error (missing operator) in expression.

The line it falls on is:

rsQryTop3A.FindFirst ("strName = '" & rsTbl3A.Fields("strName") & "'")

Running my mouse over the second strName it highlights a name O'Grady, so I think it's falling over the apostrophe. Just guessing?

Any ideas, thanks

 

[lespaul]

yep, those apostrophe's are the bane of many a programmer....maybe use the REPLACE function to get rid of them?

Leslie

 

[ZOR]

Thanks Leslie. Managed to nip over to the Access Queries forum and got fast replies which fixed it.

Both of these worked fine:

rsQryTop3A.FindFirst ("strName = '" & rsTbl3A.Fields("strName") & "'")


rsQryTop3A.FindFirst ("strName = """ & rsTbl3A.Fields("strName") & """")

I can carry on now with the rest of the work. Regards

 

[MajP]

Untested, but as lespaul suggests:
("strName = '" & replace(rsTbl3A.Fields("strName"),"'","''") & "'")

 

[ZOR]

Thanks Majp, that enabled the code to make tblPersonnel.

So I am nearly there to connect relationships up

Current Table 2
ID2 Primary key

tblPersonnel (3k records)
AutoPersonID - Primary Key
ID2 - Number
strName - Text

tbl4 (Junction)
intPersonID - Number
ID2 - Number

Before I go live, just to check

Table 2 - ID2 Primary - tbl4 ID2
tblPersonnel AutoPersonID - tbl4 intPersonID

Does that look correct? I will have to straighten my thoughts as to where new names go into afterwards, presumably tblPersonnel.

Many thanks for staying out there. Regards

 

[MajP]

If I have been following correctly.
1. You now have tblPersonnel with 3k unique names. Each name has a unique ID. Any other information that is unique to a person should go in here. You no longer need the ID2 field in this table.
2. Tbl2 has a primary key of ID2 and contains information about ID2 things. (whatever that is). I think it was a sport event or team. But ID2 uniquely defines that event or team.
3. Tbl4 is the junction table that links ID2 things with people. Many people are associated to a specific ID2 and, an individual person can be associated to many ID2. Thus a many to many relationship. There are many people on a sports team, but each person can play many sports.

So in my mind everything looks correct. Tbl2 links to tbl4 by ID2, and tbl4 links to the tblPersonnel by the person ID.

Now get rid of ID2 in the tblPersonnel. You add new names to tblPersonnel and you can add additional fields to tblPersonnel to uniquely describe them. DOB, Ht, Wght, Age, First Name, Last Name, etc.

Things to consider:
1. Now to add a person to a team (or whatever ID2 is) you are adding a key to tbl4. You now have to choose from a list of names. If the name is not in the list, then you want to add the name to the list. Take a look at the notInList event of a combobox. Go to the help file to get some strategies to make a nice interface.
2. The part that confuses people with a many-to-many is how to make a subform to enter values in the junction table. In your case tbl2 information goes on the main form, and the subform is formed by linking the main to tbl4 by ID2. Then the person ID is a combobox that shows the persons name, but is bound to the hidden first column the person ID.

 

[MajP]

Just to check you said:

that enabled the code to make tblPersonnel

Hopefully, the code populated tbl4. tblPersonnel should just be a select distinct query from the original 9k names.

 

[ZOR]

Many Many thanks Majp for your excellent help on this. I could never have understood the process of a junction table without all your effort.

Yes, sorry, the code produced tbl4, and all tables have the correct data in them. Thanks for explaining ID2 removal, I could not understand where it fitted in after all the changes. I think I can now see the light at the end of the tunnel where to go to now. Just wish I could put a page of stars on the forum. Hope many more get the benefit from it. All the best.