Junction table okay for a one-to-many relationship?

[wardw]

I’m building a database where volunteers can type in data on obituaries from newspapers. I have a main form, where name(s), date of death, burial place, etc. are entered for each person. Each deceased person makes one record in my main table, AllDeathRecords.




But some deceased had multiple obituaries in different newspapers. So deceased and obituaries have a one-to-many relationship: A deceased can have many obituaries, but an obituary can have only one deceased.


I want to avoid duplication of data, so I’ve built a junction table called MultipleObituaries, with two foreign keys: PersonID from the main table, and PublicationID from a Publications table. It will also have fields for newspaper name, date, page, and column.


I’ve also built a subform (Multiple_Obituary_Records) where for each person the volunteer can fill in the obituary’s newspaper name, date, page, and column, for as many obituaries as the person had.


A question: I’ve read that junction tables are used for many-to-many relationships; will mine work for my one-to-many relationship?

[Missinglinq]

...I’ve read that junction tables are used for many-to-many relationships; will mine work for my one-to-many relationship...

Why in the world would you want it to? Using a Junction Table, in this situation, is senseless! Two Tables, linked by a common Field, and used in a Main Form/Subform scenario, is all that is needed.

And...

...I want to avoid duplication of data...

instead of helping you to avoid duplication of data, using a Junction Table will actually facilitate the entering of duplicate data!

Preventing the entering of duplicate data, such as entering a second Main Form Record for a given deceased person, is a common task, usually done using the DLookUp Function. If you search here, or in the 'net, using the terms

"MS Access" and "prevent duplicate data" I suspect you'll get about a gazillion hits with examples! Find and try one out, and if you run into problems post back here for help.

Linq ;0)>

[June7]

Each person can have obituaries in multiple papers. Each paper will have multiple obituaries (each for different person). This is many-to-many relationship. The 'junction' table is the one that relates persons with papers.

tblAllDeathRecords

tblNewspapers

tblObituaries
DeathRecID (foreign key)
NewspaperID (foreign key)
PubDate

Use form/subform arrangement for data entry:

1. main form bound to tblAllDeathRecords and subform bound to tblObituaries with a combobox to select newspaper

or

2. main form bound to tblNewspapers and subform bound to tblObituaries with a combobox to select death record

I expect option 1 will be preferable arrangement. In either case, if desired item not in the combobox, the NotInList event of combobox will be useful.

[Missinglinq]

Each person can have obituaries in multiple papers. Each paper will have multiple obituaries (each for different person). This is many-to-many relationship.

I hadn't considered that. But this would only be pertinent, would it not, if you were interested in sorting/filtering by newspapers? But this doesn't appear to be of interest to the OP, here; his only stated interest is to be able to have a single listing by person, with listings of multiple obits but no duplication of person or duplication of a particular obit. In fact, he states that his reason for having a Junction Table is "to avoid duplication of data"

...I want to avoid duplication of data, so I’ve built a junction table called MultipleObituaries...

which in and of itself makes no sense to me.

Linq ;0)>

[June7]

Right, the MultipleObituaries table should be same as my suggested tblObituaries. I just reread user post and their structure makes perfect sense to me and is actually in line with mine:

"I want to avoid duplication of data, so I’ve built a junction table called MultipleObituaries, with two foreign keys: PersonID from the main table, and PublicationID from a Publications table. It will also have fields for newspaper name, date, page, and column."

Only exception I have with that is the field for newspaper name is not necessary because the PublicationID is saved.

[wardw]

Thanks, Missinglinq and June7, for you analyses. It looks as if my subform will work okay, but I have another problem: It's blank in Form view. I'll start a new thread with that problem.