Report only specific records

[kloun04]

I have a query that pulls in multiple fields where records have the criteria equal to True. What I am trying to do is create a report with the same multiple fields but show only records that are True and group them by either dept or diagnosis. Each department may include multiple diagnoses for a single patient. Because some records can have a field that is True and a field that is False I can't get what I want.



For example: A patient has the following diagnoses by a department

Fields
Department: Hospitalist Med 1
Diagnosis: Dementia (True), Depression (True), Sleep (False)

(Grouped by Dept) I want to see only those records under Hospitalist Med 1 whose records are True (Dementia) or (Depression)

AND as a different report

(Grouped by Diagnosis) I want to see only those records under Depression diagnosed by a specific department (Hospitalist Med 1)

Thanks.

[June7]

Did you try criteria under the Dementia and Depression fields placed on separate rows to invoke the OR operator?

This is not a normalized data structure. I always find trying to filter on multiple yes/no fields frustrating.

If you want different results for different reports, and you want to use filtered query object, each report will require its own query object. Alternatives:

1. Build query directly in the report RecordSource property.

2. All the reports can be based on same unfiltered query and then apply filter to the report. This can be done with the report Filter and FilterOnLoad properties or by command when the report is opened.

[kloun04]

June 7: I have the criteria for each field placed on a separate row and the query works appropriately.

1. Build query directly in the report RecordSource property. ---- Functioning query already built
2. All the reports can be based on same unfiltered query and then apply filter to the report. This can be done with the report Filter and FilterOnLoad properties or by command when the report is opened. --- The filter doesn't work because if I filter one field in the query it doesn't display the data for all the other fields that are True.

[June7]

Right, for option 2 filter cannot be applied in query, it must be applied to the report.