Many to many relationship 1

[Darrylles]

Hiya,

Anyone out there that can tell me how to paint many-to-many relationships in an Access application form?

I've defined a link table so that there is a many-one-many relationship (as you do to get round the many-many).

What key fields do I paint? I'm assuming that I'll need sub-reports to do this.

This is a summary - I'll post more detail on request, but the many-many problem must have a standard solution.

Many thanks in advance. "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[RickSpr]

I'm not sure what you mean by "painting" the relationships and key fields. It's also unclear whether you're trying to create a form or report. Is this some sort of replacement for the Print Relationships utility? Rick Sprague

 

[Darrylles]

Sorry, I meant that I would need sub-forms on a form.

When I say 'paint', I mean what fields will I need on the form?

E.G. There is a TEAM table and a PLAYER table, there are many players in the TEAM table (e.g. Defender1_fk, Defender2_fk, Midfielder1_fk, Midfielder2_fk (foreign keys) and also each PLAYER can belong to many TEAMs.
I therefore have a PLAYER_TEAMS table that has a PLAYER_FK and TEAM_FK.
This gets rid of the Defender1_fk, Defender2_fk out of the TEAM table and the primary key of the TEAM table is used in the PLAYER_TEAMS table etc.

I can't get Access to accept this kind of relationship or at least when I produce the form, it refuses to bring back the records.

Thanks for your response Rick.


"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[RickSpr]

It all depends on how you want the data organized. There are three basic ways you could organize the form.

1. You can show the data without a filter, giving a comprehensive roster of all teams and players. The user can use the Sort commands to order it by team or by player, and the Filter commands to narrow the scope to what he's interested in. For this, you need all three tables, with simple joins from PLAYER_TEAMS to the other two.

2. You can filter for a team using a list box or combo box. The main form would then show team details, and the subform would show the roster for that team. The main form would be based on a join between TEAM and PLAYER_TEAMS, and the subform based on PLAYER. The linking field would be the PLAYER_FK. The list box or combo box would be based on TEAM.

3. You can filter for player instead. The main form would show player details, and the subform would show the teams he plays on. The main form would be based on a join between PLAYER and PLAYER_TEAMS, and the subform based on TEAM. The linking field would be TEAM_FK. The list box or combo box would be based on PLAYER. Rick Sprague

 

[Darrylles]

Thanks Rick, you took some thought & time in your response.

Have you got an (unimportant) email address where I could mail a bitmap of my relationships?
Don't post your regular address! Don't want you inundated with queries!

I'm used to a tool called Uniface where forms are really based on tables and their sub-tables etc.
I'm quite au-fait with relational databases, but Access doesn't seem to like many-many relationships at all.

I've worked with Access for approx. 6 months.

2 bitmaps would explain it to you in seconds.

If you wouldn't like to do this, then no prob, but it's gonna take a lot of typing (from both of us) to get it clear.

Kind regards and thnx for your interest,

Darrylle
(You could email me at darrylles@hotmail.co.uk - gosh darn useless email address that it is - lol)

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[mikey69]

I see you have no responses. Your problem is not easy to understand. What do you mean by paint. If you post a bit more detail, I'll try to help

 

[Darrylles]

Hiya,

I've just realised that this site actually vets and edits your posts! It also intercepts and deletes posts! Quite scary. I'm a bit wary about using it now.

Anyway, I have a TEAM table, the TEAM table has 11 soccer players. I WANT it to have maybe 6 netball players in the future, therefore I don't want to have to tie myself to 11 players now and therefore created a PLAYERS table with a relationship.
If I add a team game with 6 players, I don't want to lose my soccer teams with 11 players, so this has to be a generic design.

I decided on the following....

(PK = Primary key, FK = Foreign key)

TEAM TEAM_PLAYER PLAYER
pk team_fk pk
name player_fk name

TEAM_PLAYER.team_fk is the TEAM.pk for the team name.
TEAM_PLAYER.player_fk is the PLAYER.pk for the player name.

This means that I can have a team with one player if I wish, or a team with a million players if I wish.
I'm not tied to a particular game, with a set number of players.

How do I show the relationship in Access?

When I've done that, how do I draw (or paint) the fields on a form so that team_pk will pull back the players?

Thnx for your interest,

Darrylle


"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[RickSpr]

Darrylle, I sent you an email at the address you gave, but I never received your bitmaps.

If you're asking how to create the relationships among these three tables, what you have is a one-to-many from TEAMS to PLAYER_TEAMS, and a one-to-many from PLAYERS to PLAYER_TEAMS.

For how to design the forms, I refer you back to my previous answer. Rick Sprague

 

[Darrylles]

Sorry Rick,

Hotmail has started it's 30 day with no access and you're out policy.

Please use darrylles@yahoo.co.uk
No prob. with this - it'll be easier via mail.

Thnx for response again


Kind regards,

Darrylle
"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[mikey69]

Darrylle - It looks as if you will get sorted out by Rick, so I'll keep out. Am surprised about your comments regarding intercepting posts. Have not seen it myself. If you have good evidence, I suggest you submit it ot Tek-tips for comment.

 

[RickSpr]

For the benefit of others, let me explain that the bitmap showed:
1. A Dream_Team table with key "pk" and field "Team_Name"
2. A Player table with key "pk" and field "Player_Name"
3. A DT_Player table with foreign key "DT_fk" referring to the "pk" field of Dream_Team, and foreign key "Player_fk" referring to the "pk" field of Player_Name. The combination of DT_fk and Player_fk are the key of DT_Player.
4. The relationship from Dream_Team to DT_Player is one-to-many.
5. The relationship from Player_Name to DT_Player is also one-to-many.

Thus, DT_Player is an associative table which resolves and implements the many-to-many relationship between Dream_Team and Player_Name.

Darrylle added the following comments to the diagram:
------------------
"On the form where I want to add PLAYERs to a DREAM_TEAM, I draw (or paint) 11 combo's - 1 for each player.
I can't use a continuos form because I want the player combo's to be in formation as on a football field.
I pull ALL fields from all tables into the orm.

When I choose the first player from a combo, it displays ok, but when a choose a 2nd or 3rd or 4th etc it changes ALL combo's to that value."
------------------

Darrylle, the table structure is exactly what I had gathered from your description. The problem is that you want to have each combo box bound to a different record from the DT_Player table. Forms just don't do that--a form shows only one record, unless it's in datasheet view (in which case you can't control the positions of the controls) or continuous form view (in which case you have multiple copies of the whole form, not just of one control).

All is not lost, though. The combos are all getting set to the same player name because they are bound to the same field. If you make them unbound, they'll be independent of one another. Of course, that means you'll have to update the DT_Player table in code, but that's an unavoidable consequence of what you're trying to do (display data from multiple records on one copy of the form).

However, this is going to be somewhat complicated. For one thing, you have no way (in the tables as given) to indicate which player goes in which position. I should think your DT_Player table needs to have a PositionPlayed field, as a start. That would indicate which combo box a particular DT_Player row should be displayed in. Also, since each position can only be filled once for each team, you should probably make DT_Player's key be the combination of Team_fk and PositionPlayed. (Note that this means DT_Player will no longer be a standard many-to-many associative table, but it will continue to support that relationship.)

When a team is selected, you'll have to use code to look up each position for that team from DT_Player joined to Player, and insert the player's name into the corresponding combo box. Keep in mind that you may not find any row for one or more of the positions. It depends on whether it's a new team being composed, and whether you let them save an incompletely built team.

While the user is selecting players for each position from the combos, you'll have to check for duplicates (can't have one player playing more than one position). How you handle a duplicate is up to you; you might erase the previously existing combo, or give them a MsgBox.

When they're done selecting, they'll have to click a button to save changes (or you can do this automatically when they select a different team or close the form). You'll then use code to add or update records in the DT_Player table, using the Player.pk saved in the combo, the Team.pk from the team selection, and the Position associated with each combo. If you allow them to delete an existing player, you may also have to be prepared to delete the corresponding row from DT_Player.

This all takes a good bit of code, I'm afraid. If you're not up to speed on VBA programming, and DAO for accessing records from code, you've got quite a learning curve ahead of you. You may want to check into a third-party book to learn VBA programming. Unfortunately, I don't think there's any other way you can arrange data from all these separate rows on one form, except by using code. Rick Sprague

 

[Darrylles]

Thanks Rick,

I have a pretty good knowledge of VBA; DAO I'll have to get stuck into.
I have thought about much of the logic that you mentioned above and I thought that there must be a simpler way to do this as opposed to controlling everything via code, but alas....

Thanks a lot for all of your time.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."