Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-11-2017, 06:51 AM   #1
hockey8837
Newly Registered User
 
Join Date: Sep 2009
Posts: 106
Thanks: 2
Thanked 0 Times in 0 Posts
hockey8837 is on a distinguished road
Totals query or something else?

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!

hockey8837 is offline   Reply With Quote
Old 07-11-2017, 06:57 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,419
Thanks: 88
Thanked 1,206 Times in 1,180 Posts
Minty will become famous soon enough Minty will become famous soon enough
Re: Totals query or something else?

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.
__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
hockey8837 (07-13-2017)
Old 07-11-2017, 07:38 AM   #3
hockey8837
Newly Registered User
 
Join Date: Sep 2009
Posts: 106
Thanks: 2
Thanked 0 Times in 0 Posts
hockey8837 is on a distinguished road
Re: Totals query or something else?

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!

hockey8837 is offline   Reply With Quote
Old 07-11-2017, 07:42 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,419
Thanks: 88
Thanked 1,206 Times in 1,180 Posts
Minty will become famous soon enough Minty will become famous soon enough
Re: Totals query or something else?

You can probably make most of your new records by using a select / append query to get the data into the new table.
__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 07-11-2017, 07:43 AM   #5
hockey8837
Newly Registered User
 
Join Date: Sep 2009
Posts: 106
Thanks: 2
Thanked 0 Times in 0 Posts
hockey8837 is on a distinguished road
Re: Totals query or something else?

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
hockey8837 is offline   Reply With Quote
Old 07-11-2017, 08:05 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,419
Thanks: 88
Thanked 1,206 Times in 1,180 Posts
Minty will become famous soon enough Minty will become famous soon enough
Re: Totals query or something else?

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 ?
__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 07-13-2017, 09:33 AM   #7
hockey8837
Newly Registered User
 
Join Date: Sep 2009
Posts: 106
Thanks: 2
Thanked 0 Times in 0 Posts
hockey8837 is on a distinguished road
Re: Totals query or something else?

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!!
Attached Images
File Type: jpg Capture.JPG (24.6 KB, 26 views)

hockey8837 is offline   Reply With Quote
Old 07-13-2017, 11:39 PM   #8
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,419
Thanks: 88
Thanked 1,206 Times in 1,180 Posts
Minty will become famous soon enough Minty will become famous soon enough
Re: Totals query or something else?

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.
__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 07-14-2017, 09:05 AM   #9
hockey8837
Newly Registered User
 
Join Date: Sep 2009
Posts: 106
Thanks: 2
Thanked 0 Times in 0 Posts
hockey8837 is on a distinguished road
Re: Totals query or something else?

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 is offline   Reply With Quote
Old 07-18-2017, 10:26 AM   #10
hockey8837
Newly Registered User
 
Join Date: Sep 2009
Posts: 106
Thanks: 2
Thanked 0 Times in 0 Posts
hockey8837 is on a distinguished road
Re: Totals query or something else?

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.
hockey8837 is offline   Reply With Quote
Old 07-18-2017, 11:59 PM   #11
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,419
Thanks: 88
Thanked 1,206 Times in 1,180 Posts
Minty will become famous soon enough Minty will become famous soon enough
Re: Totals query or something else?

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...
__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 07-19-2017, 04:22 AM   #12
hockey8837
Newly Registered User
 
Join Date: Sep 2009
Posts: 106
Thanks: 2
Thanked 0 Times in 0 Posts
hockey8837 is on a distinguished road
Re: Totals query or something else?

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!
Attached Files
File Type: xls qryALLProgramAttendeesReport.xls (38.0 KB, 11 views)

hockey8837 is offline   Reply With Quote
Reply

Tags
totals q

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Totals in Query accessjunior Queries 5 10-03-2015 05:36 AM
Totals Query/sums/grand totals thsoundman Queries 17 04-16-2013 11:24 AM
Displaying previous totals, and current totals, on a report Janzi Queries 0 12-05-2011 11:17 AM
Dynamic Report - Preview Totals differ from Printed Totals Longworth2 Reports 5 06-16-2009 01:18 PM
[SOLVED] Totals in Report returns totals for all records dcg2122 Reports 1 11-09-2003 03:52 PM




All times are GMT -8. The time now is 06:32 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World