Need advice on best way to build my DB...

[lacey]

Hi all,
I need help. I'm going crazy trying to figure out the best way to accomplish what I need...

I work at a community college and am currently working on building a DB for our summer program enrollments. The classes we offer are broken down into three sessions, so I have a table for session 1, a table for session 2, and a table for session 3, all of which have individual Yes/No fields for each class in that particular session. I also have a table for registrant/office info. I have established relationships between each table as the ID field being the primary key.

The process of building the form is where I'm getting lost. I've tried simply placing all fields in the form (separated by different tabs), but I'm guessing I did something wrong or am unaware of something I need to do when building a form with fields from separately related tables...

I've also tried creating a form with 3 separate sub-forms (one for each session), but when I enter all the information it doesn't match up or enter into the tables correctly.

Well, as confusing as that all may be, at least get the general idea that I have no idea what I'm doing and simply need advice on the best way to set this all up so everything meshes correctly.

Thanks!

 

[Rabbie]

I suspect your relationships are not set up correctly which may be causing your problems. Can you let us know what relationships you have set btween your tables.

 

[lacey]

I've set up a one-to-many relationship between the Registration/Office info table with each of the other tables... is that what you are asking?

 

[Rabbie]

Since a Session can have many Classes and a Class can be in many sessions you probably need to use junction tables to model the Many-to-Many relationships.

Without knowing exactly what you are trying to achieve and how you are trying to do this it is difficult to give you detailed guidance.

It would be helpful if you could post your DB here so we can see what you actually have. Please indicate the version of Access you are using

 

[lacey]

Okay... not sure how to set-up junction tables with many-to-many relationships. Here is my DB, I am using Access 2007.

View attachment 37200

 

[Rabbie]

See this link for more info on junction tables. Also use Google if you need more info

 

[lacey]

Thank-you... that helps a lot. Now that I'm learning about how the junction tables work, what would be the best way to set that up in a form? Should I use subforms?

 

[lacey]

Here is my relationships report, can you look at it and tell me if it appears to be set-up correctly?
View attachment 37201

Thanks bunches!