Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    24

    Unanswered: Show ALL Records in Report

    Hi

    I have a database with two forms. The Clients form is for client name/address etc. It has a one-to-many relationship with a Visits form, for recording multiple visits attended by our care staff for each client. Each form has an auto-sum 'Log No' which is linked.

    I have a report set up that lists each client, alongwith that clients visits, totalling the hours.

    However, this only shows records for clients where there is also a related visits record.

    Is there any way of the report including ALL client records, even if there is no visits against that client?

    I have attached a copy of the database with 3 dummy records. The record for "Neil Wall" does not have any visits on it, and so it does not show on either of the reports.
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2012
    Posts
    24
    Further research found this solution:

    Change your join in the report's RecordSource query to be a left-join. That will return all rows from tableA regardless of whether they have a match in tableB in addition to returning all rows where there are matches. You can set the hide duplicates property to suppress what looks like repetitive data from tabldA as it prints for each record from tableB.
    Would this work for me, and if so, how do I actually do this?

  3. #3
    Join Date
    Feb 2012
    Posts
    24
    Sorry for spamming the forum, but I have an update.

    The above post WAS the issue. I have changed the join properties in the report to show all records from my client database, regardless of if they have a visit or not.

    This has led to another issue though.

    In the report, there is a field that counts how many visits there are, which I believe is just counting the number of times the log number is there. Now I have added clients with no visits, those clients show "1" in this field. How do i get it to be '0' if there are no records in the visit table for that client?

    The total hours field in the report shows #error for clients with no visits, but that is fine, as there is no data for it to calculate. I can live with that.

    We report to our contract holder about the number of visits though, and this counting of 1 for each client with no visit will cause issues.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •