Query results into Report

[VictoriaAlbert]

I'm new to Access and would greatly appreciate some insight into generating reports. Here's what I want to do.



I have a table with data fields that include County, Hospital, Social Worker, Patient Case Number, Payer, Referral, Outcome, etc. I want to generate a report that summarizes this table (and not just list those records that meet a certain criteria, which is what all the examples seem to be about). For example, one part of the report would be listing the different Social Worker's names and how many patients they saw. A calculation may be to also include what percentage of all patients this number of patients represents. The next part (ideally on the same page) of the report would then list the different Payer Sources, a count, and a percentage of the total.

I can generate queries for most of what I want to do, but I cannot seem to figure out an efficient way to get all the data I need on one report. For example, I can generate one query that returns the Social Worker's names and how many patients they saw, then another query (based on that first query) that returns the percentage of the total. I then include both queries in the Record source for the Report. When I try to insert the results of those queries in text boxes in the Report, it just returns the results of the first row of the query.

Am I totally off on my approach to this problem?

Any insight would be greatly appreciated.

--Rachel

[June7]

How would you include two queries in the report RecordSource? A report can have only one data source. Possibly you need to use subreports. Or maybe set up report Grouping & Sorting features with summary calcs in group section header/footer.

[VictoriaAlbert]

I found instructions on how to include multiple queries in a report from here:
http://office.microsoft.com/en-us/ac...001055099.aspx

[June7]

As I said, a RecordSource can be only one data source. That can be a table or a query that joins tables and/or queries. As that article states, the tables and/or queries must be related. They must be able to be joined. If you have established relationships in the Relationship builder, when you pull tables into query designer the join link will be established. For queries you will probably have to manually set the joins in the query designer.

Does the RecordSource show a join of the tables/queries?

[VictoriaAlbert]

No, the queries are not joined. So, it sounds like I need to build the individual queries such that can be joined.

[June7]

Yes. Or if the data structure does not allow the joining, perhaps report/subreport arrangement will serve your situation.

[VictoriaAlbert]

I can get the query results to show up in the report - the problem is just that its only the first result (for example, the first output, when there may be 5 for that query). I'm wondering if there's a simple way to make the report show the entire results of the query, not just the first output.

[June7]

If you see the records in the query then they should show in the report. Did you size the Detail section to reduce blank space? However, if the join is not correct I don't see how the data could output properly. Would have to examine project to analyze this.