Problem with a many-to-many table

[Real Wally]

G'day all,

I have a table that tblPublications with fields publicationIDNumber, Title, Author1, Author2, Author3, Author4 etc
Many authors have contributed to multiple publications.

I Also have a tblAuthors. This table has AuthorIDNumber (The same number I use in the author fields of the table above) and various other author details (address, phone, email, DOB, etc).

I'd like to be able to link the two be able to link both tables in such a way that:
1. I can see which authors are on a certain publication and
2. I can see what publication an author is on

I'd like to have it it in such a way that I can do a form on which not the author ID numbers but their names show up.

I suppose this is a many-to-many relation and I just cannot work out how to solve this ridle. Who can give me a hand?

Thanks a million,

Walter

 

[FoFa]

Honestly, I would break the authors out of that first table, setup a PubAuthors_XREF table and tie any authors from the authors table to publications by this cross ref. table. That way you are not limited to how many authors can be on a publication, and you can use std sql to get the information you need. basically it would have an ID field (autonumber, just because), PubID (ties to publication), AuthorID (ties to author table).

PUBID
1 Winter is Cold
2 Taking Bribes for Advancment
3 USA to English dictinary

AUTHID
34 Eskimo Pie
45 Joe (whos your daddy) Smith
47 Frozen Death

PUBAUTH_XREF
1 34
1 47
2 34
2 45
3 45

Eskimo Pie and Frozen Death worked on Winter is Cold
Joe Smith and Eskimo Pie worked on Taking Bribes
etc.

 

[Real Wally]

Thanks FoFa,

It's an option that I've also considered but it means filing out an extra table by hand and with more than hundred publications and even more authors there's a lot of room for error.
I was hoping that there's another solution for that, maybe some more complicated key actions or so. Maybe that's just not possible.
I am a complete newby with Access and see many clever solutions on this forum and was hoping that experienced users had seen a similar situation before and have come up with a clever solution.

Thanks again, I may have to go your route in the end if this turns out the way to go.

Walter

 

[Real Wally]

Thanks Pat and Fofa for the advice.
Tables have been created accordingly. It will not surprise you two that it works just fine. I haven't figured out the append query part yet , no time yet, but will try to over this weekend.

Thanks again. No doubt I'll be back for more on this great forum.