Query's table cycle (1 Viewer)

Hi!

I ran into a problem related to tables' relations within a query. When I have a "cycle" where relations keep going from table to table and end up within the same table they started from, that will be a problem. Example below:


When I have a query (as above) that will provide me with PersonName, AlbumTitle, ProductionDate and SongName, I get an empty result, but when I select and "delete" the relation between Album and Person table (picture example), I get valid results.

My confusion is why does the second query without the relation work while the first one doesn't? I mean, when I "delete" the relation, it actually remains there in the background, doesn't it? If yes, why is it important to have to remove it from Query Design if it still remains there?

Your relationships / fields in tables are creating your problem.
You should never have a circular relationship path.

Remove the presonID from the album information. As a song belongs in an album, and you are storing the person(s) against that song you can always get the person(s) from an album, through the song relationship.

Minty said:

Your relationships / fields in tables are creating your problem.
You should never have a circular relationship path.

Remove the presonID from the album information. As a song belongs in an album, and you are storing the person(s) against that song you can always get the person(s) from an album, through the song relationship.

Click to expand...

The thing is, one musician (PersonID) will be the main creator / carrier of the album while the others are performers of the songs themselves and what not, that's why I connected the tables that way.

Then add the person table a second time for the second table and delete the automated link to the wrong table if it creates one.

1. Remove PersonID from Albums and therefore the relationship between Persons and Albums. The relationship between persons and Albums is infered using SongID to SongCreation
2. Songs could be published in multiple Albums so remove AlbumID from Songs.
3. Create a New table named AlbumSongs. It is a junction table (similar to SongCreation) and connects Songs to Albums. This table only needs two fields - the two foreign keys. A third FK is needed if you want to record the Performer. In this case, for the simplicity of the diagram, you would add a second instance of Persons and connect to that (as Minty suggested) to clarify the relationship. In the AlbumSongs table, I would name the FK PerformerID and in the SongCreation, I would name the field AuthorID. This embeds the relationship in the name and will reduce confusion when creating queries regarding which Person you are referring to.

Pat Hartman said:

1. Remove PersonID from Albums and therefore the relationship between Persons and Albums. The relationship between persons and Albums is infered using SongID to SongCreation
2. Songs could be published in multiple Albums so remove AlbumID from Songs.
3. Create a New table named AlbumSongs. It is a junction table (similar to SongCreation) and connects Songs to Albums. This table only needs two fields - the two foreign keys. A third FK is needed if you want to record the Performer. In this case, for the simplicity of the diagram, you would add a second instance of Persons and connect to that (as Minty suggested) to clarify the relationship. In the AlbumSongs table, I would name the FK PerformerID and in the SongCreation, I would name the field AuthorID. This embeds the relationship in the name and will reduce confusion when creating queries regarding which Person you are referring to.

Click to expand...

Songs can be a part of 1 album only, not multiple (sure, it's not a real world scenario, but still - that's what I want to achieve so hence AlbumID within the Songs table).

As for the second table instance, when I do that in the Relationships tab of Access, I get this result. Since I have never done it before, I'd like to ask if Access itself recognizes that "Persons" and "Persons_1" are the instances of the exact same table and will it actually affect data input (e.g via forms) in a negative way or do I keep the data input the same way as previously? You woke up curiosity in me.

Realistically you have the following MAIN tables

Song
Album
Performer

You have the following links:

SongAlbum that links a song to a given album

PerformerAlbum that links performers to an album by their role in the album.

PerformerSongAlbum that links a given performer to a given version of a song on a given album. This should have the Role in it (often different that that credited on the album).

PerformerAlbum is the least important in many ways, though their role on an album can often be listed differently that what they are credited for on a given song.

I'm including RI so you can see how these files would normally be related. I've also omitted "Group" information since you did not originally include what band a given album/song is for. This does make it a bit more complex, though not terribly. Remember, there is a difference between Roger Waters playing in Pink Floyd for one version of a song and him covering it on his own. Since compilation albums need to give credit by group that would normally be linked off of the version of the song.

When you add a second instance of the same table to the relationships window (or to a query for that matter), Access suffixes the name to make it unique. This is simply a picture so that the relationships between the table are separate and can be viewed logically as independent. Your circular view in the original picture implied a loop whereas in fact two separate persons are probably involved. It is still possible that the same person performed and created the song but the separate table for the second relationship to person makes it clearer that the possibility of multiple people exists.

Mark, your abbreviations make the diagram difficult to follow.