Creating a complex report

[tigergirl]

Hi,

I am making updates and upgrades to a database created by someone else and I think I have hit the outer limits of my abilities. I'm hoping I can explain this clearly enough so you all understand what I'm talking about!

This database has about 15 files, and I think 4-5 of them are involved in this report:

Client information - holds basic client info - name, address, employer

Workshop information - holds basic event info - title, date, location

Workshop registration information - line item that registers a client for a workshop

Session information - holds information about breakout sessions WITHIN a workshop

Session registration information - line item that allows a client to be registered for breakout sessions WITHIN a workshop

Meals registration information - separate line item that allows a client to be registered for meals within a workshop (there is no separate meals file where the meals get set up - this is accomplished through drop downs and data entry and the line items that are created don't relate to a specific meal. Therefore, a meal doesn't actually relate to a specific event, it just relates to whatever date the data enterer puts in...this leads to the definite possibility of errors, but it's how it was created by the original developer and I'd like to leave it as such if I can.)

So with that said, I want to create an event report that shows one line per registration with all of the related information - name, address, employer, sessions, and meals. My problem is that because each session is its own record, I don't know how to get them all onto one line.

I want it to look like a grid where the sessions are listed across the top and the appropriate sessions are checked on each line. A further complication is that some events may have three sessions while others might have 12 - how do I create a grid that automatically accounts for the proper number of sessions, so I don't have 12 columns for an event with only three sessions?

Finally, I need to do the same thing with the meals - list across the top of the grid all of the meals that actually HAVE registrations with checks down below. But the meals don't have any common reference point (ie - meal ID), just the date entered by the data entry person and the meal name and choice of food.

I hope I've provided enough information. I've been stressing over this for days and I finally realized that there's a really smart group of people here who might be able to help me, if I can just make it clear! Thanks in advance!!

Tiger