Calculating across tables (1 Viewer)

[Rusty]

Hey Guys,

It’s been a while but I have been visiting from time to time. Just been busy with a load of web design.

Anyhoo, I have been asked to create a report on total revenue generation for projects and have taken it as far as I can, and now need your help.

I have two queries “qryCommTrialsRevenue_PatientEvents” and “qryCommTrialsRevenue_StudyCharges” that select income via two cost codes and between two dates.

Two further separate queries calculate the total for each project. That’s as far as I can take it at the moment and now I cannot see the wood for the trees…I need to calculate the total for each project ACROSS the tables. Can this be done successfully? Every time I try it the numbers don’t add up.

Cheers,

Rusty

 

[Murli]

What do you mean by Across the tables?

Please explain...

 

[RV]

Rusty,

put this in a new query and see if it's what you're after:

SELECT Nz(qryCommTrialsRevenue_PatientEvents_TOTAL.ProjectReference,qryCommTrialsRevenue_StudyCharges_TOTAL.ProjectReference) AS Expr1, 
CCur(Nz(qryCommTrialsRevenue_PatientEvents_TOTAL.SumOfAmount,0) + Nz(qryCommTrialsRevenue_StudyCharges_TOTAL.SumOfCost,0)) AS Expr2
FROM qryCommTrialsRevenue_PatientEvents_TOTAL RIGHT JOIN qryCommTrialsRevenue_StudyCharges_TOTAL ON qryCommTrialsRevenue_PatientEvents_TOTAL.ProjectReference = qryCommTrialsRevenue_StudyCharges_TOTAL.ProjectReference

RV