Creating Many to Many relationship in Access 2002 1

[inheritance]

I have four tables User, Member, Artist, Genre all with primary keys like so UserID, MemberID, ArtistID, GenreID and fields which are relevant to the table

UserID MemberID ArtistID GenreID
UserName FirstName Name Genre
Password LastName Link
Address Image
etc Bio

I want to be able to define a many to many relatioship between the artist and genre and so far I have tried defining a junction table called Artist_Genre with both ArtistID and GenreID FK's. If I think I know what I am talking about, this is supposed to create a many to many relationship from a pair of one to many relationships from the Artist and Genre tables ? how does the other two tables connect to this new entity
I have played about with these relationships like it was DNA
I just can't get it all together
Does anyone know what I am trying to do ?
Cheers

 

[RodgerC]

Basicly you can only have M-M, 1-M, 1-1 relationships. That is it. You are right in creating a junction table called Artist_Genre, that's the way its done in Access.

So, how does the other two tables connect to this new entity? However you want. You could put a 3rd table's ID into Artist_Genre & use all 3 IDs as Primary Key for this new table, or you can create relationships with the other tables via 1-M or 1-1 in the traditional way. ie. Artist - Member could be M-1, User - Genre could be 1-1. You just have to play with the 'DNA', like you said, to get the mix that will help you get the results you want.

Maybe learning some SQL to help you extract the data you you want may help.

 

[inheritance]

I am currently desiging a site with a directory which I am building in access. I am using ASP/ODBC to connect to it and define filtered recordsets for live binding and displaying my data
SELECT ArtistID, ArtistName, Genre
FROM Artist, Genre
WHERE ArtistName LIKE 'varArtistName' OR Genre LIKE 'varGenre'

Request runtime variables are linked to the URL search parameters..(It's not rocket science)
So why do I have spurious results from my search
If you think its the SQL statements then let me know
But I seem to have a gut feeling that its the relationships between the tables
It eludes me
Thanks

 

[scking]

Create a joined query using the query builder

Artist_Genre with both ArtistID and GenreID

Artist Artist_Genre Genre
PK ArtistID PK ArtistID+GenreID PK GenreID

Join the ArtistID in the Artist table to the ArtistID in the Artist_Genre table and connect the GenreID from the Genre table to the GenreID in the Artist_Genre table. THIS CREATES A VIEW that you can reference directly from SQL and extract anything in all of the joined tables. OR you can look in the SQL view for the query and paste the SQL into code.

Steve King Growth follows a healthy professional curiosity

 

[inheritance]

Thanks
Thats exactly what I have done
So once I have Artist & Genre tables linked
What about the member and user tables, how do they connect up?
I have tried every possible way to link them so that when I create a form to test it, I put every record onto it from all the tables so I can fill in the relevant data but I can never access certain table elements which leaves me to believe I can't connect them like I think I want them to be connected
I'm thinking
User logs in ...so then they can update their details ie:Member, Artist and Genre
Trying to visualise this through relatioships is confusing the heck out of me
Should I be able to enter data into every single record in the database from the one form?
You have reassured me that what I have done is correct with the M-M between the Artist & Genre, thanks but still not clicking about something for the Member and User tables.

 

[inheritance]

Oh!
Sorry if I posted in the wrong section ppl