Results 1 to 4 of 4
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    160

    Report with data from 2 queries

    So this might belong in the queries section, as the answer may be, how to create a single query with all this information.

    and it would take a small novel to explain why the data is setup this way but just accept that it has to be the way it is.

    one query pulls data from a table about past due fees, and fines for each chapter in our organization.

    another query pulls data from a different table about loans that any chapter has taken out from the organization.

    how can I get all of this data on a single report.

    the pictures below should help explain better what I am trying to do.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	13 
Size:	83.2 KB 
ID:	47010


    Click image for larger version. 

Name:	Capture2.PNG 
Views:	13 
Size:	49.3 KB 
ID:	47011

    Thanks for any help

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,881
    Can you post a copy of the data base with only a few related records from each of your tables?
    Might be easier to get focused responses if readers had more context.

    First thought is perhaps Union query???

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,054
    I usu make a 'report' table,
    Q1 appends the fees to the table
    Q2 appends the loans to the table,
    Q3 sums the report tbl for output

    or
    1 query to join clients qry & loans qry & fees qry for the report.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,850
    Why not simply create a new query from the two totals (PastDue and Loans) with a left outer join between the ChapeterName fields (arrow pointing towards LoansTotals).? An alternative would be adding a calculated field in the first query qry_AllChptPastDue using a dLookup on the second: Total_Loanlookup("[SumOfTransAmount]","qry_AllLoansTotals","[ChapterName]='" & [ChapterName] & "'").

    To add this value to the report you would probably need to add a grouping on the ChapterName field and add the Total_Loans in either the footer or header.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Use 2 Queries As Data Source For Report
    By chalupabatman in forum Reports
    Replies: 2
    Last Post: 01-03-2019, 12:32 PM
  2. Replies: 3
    Last Post: 09-11-2018, 12:40 PM
  3. Replies: 11
    Last Post: 04-07-2015, 09:15 AM
  4. Replies: 19
    Last Post: 08-01-2013, 10:47 AM
  5. Replies: 3
    Last Post: 05-21-2010, 03:57 PM

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