Reference a field from another report 1

[mytaurus2000]

I have a report called rptPrimaryPurpose. On the report I have grouped by topics (Education,employment,communityliving)

For each topic I have counted the purpose and displayed.

In the grouping's footer I have a Total, so the report reads:

Education
Like 1
Dislike 4
Don't Know 3

Total 8


I need to calculate performance measure so I have a query that selects on Likes and Dislikes adds them. I want to reference this field from my query and place in current report so it would read as follows

Education
Like 1
Dislike 4
Don't Know 3

Total 8
Performance 5

How can I reference Performance from my query on the current report, I receive an error when I try

The report control source is qryPrimaryPurpose, but Performance is from qryPurposePerformance.

 

[Remou]

It may be possible to DlookUp the query or to use a subreport to show the query. It is difficult to say from the information you have supplied.

 

[mytaurus2000]

This statement selects only Like and Dislike:

SELECT COUNt([tblPerformanceOutcome.PerformanceOutcome]) AS PerfCount, [tblDeviceDemonstration].[BenefitID]
FROM tblPerformanceOutcome INNER JOIN tblDeviceDemonstration ON [tblPerformanceOutcome].[PerformanceOutcomeID]=[tblDeviceDemonstration].[PerformanceOutcomeID]
WHERE ((([tblPerformanceOutcome].[PerformanceOutcomeID])=1)) Or ((([tblPerformanceOutcome].[PerformanceOutcomeID])=2))
GROUP BY [tblDeviceDemonstration].[BenefitID];

This statement selects all three like, dislike, and don't know and is the control source for my report

SELECT Count([tblDeviceDemonstration.BenefitID]) AS BenefitCount, [tblDeviceDemonstration].[PerformanceOutcomeID], [tblDeviceDemonstration].[BenefitID]
FROM tblDeviceDemonstration
GROUP BY [tblDeviceDemonstration].[PerformanceOutcomeID], [tblDeviceDemonstration].[BenefitID];


The report I have currently shows all three and sums them and I have called that PerformanceTotal, which I want. But I also want the first queries PerfCount to display on my report. I have to calculate performance by PerfCount/PerformanceTotal.

I hope this makes a little more sense.

 

[Remou]

You could join the two queries to create your report, or perhaps use DlookUp or DSum:

[tt]=DlookUp("PerfCount","Name of Query","BenefitID=" & [BenefitID])[/tt]

[tt]=DSum("PerfCount","Name of Query","BenefitID=" & [BenefitID])[/tt]

 

[mytaurus2000]

When I join the two queries, the numbers arent correct.

Do you mean join them as one query or do you mean in the reports section in the controlsource section?

 

[mytaurus2000]

I tried using this to decide, but doesn't return right numbers either.

SELECT Count(IIf([tblDeviceDemonstration.BenefitID] IN (1,2),[tblDeviceDemonstration.BenefitID], Null)) AS BenefitCount, [tblDeviceDemonstration].[PerformanceOutcomeID], [tblDeviceDemonstration].[BenefitID]
FROM tblDeviceDemonstration
GROUP BY [tblDeviceDemonstration].[PerformanceOutcomeID], [tblDeviceDemonstration].[BenefitID];

 

[Remou]

By join the queries, I mean open both of then in the query design window and drag the BenefitID field from one to the other to create a join. The fields you want can then be selected from each of the queries.

 

[mytaurus2000]

Thanks Remou for all your help. Works perfectly