Share via

Report won't display all records

Anonymous
2013-09-27T20:41:56+00:00

I have created a couple of tables to hold data about help cases. I want to create a report of all help cases in the system. In some instances, those cases have an entry in the "Closed Cases" table and sometimes they do not. I built a query which correctly displays ALL of the cases regardless of whether they are marked closed or not. The report based on this query though only displays cases that have data in the "Closed Cases" table.

I've checked my relationships and they are supposed to be pulling all data from the cases and only the data that matches from the closed. For some reason, I just can't get the report to display any records that don't have full and complete information in the query (even though the query displays it fine).

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

15 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-09-27T20:54:05+00:00

    I'm quite new at this whole Access thing and just trying to make this project work.

    The reason I chose to go with a separate table that has closed case information is that I wanted to capture the date of when the case is closed as well as a summary of the case. I also have another table that captures notes from the progress of this case and is also blank for some records as they don't have notes yet. Again, the query is displaying correctly.

    My settings for the filter under the Data section of the Property Sheet is as follows:

    Filter - BLANK

    Filter on Load - Yes

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2013-09-27T23:27:31+00:00

    I would love to simplify the structure of it all. I understand using a column in the "master" case table to denote a closed case but I'm having issues understanding how I would then reopen a case yet preserve the date it was originally closed. Due to the nature of the stuff we're doing, we could close and reopen a particular case multiple times and it would be helpful to keep track of that as well...hence the separate table with a one to many relationship. Maybe I went wrong somewhere with that one.

    At any rate, yes, the underlying query displays all of the entries. The report built on the query only displays entries that have a corresponding entry on the closed case table. I will post the SQL as soon as I can get access to the file again. My home computer doesn't have Access.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-09-27T22:03:50+00:00

    I agree with Scott that a second table is probably not needed; but if you want to get the report working with your current structure, it should be possible. If you open the Query upon which the report is based do you see all the records, and they just don't appear on the Report? That would be very odd if so. If you are missing records in the query, please open the query in SQL view and post the SQL text to a message here. It sounds like you may just need to change the join type (in the Query, not in the relationships window).

    Was this answer helpful?

    0 comments
  4. ScottGem 68,810 Reputation points Volunteer Moderator
    2013-09-27T20:58:05+00:00

    So you add a field to your table: DateClosed. If that field is blank the record is still open. Again, no need for a separate table to complicate your life.

    Was this answer helpful?

    0 comments
  5. ScottGem 68,810 Reputation points Volunteer Moderator
    2013-09-27T20:46:27+00:00

    First, there is no real good reason to have two tables. You should have one table with a flag to indicate whether a case is closed.

    Second, if your query returns the correct records, and the Recordsource of the report is that query, then it should show all the records. You might want to check the Filter property to see if that is what is restricting records.

    Was this answer helpful?

    0 comments