Relationship/Query Problem

[bees292]

Sorry for posting yet another thread on many-to-many based queries, but mine aren't working and I need some pointers. I have downloaded and studied Pat's example of the many-to-many DB, and mine now looks fairly similar. Relationships have been properly defined, it's just that my forms dont seem to be behaving how I'd like them to.

Tables:

Papers
------
PaperID (key)
Title
Year
etc......

Authors
-------
AuthorID (key)
AuthorName
etc........

Join (for M-2-M)
---------
AuthorNum
PaperNum

These tables have been linked correctly and show the correct relationships (Papers and Authors are linked with one to many to the Join table)

I have a main form: Papers, data source 'qPapers'. In this I have a subform: Authors, data source 'qAuthors'. These queries are set up in the same way as Pat's, i.e., Authors uses the join fields 'AuthorNum' and 'PaperNum'.

However, when I open Papers form the Authors Subform displays all records and not just those linked to this particular paper. So in this way it doesn't function in the way that Pat's does.

Please help. I've studied Pat's closely and can't work out what I'm doing wrong. Many thanks

 

[KeithIT]

Base your subform on a query that pulls all the records from the join table and only those records from the papers table where the joined fields match (i.e.: join them on PaperNum and PaperID and then pull all from Join and only those fields...).

Your query should pull the paper number and author number where the author number is equal to the author id from the main form (did that make sense?). This will pull all the records in the join field where the author number is equal to your specification and only those fields from the paper field where the paper number matches the author. Okay, I'm not sure that was the best grammer ever, but do you understand what I'm getting at?

 

[Pat Hartman]

Make sure the master/child links for the subform are properly set. Also, my recommendation is to use the same name for primary key/foreign key pairs whenever possible. So I would use AuthorId and PaperID as the field names in the Join table so it is easy to see which table these foreign keys relate to. When you use different names, you (or others) need to refer to the relationship diagram to see the relationships.

 

[bees292]

Thanks to both of you. Pat was right to suggest the Child/Master fields weren't set correctly.