Selecting Distinct Records in union query/report

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?  
0
Utf
1/5/2010 9:45:01 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
1095 Views

Similar Articles

[PageSpeed] 36

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]; 


-- 
Build a little, test a little.


"PocketRocket" wrote:

> 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?  
0
Utf
1/6/2010 12:18:01 AM
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 wrote:
> 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?  
0
John
1/6/2010 1:31:29 PM

"John Spencer" wrote:

> 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)
0
Utf
1/6/2010 11:03:01 PM
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 wrote:
> 
> "John Spencer" wrote:
> 
>> 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)
0
John
1/7/2010 2:09:19 PM
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

"John Spencer" wrote:

> 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 wrote:
> > 
> > "John Spencer" wrote:
> > 
> >> 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)
> .
> 
0
Utf
1/7/2010 8:54:05 PM
Reply:

Similar Artilces:

Export or Print multiple reports from one Access Report by Group
I have a sales report that is grouped by Salesperson. Each salesperson has multiple pages of the report and I need to either print it or export it to PDF as individual reports for each salesperson. Is there a way to export/print the report into seperate reports for each Salesperson? Thanks. I am pretty new to Access, but I have figured out how to get the report in the structure I want it. I just can't get the information out of the program in the format. I can print as one PDF document and then go and cut that up in Adobe, but I wanted to see if Access could do it for me and save ...

Adding Multiple Records
Hi, I am attempting to add multiple records to a table from a command button. All but one of the fields will be the same for each record. I have checkboxes with user names, and I'd like to add a record for each user that is checked. The form is unbound - with textboxes that will hold the standard information for each record. I just need to add separate records based on whether the user's checkbox is checked... if chkbox.value = -1 then docmd.runsql "insert into..." I'm having trouble setting up the loop and/or recordset. Any guidance is greatly appreciated. Thanks ...

Financial Reports crash GP
One of our users tried to run a Balance Sheet Financial Report this morning and it crashed GP on her PC. A web page popped up that says: "This problem was caused by Microsoft Dynamics GP 10.0. An update is available that might solve this problem." I also check the event logs for the pc and found this error: "The description for Event ID ( 1000 ) in Source ( Dexterity ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retriev...

Changing Field values after a report is printed
I have a form that is filled out by a user for deliveries, one product per entry. The users may create several entries for different products for delivery to various areas. After the form is filled out a button is clicked that generates a report to print out for obtaining signatures for delivery. Is there a way to also change the value of a field in my table to indicate that the delivery / form has been printed? I need this as sometimes a second delivery for that day may be needed and I do not want the deliveries that have already been made to be printed again. Thank you, ...

Report Templates
Can anyone help me. I'm looking to see if there are any free basic "Business Report" templates for Publisher out there. I'm not looking for anything complex, but a simple design to turn a long 60 page text document into something much more readable. Thanks -- ************************************* Colin McDonald colmacd@btinternet.com ************************************* >-----Original Message----- > >Can anyone help me. > >I'm looking to see if there are any free basic "Business Report" templates >for Publisher out there. > >...

Struggling with MS Query...
I am learning about MS Query from a book. I am falling at the first hurdle... I have an Excel file on my desktop that I want to be able to query. It contains list of numbers with column headers (i.e. your standard table layout) As I understand it, I need to specify that Excel file as a data source. So I select <New Database Query> and get the <Choose Data Source> dialog box. Now I get confused... I have tried using <New Data Source>. Here I get a list of 'drivers' to select from. I selected 'Excel Driver'as the most obvious (as I want to query an Exc...

Control Source for text box on a report
I have a table with only 2 fields. One is the name of Department and the other is the total number of positions in that department. On my report I have a label for every department. Next to that label I have a text box that counts every record on my report for each department. Next to that I want to add another text box that contains the corresponding number in the table for that department. How can I easily do this? Thank You, CEV I forgot to mention that I would also like to add yet another text box that adds the total of all the other text boxes that contain the total number of ...

Queries 06-01-10
I need help with this , it will not run, am i missing something. Private Sub Worksheet_Activate() Call Sheet5.getvalues Call Sheet5.SortRange1 End Sub Sub getvalues() lr = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row) 'MsgBox lr Rows("2:" & lr).ClearContents With Worksheets("Automated RAC") slr = .Cells(Rows.Count, "a").End(xlUp).Row 'MsgBox slr For L = 2 To slr dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1 ' If .Cells(L, "L") =jone and not .cells(f, "F") = "Closed...

Error when selecting reports in CRM 3.0
I have a fresh install of CRM 3.0 on Server 2000, and when I view reports on the server, the reports page show up correctly. However, when I try to view reports on my pc I get a "The request failed with HTTP status 401" error. In going through previous questions posted, I have looked at all possible solutions and everything seems to be set up correctly. I have verified everything in the documentation (Additional Setup Tasks Required if Reporting Services Is Installed on Different Server), and I am using System User as the service account, yet it still gives me the error. An...

running difference between adjacent records in report
I need to calculate the "in-between-time" for two records. Example: process 1 starts at 11:00 a.m. and ends at 11:15 a.m.; process 2 starts at 12:30 p.m. and ends at 12:45 p.m. I need to calculate the difference between the end of process 1 and the beginning of process 2 in an Access 2003 report. This is sort of the opposite of a running sum, but I cannot figure out if I can manipulate the running sum function to find the answer. You can do this by declaring a variable to hold the ending date/time from the previous record, so you can compare it to the starting time from the...

Tender Summary report combining all registers and tender types
Hi, I know the Z report breaks down the tender type totals. Is there any report template to combine all registers into one total for each tender type (Visa/MC, debit card, Discover card, American Express, check, cash, etc) thanks This is a multi-part message in MIME format. ------=_NextPart_000_0118_01C8B095.F2EA34B0 Content-Type: text/plain; format=flowed; charset="utf-8"; reply-type=original Content-Transfer-Encoding: 7bit David, The attached report will do if you have HQ. Hope this helps Regards, Don "David Moffatt" <DavidMoffatt@discussions.microso...

SRS (CRM_Locale) is set to false (Error when trying to view report
Hi When a user including CRM Administrator tries to access any report from reporting services they get the following error: "The property 'nullable' of report parameter 'CRM_Locale' is set to false. However, the corresponding parameter (default) value is null or contains an empty string. (rsProcessingError)" I can open the reporting server's site in a browser seperate from CRM and view the list of reports but I get the same error. But my CRM reports properly functioning at CRM clients Any usefule suggestions? Cheers Pareek Hi, I have exactly the same issue...

better sorting and adjustments on bank reconciliation selection wi
It would be great to be able to sort the transactions by amount.. This would really spee the process for finding those elusive transactions when doing arduous bank recs. (I just did 5 bank recs...) thanks, Jason ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://...

Run a Query using VBA without User Interface
Hello, I have a VBA Macro that Opens an Append Query (see below), which works great, except for the end user has to answer the following MsAccess prompts: 1) You are about to run an append query that will modify data in your table. (They have to click Yes) 2) You are about to append 1 row(s). (They have to click Yes) I'd rather them not have to answer either question. Instead I'd like the Append Query to run and do what it's asked without any human intervention. Can this be done? Please help! VBA Macro Code: DoCmd.OpenQuery "tblTask_List Query", acViewNorm...

Insert record into IV00108 manually
Hi, I was trying add new price level information with special price into IV00108 by inserting new record for each item, when i look into GP price list maintenance it's showing special price list with 0 price and when i select any lookup for that line and come back it update actual price. Some window level scripting or something might required to update IV00108?? Any suggestions would be highly appreciated. Thanks in advance, Regards, Dharmesh Hi Again, Actually i was missing to do entry in IV00107 and hence i was facing this problem, It's sorted out now af...

Cannot promote email to CRM record and select regarding case
In SFO we cannot promote an incoming email to a CRM record and select regarding Case. We can select regarding everything else, and when sending we can select regarding Case, but when promoting Case is simply not on the Drop Down menu. We have 1.2 with all the latest updates. Can someone please help? Unfortunately SFO does not let you select cases in "regarding" You can hack the XML for the lookup so that it does this, but thats not supported... Peter "Biyoojok" <biyoojok@gmail.com> wrote in message news:1116819255.293512.81100@g49g2000cwa.googlegroups.com.....

COUNTIF query #2
Hey all Hopefully a nice easy one.... We have a logging system, and the spreadsheet basically has the person name doing a call, what the call is and how long it took. This is use all day so loads of calls get logged by various people. Say column A is Persons name and Column B is time spent. I need calulation that checks how much time 'person 1', 'person 2', 'person 3 etc has spent on calls during the day and add's it all up. Please can someone show me how to do this, i have tried a COUNTIF bu cant quite get it to work. Many thanks in advance. Darren (digital_life...

How to show what record number you are on in form?
I have created an unbound form in Access 2007 and am listing the records which are in my table. I found out how to show how many records there are in the table but would also like to show which record you are on. For example, when I click the next button to see the next record it will show: "Record 2 Of 30", and so on. This is the one I used to count the amount of records: Dim countRecords As Long countRecords = DCount("customerNumber", "customer") Me.txtRecordAmount = "Record # Of " & countRecords How can I show what reco...

Only Display records before and after a change in teh value of a f
I have a table that looks like the following RUNID | DATETIME | PROCESS 0001 1/1/09 1201 Neck 0001 1/1/09 1202 Neck 0001 1/1/09 1203 Neck 0001 1/1/09 1204 Neck 0001 1/1/09 1205 Neck 0001 1/1/09 1206 Body 0001 1/1/09 1207 Body 0001 1/1/09 1208 Body 0001 1/1/09 1209 Body 0001 1/1/09 1210 Neck 0001 1/1/09 1211 Neck 0001 1/1/09 1212 Neck 0001 1/1/09 1213 Neck 0001 1/1/09 1214 Body 0001 1/1/09 1215 Body 0001 ...

Report based on form value
I have a report based on a table. The table contains an ID# which is unique to an individual, but the ID# may appear more than once in the table. I also have a form that displays one ID# and has a command button to print the report. I use the DoCmd.OpenReport command in the OnClick function of the command button, but am having problems with the criteria part. strCriteria = "[ID#] = " & Me.ID where ID# is the field in the table and ID is the field on the form, returns a dialog box asking for the ID#, though the current ID# is displayed in the dialog box. I'm pr...

Solver internal error sensitivity report
Hello, I use the solver add-in in the German version of Excel 2007. When I try to generate sensitivity reports I get an error message: unexpected internal error. The generation works once when I uninstall and re-install the add-in. Obviously it is not an option to re-install the add-in for each sensitivity report... Any ideas what the cause may be? Any suggestions how to solve the issue? Thanks. Patrick ...

Using categories, task, etc to create reports
I would like to summarise/quantify information from Outlook, using existing fields. For example: the # of tasks and phone calls that belong to a particular Category; or, the amount of time spent for a category or for a contact, using date ranges. Is there anything existing in Outlook? is there a way to do this in Outlook? Any suggestions? ...

MX Record SBS 2008
Hi, Setup a new SBS 2008 with remote.mydomain.com. Installed a 3rd party SSL cert. Previously the client was using POP3 emails. Problem is we can't receive emails. We've changed the DNS A record for 'remote' to the external server IP address as normal. We've deleted the old MX record for Mail and replaced it with Remote. A Records Mail > Server IP address Remote > Server IP address MX Record Remote > Server IP address > Priority 0 I have made sure port forwarding is setup on the router, I can telnet port 25 to the server and I can se...

How do I merge records to post cards, one record per card, four/sh
I completed the merge to Avery 8383 - 5.5 x 4.25 postcards - 4 per sheet -- BUT the Publisher document prints FOUR OF THE SAME ADDRESSES PER SHEET -- I NEED FOUR DIFFERENT ADDRESSES FROM THE MERGE PER SHEET. HELP!!! You can only have one card on your screen for the merge to work correctly. In page setup, postcards. Print preview will show the same information on each card, this is a bug. Print a sample if you are unsure. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "jOAN mARIE" <jOAN mARIE@discussions.microsoft.com...

list attachments in a form or record
I have figured out how to add attachments to a record, but the default way of handling them on forms isn't the greatest. I would like to be able to have a list of the attachments directly on a form (I have a form that lists details for contacts and I would like one of the fields to be the list of attachments). I would also like the ability to select and open the attachments from the form. Is this possible? I did a quick search and haven't found any discussion that covers this. I'm using Access 2007. First I would question that maybe you shouldn't store the do...