Report within a Report

[rch918]

I have been working on a report based on queries trying to, essentially, have multiple reports in one.

I'll try and explain it the best I can...

My report needs to have and/or needs to be based off of the following items:

• Assignee
• Mnemonic
• PracticeMHMDDept
• PracticeStatus
• ProviderMHMDDept
• ProviderStatus

The reasoning behind have separate Practice & Provider MHMDDept is because 1 practice(a group) can have 5 providers. Of those 5 providers, 4 have an MHMDDept of 'APCP' and 1 has an MHMDDept of 'APP'.

On the zipped database attached, qryAPPNumProvandPract AND qryAPCPNumProvPract (both based off qryPerMnemonic) are pulling the numbers I am wanting in a report. However, for the purpose of this database, we are not wanting multiple reports to have to go through. We are wanting to see the Assignee and the number of practices they have based on the status'/dept. When trying to combine the queries, the numbers are not calculating correctly and therefore i'm not able to combine them into one report. I have shown a table example below of what I am looking for in the final product:

ASSIGNEE	Total APCP Practices	Total APCP Providers	Total APP Practices	Total APP Providers
Amy	22	44	0	0
Angela	7	19	5	30
Bill	13	26	0	0
Devyn	11	27	8	21
Haley	5	17	0	0
Jackie	7	13	0	0
Janet	13	40	0	0
Javier	16	30	0	0
Lindsey	2	4	0	0
Michele	7	9	0	0
Rebecca	7	16	3	23

Column #2: Count Total APCP Practices if the PracticeMHMDDept = 'APCP' and the PracticeStatus = 'REA'.
Column #3: Total APCP Providers is the sum of countofproviders if ProviderMHMDDept = 'APCP' and the ProviderStatus = 'Active'
Column #4: Count the Total APP Practices if PracticeMHMDDept = 'APP' and the PracticeStatus = 'REA'


Column #5: Total APP Providers is the sum of countofproviders if ProviderMHMDDept = 'APP' and the ProviderStatus = 'Active'

I want this to be grouped like above per Assignee.

Can anyone PLEASE PLEASE PLEASE help get this information onto 1 report. I am now going in circles.
REATEST07.19.zip

[aytee111]

What is the name of the report and queries that pertain to this question?

[Preston]

What you exhibit is only one report based on multiple datasets. You need to use a query to gather the two sets of data together for the report. You would need a query that returns all assignees that have a record in either dataset (unique: one result per assignee), then you left join that query to each dataset and pull in the counts.

[ssanfu]

My $0.02 worth......

I would suggest stepping back and fixing the problems first.

There are special characters in field names (question marks), spaces in object names, look up FIELDs and (worst of all IMHO) multi-value fields (MFVs).
The tables "tblREAPractices" and "tblREAProviders" structures should be normalized; these two tables are designed like spreadsheets.


If you don't fix these items, you (as programmer) will always have difficulties with your dB.......

[rch918]

I think I have normalized my tables. Can you let me know if these tables will work for what I am trying to do?
Do you have any suggestions to replace the MFVs?
I know i still have spaces and special characters in there and I am working on removing those right now.

[ssanfu]

This is what you have



I don't know anything about your business, but from your dB/tables, I was suggestion something like this

[rch918]

Ssanfu,

Thanks for the response and suggestion. I figured I could split the tblPractices and tblProviders into more tables. The wizard did it but it kept choosing a date field as the PK.
I have been looking at your DB model design and I understand for the most part why and how you split the tables. The question i have is what is the sequence in tblMeasures and tblStatusText?

[ssanfu]

The question i have is what is the sequence in tblMeasures and tblStatusText?

It is my way of being able to control the sort order of the data. You can set the most used/best option at the top of the list in the combo box.
For example, for the ROW Source property of a combo/list box, you could use

SELECT MeasureID_PK, DeficientMeasures FROM tblMeasures

or
SELECT MeasureID_PK, DeficientMeasures FROM tblMeasures ORDER BY DeficientMeasures

or
SELECT MeasureID_PK, DeficientMeasures FROM tblMeasures ORDER BY Sequence

If you the last SQL statement, you can change the order the "deficientmeasures" field options are displayed. (I could be a little bit of a control freak... )
It is your dB... feel free to delete it.....