Relationship Structure

[Ramtrap]

Hi all,



I've semi-recreated a database I'm trying to make, a Conference Scheduler. I'm pasting an image, below, of the relationships I have established. Here's what I'm trying to do:

I have a form that opens a report to show the current agenda for the overall main conference. Each Day of the conference has a button whereby a user may add an agenda item. (Example: "BIG CONFERENCE" has Conference Days 1 and 2, each with their own agenda:

Day 1: 8am - 10am - Agenda Topic 1
Day 1: 10am - 2pm - Agenda Topic 2
Day 2: 10am - 11:30am - Agenda Topic 1
Day 2: 11:30am - 1pm - Agenda Topic 2
Day 2: 1pm - 4pm - Agenda Topic 3

What I am trying to do is add an agenda item, but it's not linking up. It will pop up the proper form, but I get the error, "You cannot add or change a record because a related record is required in table 'tbl_Conference_Day'

Is it apparent to anyone what is amiss? My guess is it has something to do with the ConferenceID Primary key being related from the Main to both the Day and Agenda tables, but I can't figure out if that's correct. Is there anything glaringly obvious why I'm getting the error?

Thank you!

[June7]

Don't include ConferenceID in tbl_Person_to_Conference or tbl_Agenda. The ConferenceID can be retrieved by links to tblConferenceDayID.

Can an agenda have multiple days? Will a day associate with multiple agendas? If yes to both then this is a many-to-many relationship and a 3rd (junction) table is needed.

[orange]

Further to June's comments/questions, can you lead us through the "business" of your conferences and agendas in simple English?
For example, suppose John wants to attend the Conference you are having in Centerville, Ohio----- can you lead us through the who, what, where, when, how much and how often sort of scenario. It will help us understand your proposed database tables and relationships and it will help you when you have to tell someone else what you are trying to support with this database.

Good luck.

[Ramtrap]

Further to June's comments/questions, can you lead us through the "business" of your conferences and agendas in simple English?
For example, suppose John wants to attend the Conference you are having in Centerville, Ohio----- can you lead us through the who, what, where, when, how much and how often sort of scenario. It will help us understand your proposed database tables and relationships and it will help you when you have to tell someone else what you are trying to support with this database.

Good luck.

Sorry I haven't been able to respond. Work computer went down. Still a little wonky now so hopefully this doesn't crash on me, again.
I think the thing I need most help with is my tables and relationships, just to know if I'm on the right track.
To put into context the images below, I have one table (Conference_Main) that houses the main conference information (ex: ABC Conference).
I have another table (Conference_Day) that houses the content of each individual day (and is related by the Conference_ID key); ex: Day 1: Subject X, Day 2: Subject Y, Day 3: Subject Z.
Lastly I have an agenda table that houses the unique Agenda items; it must be related to the Conference_Main_ID and the Conference_Day_ID; ex:
ABC Conference (Conference_ID key)
Day 1 - Subject X (Conference_Day_ID key)
Agenda Item 1; 8am to 9am (Agenda_ID key)
Agenda Item 2; 9am to 10 am (Agenda_ID key)
Agenda Item 3; 10am to 12pm (Agenda_ID key)
...etc.
Day 2 - Subject Y (Conference_Day_ID key)
Agenda Item 1; 8am to 10am (Agenda_ID key)
Agenda Item 2; 10am to 11 am (Agenda_ID key)
Agenda Item 3; 11am to 1pm (Agenda_ID key)
...etc.
Day 3 - Subject Z (Conference_Day_ID key)
Agenda Item 1; 8am to 9:30am (Agenda_ID key)
Agenda Item 2; 9:30am to 12:30pm (Agenda_ID key)
Agenda Item 3; 12:30am to 2pm (Agenda_ID key)
...etc.

<I apologize I can't post the images; the site keeps crashing whenever I try>

Hopefully this will explain it well enough. If not, I'll find a way to post the relationships I have. Here's an explanation, though:

Version A (this feels wrong to me):
Person TO Person_to_Conference (One to Many)
Person_to_Conference TO Conference_Main (Many to One)
Conference_Main TO Conference_Day (One to Many)
Conference_Day TO Agenda (One to Many)

Version B (Is this ...more right?):
Person TO Person_to_Conference (One to Many)
Person_to_Conference TO Conference_Main (Many to One)
Person_to_Conference TO Conference_Day (Many to One)
Conference_Main TO Conference_Day (One to Many)
Conference_Main TO Agenda (One to Many)
Conference_Day TO Agenda (One to Many)

Any help supremely appreciated. Thank you!

[Ramtrap]

Further to June's comments/questions, can you lead us through the "business" of your conferences and agendas in simple English?
For example, suppose John wants to attend the Conference you are having in Centerville, Ohio----- can you lead us through the who, what, where, when, how much and how often sort of scenario. It will help us understand your proposed database tables and relationships and it will help you when you have to tell someone else what you are trying to support with this database.

Good luck.

To better answer your question re: John, let's use three people: John, Eric, Kelly

All are going to ABC Conference (re: Conference_Main) but will have different agendas based on the different days they're attending

Example:
John goes to Days 1 and 2
Eric goes to Days 2 and 3
Kelly goes to Day 3 only

My assumption is they would all need that Conference_ID (for ABC Conference), a Conference_Day_ID (for the individual day they're going). The agenda is a table for taking the individual agenda items (Agenda_ID), but a report to display the current agenda for the day they're going to.

i.e. I can have an overall agenda that will indicate each day and the full agenda items (e.g., Days 1, 2, and 3), as well as a separate report that can display the agenda for just the specific day in question (e.g., all of Day 1).

Does that help, @orange?

[June7]

ConferenceDayID is a unique value in tbl_Conference_Day. Save it as foreign key in related tables. Can always retrieve the ConferenceID from tbl_Conference_Day in queries that link on the PK and FK fields.