Selecting Distinct Records in union query/report

[PocketRocket]

Using Access 2007. I have a table of clubs and a table of club_gifts. They
are linked via a key club#. I want to produce a report of all clubs from the
club table plus all records from the club_gifts. There may be multiple rows
for a given club in the club_gifts table, but I only want one row with the
total of the gift_amount. I have build a union query, but when a club has a
record in the club_gift table, it appears twice in the query results, once as
the row in the clubs table and once as a record in the club_gifts table. I
only want the latter record to show. Is there a way to do this in either the
query or a report based on the query?

Club table: Club_gifts table:
Club_Num Club_Num
Club_name Club_name
Club_mbrs Gift_date
Gift_amount

Result desired:
Club Name Members Gift Amount Gift/Mbr
AAAAA 42
BBBBBB 50 $150 $3.00
CCCCC 120
DDDDD 70 $350 $5.00
Total 282 $500 $1.77 ($500/282=1.77)

Does anyone have any good ideas for me to try?

 

[KARL DEWEY]

Try this --
SELECT [Club].[Club_name], Count([Club].[Club_mbrs]) AS Members,
Sum([Club_gifts].[Gift_amount]) AS [Gift Amount],
Sum([Club_gifts].[Gift_amount])/Count([Club].[Club_mbrs]) AS [Gift/Mbr]
FROM [Club] LEFT JOIN [Club_gifts] ON [Club].[Club_Num] =
[Club_gifts].[Club_Num]
GROUP BY [Club].[Club_name];

 

[John Spencer]

Is Club_mbrs a count of the number of members in the club?

If so, a query that looks like the following should work.

SELECT Club.Club_Name
, Club.Club_mbrs
, Sum(Club_Gifts.Gift_Amount) as GiftTotal
, Sum(Club_Gifts.Gift_Amount)/Club.Club_Mbrs as AvgGift
FROM Club LEFT JOIN Club_Gifts
ON Club.Club_Num = Club_Gifts.Club_Num
GROUP BY Club.Club_Name, Club.Club_mbrs

The above is the SQL view of the query and you should be able to paste it
directly into the SQL veiw of a new query. If you NEED to build this in query
design view, post back for instructions (if needed).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[PocketRocket]

Is Club_mbrs a count of the number of members in the club?

If so, a query that looks like the following should work.

SELECT Club.Club_Name
, Club.Club_mbrs
, Sum(Club_Gifts.Gift_Amount) as GiftTotal
, Sum(Club_Gifts.Gift_Amount)/Club.Club_Mbrs as AvgGift
FROM Club LEFT JOIN Club_Gifts
ON Club.Club_Num = Club_Gifts.Club_Num
GROUP BY Club.Club_Name, Club.Club_mbrs

The above is the SQL view of the query and you should be able to paste it
directly into the SQL veiw of a new query. If you NEED to build this in query
design view, post back for instructions (if needed).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John: Thank you. You are right that Club_mbrs a count of the number of
members in the club. That worked but in working it, I found that I had left
out one bit of information. There is a check date in the club_gifts table
and I need to include only the checks dated after a certain date (10/1/2009).
When I add that to the parameters, I get those clubs who have given, but not
the clubs who have not given this year. I did not realize that the forum
would not take the spaces I carefully inserted in my example. The example
actually shows some clubs with gifts and some without. Anyway, I am glad to
have a fellow Terp assisting.
Don (UMD '66)

 

[John Spencer]

Ok, then you need to use a subquery in the FROM clause to limit the records

SELECT Club.Club_Name
, Club.Club_mbrs
, Sum(qGifts.Gift_Amount) as GiftTotal
, Sum(qGifts.Gift_Amount)/Club.Club_Mbrs as AvgGift
FROM Club LEFT JOIN
(SELECT * FROM Club_Gifts WHERE CheckDate >= #10/1/2009#) as qGifts
ON Club.Club_Num = qGifts.Club_Num
GROUP BY Club.Club_Name, Club.Club_mbrs

IF you get an error with that, you can always use two queries.
The first query would get the information from the Club_Gifts table (limited
by your criteria).

The second query would use that saved query and the Club table. You would
just replace references to Club_Gifts with references to the saved query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[PocketRocket]

John:

The subselect worked perfectly. I had forgotten about that, not having
written native SQL for 15 years. Thanks for all your support. Go Terps. --
Don