Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77

    My report yields more than one result

    I am not sure that what I want can be done. Here goes. I have a report that takes data from 2 tables via 1 query. The report gives me the expected results in fact it gives me more than I would like. I only need 2 pieces of data from table 2 and all of the data from table 1 but the results result in 2 or more lines with exactly the same info. I think the results are because table 1 (the controlling table) has only one set of data based on 1 field (MRN) set as a text field with index that allows no duplicates. Table 2 uses the same field (MRN) but is set to allow duplicates since I track different data on each table.



    What I need is for the report to only provide the latest table 2 data, by date, on one line with data from both tables. The mulitple lines I think are coming because there are multiple entries in table 2.

    Is this possible?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    We might need a sample here Molly.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    Have a quick fix by using

    select distinct
    instead of
    select

    in the underlying query statment

  4. #4
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77

    I have made a copy

    I have attached a word file which shows part of the report.

    As I said I need only have a one line item per patient whether they are a returning or a new patient. If they are returning I only need the present or last date. I have included the query from which the data is drawn.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Is your report using the "qryChaplain Daily List Query" query?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  6. #6
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77

    Yes but!

    I have changed the query several times. If the query has both the tblContact and tblTimeData then yes that is the query.

  7. #7
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    Would you give us the actual database file instead?
    The word file only has partial information we need to analyse.
    The actual sql query is still missing. (the critical point in this question)

  8. #8
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77

    All Zipped up

    Attached is the file. All patient names have been removed for HIPPA. There are some phony names entered.

  9. #9
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    I spotted that
    qryChaplain Daily List Query
    has 2 different [tblTime Data].ReturnDate for one patient of a Date

    Is this [tblTime Data].ReturnDate really necessary to differentiate the query data?

    Is this a duplicate or not?

    Or would you like to choose the last [tblTime Data].ReturnDate data record instead?

  10. #10
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77

    Corrected

    The "ReturnDate" should not be on "tblContact". Therefore, I deleted that field in "tblContact" and you can do the same.

    If you do the same and then go to the main menu and click on "Go To Report" and then click on "Review and print Chaplain's List" you will note that "Cornpone, Justin A" shows up twice. Note that the query has "False" in the criteria for the "Discharge" filed which filters out any patient who has been discharged.

    What I want is for the db to return only the last incident (in this case 6/11/2009) for for returning patients and all other patients who are not discharged. I need the code to accomplish this.

    TIA

  11. #11
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    Do you mean that you want to select only the record with

    the last [tblTime Data].ReturnDate data record on it when it has duplicate?

  12. #12
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77

    Arrow Cornpones - All of um!

    I want the report to return:

    1) Cornpone, Justin A dated 6/11/2009 - A return patient;

    2) Cornpone, Jubilation T; and

    3) Cornpone, Sally J.

    All are current patients but Justin has been discharged and has returned.

  13. #13
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    I offer a simpler solution.

    As you only need two fields in [tblTime Data]
    [tblTime Data].ReturnDate and [tblTime Data].ReturnPatient.

    You can create a temporary query to have the max([tblTime Data].ReturnDate) by [tblTime Data].ReturnPatient

    Then you can easily create your final query solution by linking this temporary query to the table tblContact.

  14. #14
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    A much more complicated subquery approach is
    done by a lot of trial and errors.
    This is painstaking and not recommended for normal users.

    The code of the query (qryChaplain Daily List Query) is rewritten as follows:-

    Code:
    SELECT tblContact.Date, tblContact.RoomNumber, 
    tblContact.PatientName, tblContact.PatientMRN, 
    tblContact.Discharged, tblContact.City, tblContact.Code, 
    tblContact.ParishName, tblContact.Memo, 
    tblContact.PatientDeclines, tblContact.PatientFirstName, 
    tblContact.PatientMiddleInitial, tblContact.CheckBack, 
    tblContact.Phone, tblContact.Communion, tblContact.[City 
    Name], tblContact.PetTherapy, tblContact.PetType, [tblTime 
    Data].ReturnDate, [tblTime Data].ReturnPatient 
    FROM tblContact LEFT JOIN [tblTime Data] ON 
    ((tblContact.PatientMRN=[tblTime Data].[Patient MRN])
    )
    WHERE (
    ((tblContact.Discharged)=False)
    and 
    (
    ([tblTime Data].ReturnDate is null) or
    (
    [tblTime Data].ReturnDate in 
    (
    select top 1 [tblTime Data].ReturnDate
       FROM [tblTime Data]
       WHERE [tblTime Data].[Patient MRN] = [tblContact].[PatientMRN]
       ORDER BY [tblTime Data].ReturnDate DESC
    )
    )
    )
    )
    ORDER BY tblContact.RoomNumber;

  15. #15
    hawzmolly is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    77

    Thumbs up Thank you, Thank you, Thank you!!!

    After playing around with the code provided I am getting exactly what I want. I have also learned even more. This one can be marked SOLVED.

    Here is my code:

    SELECT tblContact.Date, tblContact.RoomNumber, tblContact.PatientName, tblContact.PatientMRN, tblContact.Discharged, tblContact.City, tblContact.Code, tblContact.ParishName, tblContact.Memo, tblContact.PatientFirstName, tblContact.PatientMiddleInitial, tblContact.Phone, tblContact.Communion, tblContact.[City Name], tblContact.PetTherapy, tblContact.PetType, [tblTime Data].ReturnDate, [tblTime Data].ReturnPatient
    FROM tblContact LEFT JOIN [tblTime Data] ON tblContact.PatientMRN = [tblTime Data].[Patient MRN]
    WHERE (((tblContact.Discharged)=False) AND (([tblTime Data].ReturnDate) Is Null Or ([tblTime Data].ReturnDate) In (select top 1 [tblTime Data].ReturnDate FROM [tblTime Data] WHERE [tblTime Data].[Patient MRN] = [tblContact].[PatientMRN] ORDER BY [tblTime Data].ReturnDate DESC)))
    ORDER BY tblContact.RoomNumber;

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export a simple query result to an excel file
    By wop_nuno in forum Import/Export Data
    Replies: 1
    Last Post: 05-21-2009, 04:18 AM
  2. Replies: 2
    Last Post: 04-29-2009, 11:37 AM
  3. Search result links adding extra info
    By Vbcw in forum Programming
    Replies: 0
    Last Post: 12-19-2008, 12:19 PM
  4. Result of Count Query not known elsewhere
    By Carole in forum Access
    Replies: 1
    Last Post: 09-07-2008, 09:39 AM
  5. Replies: 2
    Last Post: 08-04-2008, 04:16 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