Totals query or something else?

[JacqueWill]

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!

[aytee111]

Your table structure needs to be normalized, ONE table for ONE subject. You are mixing and matching subjects and this is going to cause you major issues, as you are encountering already.

Table for programs - ProgramID, name, subject, category, etc.
Table for courses - CourseID, ProgramID, date, time, location, TeacherID, etc
Table for teachers - TeacherID, name, etc
Table for registrations - RegID, CourseID, person, etc
Table for attendance - AttID, CourseID, total attendees, etc

Remove the "#" from the field names, never use special characters or spaces in field names.

[CJ_London]

Crossposted here with answers https://www.access-programmers.co.uk...d.php?t=294681

@Jacque -please read this link about cross posting etiquette http://www.excelguru.ca/content.php?184