Results 1 to 3 of 3
  1. #1
    roaftech is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2012
    Location
    Romania
    Posts
    65

    Combining two queries into one report

    I have an aid donation database which contains details of all aid donations since 2009. Two similar queries list donations by item type and total for each year; the difference between the two queries is that one lists donations to persons aged 60+ only. Both queries work fine when run separately.

    Sample outputs from separate runs:


    Click image for larger version. 

Name:	StatsDump.jpg 
Views:	9 
Size:	101.0 KB 
ID:	29872

    I want to produce a report showing a combined output showing the total of each item per year along with the number of those given to the pensioners on the same row; ie:
    item type; year of donation; number of donations (all); value of donations (all); donations to 60+; value to 60+

    query is coded as
    Code:
    SELECT statsACPROS124.Item, statsACPROS124.ByYear, statsACPROS124.ItemCount AS CountAll, statsACPROS124.SumOfValoare AS ValueAll, [statsACPROS124+60].ItemCount AS Count60, [statsACPROS124+60].SumOfValoare AS Value60
    FROM statsACPROS124 LEFT JOIN [statsACPROS124+60] ON statsACPROS124.Item = [statsACPROS124+60].Item;
    However, this produces duplicated records - 190 in place of the 36 expected.
    Click image for larger version. 

Name:	StatsDump2.jpg 
Views:	9 
Size:	90.7 KB 
ID:	29873

    In acoustic terms, I am getting feedback. What did I miss?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Suggest puling Type field into both queries. Then include join clause with compound linking using Type and ByYear fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    roaftech is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2012
    Location
    Romania
    Posts
    65
    Perfect! Thank you for a prompt and accurate response!
    (Do you spend all day waiting for these questions?!!)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Combining Queries?
    By Khermann in forum Queries
    Replies: 1
    Last Post: 06-02-2016, 08:34 AM
  2. Replies: 2
    Last Post: 03-10-2015, 12:48 PM
  3. combining 2 queries
    By sdel_nevo in forum Queries
    Replies: 2
    Last Post: 07-23-2014, 04:41 AM
  4. Replies: 2
    Last Post: 06-21-2014, 07:11 AM
  5. Combining queries
    By jamo in forum Queries
    Replies: 11
    Last Post: 11-09-2012, 07:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums