Trouble setting up a functional many to many relationship

[Jojojo]

Hi! I am setting up a database for a friend's Fitness/Massage Studio. I used the Events Management Template that comes with Access and then modified it (and the VB code) to suit her needs. I had the database almost complete - and then she requested one last report - which the database isn't set up for - and I can't figure it out how to do it. I haven't worked with Access in over 10 years - so I'm basically a newbie.

The main tables where the problem lies are the:
Attendees
Registration
Events



I need to set up a functional many to many relationship as 1 Registration can have many events and 1 event can have many registrations.

Each Attendee (member) can have multiple registrations, with up to 5 classes per registration. The best way I was able to get it to work was to have 5 drop downs when they register a class, so they can choose 1 to 5 classes. This actually worked (but I'm sure there is a better way). But she would now like to be able to run class lists.

The class list would show Event details header(classname, day of the week it runs, start time etc), then the member info, their registration date etc - I would like to be able to set it up that she could select the class from a drop down list, enter the registration date period and it would produce the report. Because I can't get the join table to auto populate - I can't get the report to run.

Any help would be soooo greatly appreciated!! I've attached the database - any input on any of it is welcomed!

[June7]

Multiple similar columns is an indicator of bad design. Multiple columns for classes will cause issues. Report to group and sort by classes will not be easy. Each class registered for should be a record. In your sample data you show a record like:
12/1/2011 1/25/2012 Basic Step Tuesday Prental Fitness Monday 6:15:00 PM
This should be two records like:
12/1/2011 1/25/2012 Basic Step Tuesday
12/1/2011 1/25/2012 Prental Fitness Monday 6:15:00 PM

I don't understand what you mean be registration has many events. Define the registration entity.

Also, appear to have misspelled prenatal as prental.

[Jojojo]

Thanks for the reply. That's the problem I'm having - I know that having the multiple columns for classes is the wrong way but I can't figure out the right way to to do it

I do know that the prenatal class has a typo - once the database works, I will actually go in and add (and correct this) a large # of classes.

What I meant by each registration has many events - is this: There is a Fee Schedule set up: 1 day a week, 2 days a week, up to 5 days a week. The member can have any combination of classes depending on the # of days they want to attend the gym. So member 1 can sign up for 2 days a week for, the system will automatically calculate 8 weeks from the registration date and can choose which 2 classes, and then 8 weeks later could re-register for 1 day a week or 5 days a week with different classes. I envision the Registration form having a multivalue combo box, where it would show a drop down list and you could check off which classes they are registering for - but I am building this in Access 2003 - and that feature isn't there yet.

What would be the right way to do that?

[June7]

I REFUSE to use multi-value fields, one big headache.

Use Find&Replace on the tables to fix the misspelling.

Maybe:

tblMemberRegistration
RegistrationID (primary key)
MemberID (foreign key)
RegistrationType (1day, 2day, 5day)
DateReg
DateExp

tblClassesRegistered
RegistrationID (foreign key)
ClassID (foreign key)

Might call for a form/subform/subsubform arrangement.

[Jojojo]

I actually tried that, and got lost in the subform/subsubform step. Where the combo boxes are, I tried replacing those with a subform - I've been able to figure out how to use the subforms to show records, but not sure how to use them to enter records... I just keep seeing combo boxes on the subform... I can't envision it - so I can't picture how to build it... sorry to sound so dense lol - any chance of a bit of a walk through

[June7]

Access Help has guidelines on building subforms. A subsubform is no different. Make attempt and if you need more help, can post project for analysis.

http://office.microsoft.com/en-us/ac...005187803.aspx

[Jojojo]

Thanks! I think I have it! Would you mind taking a peak (I've attached the revised copy) and just let me know if I have it right? I got the subform set up, and linked to the junction table - the junction table populates - so I think I did it.

The one thing I am unsure about - is set the EventID in the subform as a drop down to a query - I wanted to have the class name, date, and start time show on 1 line. The Junction table stores the EventID, not this string - so I think I'm ok. Do you see a problem with that am I good to go with it?

Thanks so much for your help!

[June7]

The forms of interest are Registration and EventRegistration subform? The EventID combobox is perfect, unless you want more defined separators such as comma or | in addition to the space and might want an ORDER BY on the constructed field in the RowSource query. Congratulations!