Another Query Quandary

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CS - 09 Oct 2005 18:35 GMT

I am trying to discover average donations per participant at events we run.

Tables in the Query:
BasicContactT (Name, address, etc.)
EventT (Date, Time, Location, Type)
ParticipationT (Which Contact attended)
DonationT (Donation Collected Where/How/How Much)
EventTypeT (just a lookup for including a name in a report)

DB is structured this way because not all Contacts Participate, not all
Participants Donate, and not all Donations come through Events.

The DB works fine at present for showing me, via subform, total
participations and donations for each Contact, the major tracking use for
the DB.

Because we sometimes get Donations at Events in cash, and do not know who
provided it, I have handled this by creating a Contact called "Unknown
Donor" -- this ContactID shows up in the Donations Table, with a link to the
Event where it was gathered, but *not* in the Participant Table (because I
don't want to skew Participant stats).

I want to discover the average donations per participant for each event.
However, when I include the tables in the table,

This is my sql
SELECT EventT.EventDateStart, EventTypeT.EventTypeName,
Count([BasicContactT].[FirstName] & " " & [BasicContactT].[LastName]) AS
Expr1, Avg(DonationT.DonationAmnt) AS AvgOfDonationAmnt
FROM ((EventT INNER JOIN (BasicContactT INNER JOIN ParticipationT ON
BasicContactT.ContactID = ParticipationT.ContactID) ON EventT.EventID =
ParticipationT.EventID) INNER JOIN EventTypeT ON EventT.EventType =
EventTypeT.EventTypeID) LEFT JOIN DonationT ON ParticipationT.ParticipantID
= DonationT.ParticipantID
GROUP BY EventT.EventDateStart, EventTypeT.EventTypeName
ORDER BY EventT.EventDateStart;

I get an average, but it is skewed because the "Unknown Donor" contact does
not appear in the Participation Table and so, that donation is excluded (I
don't want Unknown Donor in the ParticipationT, it is only a value to
represent donations from sources at the event that we can't identify to a
specific participant). We know the donation came from someone at the event,
but not who.

Any help much appreciated. Thanks in advance.
Carol

Reply to this Message

Vincent Johns - 10 Oct 2005 10:19 GMT

> I am trying to discover average donations per participant at events we run.
>
[quoted text clipped - 4 lines]
> DonationT (Donation Collected Where/How/How Much)
> EventTypeT (just a lookup for including a name in a report)

OK, I set up the following Tables similar to yours:

[ContactT]:
ContactID FirstName LastName Address
--------- --------- -------- --------------
-1407502625 Generous Golfer 888 EZ Street
-362477780 Free Loader 555 5th Ave.
-26722596 StickInThe Mudd Nowheresville
109493301 --- Unknown Donor ---
1556451767 Jumpin' Jehosophat 123 Main

[DonationT]
DonationTID How Donation ParticipationID
Amnt
----------- --- -------- ------------------------------------
-1037386077 Cash $30.00 Unknown Donor Golf outing: 9/10/2005
-150555822 Cash $10.00 Jehosophat Golf outing: 9/20/2005
31285636 Check $50.00 Jehosophat Golf outing: 9/10/2005
1350978686 Cash $20.00 Unknown Donor Non-Event:
1538888741 Check $100.00 Golfer Golf outing: 9/10/2005
1967753947 Cash $40.00 Unknown Donor Golf outing: 9/20/2005

[EventT]:
EventID EventDate Time Location EventTypeID
Start
------- --------- ---------- -------- -----------
1116337320 Non-Event
1255563768 9/10/2005 8:00:00 AM Fairview Golf outing
1773406361 9/15/2005 7:00:00 PM Capitol Party
1033828582 9/20/2005 9:00:00 AM Brookwood Golf outing

[EventTypeT]:
EventTypeID EventTypeName
----------- -------------
-416036163 Party
-360681158 Golf outing
1200566378 Non-Event

[ParticipationT]:
ParticipationID ContactID EventID
--------------- --------- ----------------------
-2119384687 Jehosophat Golf outing: 9/20/2005
-875776851 Unknown Donor Non-Event:
-468687761 Jehosophat Golf outing: 9/10/2005
-352827262 Unknown Donor Golf outing: 9/20/2005
-125578800 Loader Party: 9/15/2005
0 Unknown Donor Golf outing: 9/10/2005
734605675 Loader Golf outing: 9/10/2005
1356136244 Golfer Golf outing: 9/10/2005

Note: For all of the foreign key fields, such as [EventID] in the
[ParticipationT] Table, I used Lookup Queries, so that instead of seeing
a meaningless number like 1773406361 you see a more meaningful name such
as "Party: 9/15/2005". But the value stored in the Table is the number.
This is an example Lookup Query:

[Q_LookupEvent]
EventID What
---------- ----------------------
1116337320 Non-Event:
1255563768 Golf outing: 9/10/2005
1773406361 Party: 9/15/2005
1033828582 Golf outing: 9/20/2005

defined as...

SELECT EventT.EventID,
[EventTypeT]![EventTypeName] & ": "
& [EventT]![EventDateStart] AS What
FROM EventT INNER JOIN EventTypeT
ON EventT.EventTypeID = EventTypeT.EventTypeID
ORDER BY EventT.EventDateStart;

> DB is structured this way because not all Contacts Participate, not all
> Participants Donate, and not all Donations come through Events.

In my example, Mr. Mudd does not participate and Mr. Loader participates
but does not donate. Event "Non-Event" accepts extraneous donations.

> The DB works fine at present for showing me, via subform, total
> participations and donations for each Contact, the major tracking use for
> the DB.

This can be accomplished via a Query similar to this (for donation):

[Q_TotalDonations]
FirstName LastName SumOfDonationAmnt
--------- ---------- -----------------
Generous Golfer $100.00
Jumpin' Jehosophat $60.00

SELECT ContactT.FirstName, ContactT.LastName,
Sum(DonationT.DonationAmnt) AS SumOfDonationAmnt
FROM (ContactT INNER JOIN ParticipationT
ON ContactT.ContactID = ParticipationT.ContactID)
INNER JOIN DonationT
ON ParticipationT.ParticipationID = DonationT.ParticipationID
GROUP BY ContactT.FirstName, ContactT.LastName,
ContactT.FirstName
HAVING (((ContactT.LastName)<>"Unknown Donor"))
ORDER BY ContactT.LastName, ContactT.FirstName;

> Because we sometimes get Donations at Events in cash, and do not know who
> provided it, I have handled this by creating a Contact called "Unknown
> Donor" -- this ContactID shows up in the Donations Table, with a link to the
> Event where it was gathered, but *not* in the Participant Table (because I
> don't want to skew Participant stats).

Not seeing an easy way to omit "Unknown Donor" from the list, I included
him in [ParticipationT] but omitted him from [Q_TotalDonations] via
"HAVING".

> I want to discover the average donations per participant for each event.
> However, when I include the tables in the table,
[quoted text clipped - 20 lines]
> Any help much appreciated. Thanks in advance.
> Carol

I couldn't tell if you wanted "Unknown Donor" included in the averages
or not. Assuming not, the following Query will do it:

[Q_AveragePerParticipant]

EventDateStart EventTypeName NumPar SumOfDona AvgAmtPer
ticipants tionAmnt Participant
-------------- ------------- --------- --------- -----------
9/10/2005 Golf outing 3 $150.00 $50.00
9/15/2005 Party 1
9/20/2005 Golf outing 1 $10.00 $10.00

SELECT EventT.EventDateStart,
EventTypeT.EventTypeName,
Count([ParticipationT]![ContactID]) AS NumParticipants,
Sum(DonationT.DonationAmnt) AS SumOfDonationAmnt,
[SumOfDonationAmnt]/[NumParticipants] AS AvgAmtPerParticipant
FROM ((EventT INNER JOIN (ContactT INNER JOIN
ParticipationT
ON ContactT.ContactID = ParticipationT.ContactID)
ON EventT.EventID = ParticipationT.EventID)
INNER JOIN EventTypeT
ON EventT.EventTypeID = EventTypeT.EventTypeID)
LEFT JOIN DonationT
ON ParticipationT.ParticipationID = DonationT.ParticipationID
WHERE (((ContactT.LastName)<>"Unknown Donor"))
GROUP BY EventT.EventDateStart,
EventTypeT.EventTypeName
ORDER BY EventT.EventDateStart;

If you want to include "Unknown Donor" donations in the average, delete
the 4th line from the end, beginning "WHERE". If you do that, you
should see numbers more like these, which include "Unknown Donor":

EventDateStart EventTypeName NumPar SumOfDona AvgAmtPer
ticipants tionAmnt Participant
-------------- ------------- --------- --------- -----------
9/10/2005 Golf outing 4 $180.00 $45.00
9/15/2005 Party 1
9/20/2005 Golf outing 2 $50.00 $25.00

-- Vincent Johns <vjohns@alumni.caltech.edu>
Please feel free to quote anything I say here.
-

Reply to this Message

CS - 11 Oct 2005 05:22 GMT

Thanks much, Vincent -- I got my problem solved!

Reply to this Message