Results 1 to 3 of 3
  1. #1
    Mithril28 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    21

    Modify report to use result set from a query that returns more than 1 row

    I have a report that creates confirmation letters to send to customers when they update their phone number.
    It gets data from a table using a query that is called from autoexec.

    The design of the report and the table is simple:
    Date
    Name
    Address

    Phone Type


    Old number
    New number

    If a user changes more than one number (cell, home, business) at a time there are then multiple rows in the table, and the information is sent out in multiple letters. My managers used to be fine with multiple letters going out to the same customer in a day, but now they would like it to all be in one letter.

    Is there a way to use data in a report from a query that returns more than 1 row as follows?

    Date
    Name
    Address

    Phone Type
    Old number
    New number

    Phone Type
    Old number
    New number

    Phone Type
    Old number
    New number

    Thank you!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,371
    i hope you have a subtable called tPhones
    PhoneID (autonum)
    ClientID
    PhoneType
    PhoneNum


    you want the autonum. This helps show the newest #.

    make a subreport. The subreport uses a subquery.
    the subqueries:
    Q1,pulls Max(PhoneID), ClientID from tPhones
    Q2 joins Q1 to tPHones to pull all the data
    the phone subform uses Q2

    the main report will have the client data,
    the Phone SubRpt links to the clientID

    it works on a query to, to pull only 1 phone by joining Q2 to the data table and produce 1 record /client.
    This will show UserID, phone

  3. #3
    Mithril28 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    21
    Thank you, I will try that!!

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

Similar Threads

  1. Replies: 3
    Last Post: 05-24-2017, 01:26 PM
  2. Replies: 7
    Last Post: 04-27-2017, 11:00 AM
  3. TempVars Returns Strange Result
    By sberti in forum Access
    Replies: 3
    Last Post: 09-30-2015, 05:41 PM
  4. Replies: 3
    Last Post: 07-22-2014, 11:37 AM
  5. Replies: 9
    Last Post: 05-08-2013, 02:37 PM

Tags for this Thread

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