Totals query or something else?

[hockey8837]

Hi,
I have a database for my nonprofit zoo. In it, we track program attendance for free programs, events, and programs requiring registration. I have a Table (TblPrograms) where we'll collect things like program name, date, times, fees (if any) and attendance. Attendance can be recorded right in the "TblPrograms" in the [TotalGuest] field for the majority of our programs (things like free with admission special events, keeper chats, animal encounters, etc.). We just record the total bodies that come by.

But for SOME programs, we require registration (and they're usually fee-based). Something like a breakfast program; for this, the "TblPrograms" [ProgramID] is joined with a [ContactID] from the "TblContacts" into the table "MMContactsPrograms" (where they get a new [ID]). But we also have a [#Adults], [#Kids], and [#NonScouts] fields that would have participant #s in them. This allows us to keep track of registered participants, calculate fees based on adult/child prices, print out rosters, etc.

Where I'm running into problems is when I want to run a report that will tell me a count of ALL guests for the last week or month who attended a program. When I run a query to pull #s from both the "TblPrograms" and the "MMContactsPrograms", I'm getting the fee based events with individual registrants pulling multiple times in the query.

To better explain, if I pull program #s from last week, it will show all of our programs:
Date Program Name # Guests
6/20/17 AM Keeper Chat 27
6/20/17 PM Keeper Chat 19
6/21/17 Roving 31
6/24/17 Breakfast 3 (combo of [#Adults] + [#Kids] + [#NonScouts])
6/24/17 Breakfast 2 (combo of [#Adults] + [#Kids] + [#NonScouts])
6/24/17 Breakfast 1 (combo of [#Adults] + [#Kids] + [#NonScouts])
6/24/17 Breakfast 5 (combo of [#Adults] + [#Kids] + [#NonScouts])
(where all the Breakfast programs should instead be combined together in a line that is just "6/24/17 Breakfast 11")

I've tried to do a totals query for the "MMContactsPrograms" table to give me a sum of each program's participants prior to querying with the "TblPrograms", but it's not working- maybe because I have 3 fields that could have possible participant that are being combined in an Iff(IsNull) line. I've tried combining the 3 fields in a separate query and then running a total query off it as well as trying it in the same query. Ideally, it would sum based on [ProgramID] so that each program is summed in one line.

Am I going about this all wrong?

Thanks in advance for any help!

 

[Minty]

If you stored your attendance numbers as a separate table split down by the following columns you will find this much easier.

ProgramID
AttendDate
GuestType
NoOfGuests

Now the total number of any type of guest would simply be a sum(NoOfGuests) grouped by the ProgramID. You can also have as many Guest types as you needed without it breaking.

 

[hockey8837]

Ahhh.... yes that makes more sense. It's going to be a pain to correct, though, I think. I've got thousands of records.

:-/

Thanks!

 

[Minty]

You can probably make most of your new records by using a select / append query to get the data into the new table.

 

[hockey8837]

Just thought of something, though. I may have one person registering multiple different types of attendees.


Adults Kids
Jon Doe 2 3

But if I only have one "guesttype" how do I get the same details in one record? This will make record entry more tedious, and check-in for programs with a participant list more challenging.

GuestType NoOfGuests
Jon Doe Adult 2
Jon Doe Child 3

 

[Minty]

It will make it more interesting, but it's really the only way to store the data, your first layout is reverting to spreadsheet thinking, and as you have discovered makes queries and general data manipulation very awkward.

Technically they aren't the same guest type. 3 are Children, 2 are adults, so you would need you make two entries somehow to identify them.

You can easily set a default on a form to be the same as the previous record in code if that would help ?

 

[hockey8837]

Hi again!
Thanks for your help thus far!

Okay, so I'm trying to reformat my MMContactsPrograms table that has adults, children, and nonscouts separated (like a spreadsheet-d'oh!).

Looking at doing some kind of append or update query to get those three number fields into one, but I might (and by might, I mean definitely) need some help with the iff/then statement. I'll need it to copy [#Adults] to [TotalGuests] and label the [GuestType] as "Adult" if [Adult] does not equal 0. Same for [#Child] and [#NonScout] to likewise be "Child" and "nonscout".

I'll also need it to duplicate the record if either [#Adult], [#Child], or [#NonScout] are anything other than 0 so I don't lose the additional guest types/counts and so that [TotalGuests] can later be summed for the whole program.

I've included a screen shot of the MMContactsPrograms table in a query view if that helps at all.

Thanks so much in advance!!

 

[Minty]

Rather ironically I often find this kind of data reorganisation/manipulation easier in Excel...

However in this instance I think all you need to do is run a select query to pull in your key ID fields and one guest column at a time. So just do the Adults where >0 and simply have your GuestType field set to "Adult" or 1 or whatever you where going to use. If that gives you the correct results change it to and append query to your new attendance table.

Then just change it to Child > 1 and rinse and repeat.

 

[hockey8837]

Thanks!

I actually thought about this same thing on my drive home last night and started working it around in my brain a bit with the same approach. I'll give it a shot, thanks!

 

[hockey8837]

So,
I'm having some major issues converting this data due to the complexity of other items being tracked.

I'm having a hard time combining the info with multiple records for one contact (i.e. two records when they're registering adults and children, or possibly also nonscouts). We base our fees off of whether registrants are members vs nonmembers, so I had calculations in a query that would calculate fee based on member status, but now I'm stuck calculating based on member status *and* guest type status.

For example:
AdultFee: IIf([Member]=False,[#Adults]*[NonMemberAdultFee],[#Adults]*[MemberAdultFee])

I'm not a savvy enough programmer to figure out the extra WHERE condition in a query that will still return ALL results but appropriately charge adults, or kids, or nonscouts when I'm multiplying [totalguests] by [memberadultfee] when they may or may not be a member and when [guesttype] is adult.



Additionally:
When people register for programs, we generate a receipt for them (report) that is a contract with program info, so getting the receipt to pull multiple registration lines in one receipt rather than a separate receipt for each [guesttype] I think will be another future issue for me.

I've attached an excel export of a report I have pulling programs for the last month. You'll see the issue with repeat names in the excel...I just need to figure out a better way to compile those repeats that have an associated contact ID so are showing up more than once.

 

[Minty]

No attachment?

I would add a field MemberType to your bookings / or contacts list, you can then use that to look up / join to a table of centrally set rates? Default value of 0 would be a non member with normal rates...

 

[hockey8837]

Sorry- I had it in an .xlsx instead of .xls- attachment should be there, now!

I do have on the contact list a yes/no field for member, but each program has its own unique rates which may not apply to other programs so I'd just been storing them in the TblPrograms with all the other relevant program information (like time, date, etc.). Each program can have up to 4 rates- member adult, nonmember adult, member child, nonmember child.

Some programs do repeat multiple times throughout the year, some programs happen only once. Some programs are free, others are fee based.

I don't know if this helps or makes things more confusing! Hopefully the spreadsheet export will help to clarify a little- though this is just summary data so I don't have it pulling the fees or member status.

Thanks again in advance!