Need solution for calculations

[atisz]

Hi,

I haven't worked with Access for a while, now i'm working on a project and just can't handel with a calculation. I have somewhere the solution for my problem, I had use it other times, but now i just don't know where to find that sample database.

I have 2 tables: tblProjects and tblCosts

tblProjects
ProjectID
ProjectName
ProjectValue
...............

tblCosts
ProjectID
CostName
CostValue
................

What I want is to calculate the Benefit=ProjectValue-CostValue.

I know it is possible, in other cases I have used some union queries, sum calculation and I had my results very simply. But now, as I said before, can't find that piece of SQL.

Any solution is appreciated.

Thank you

 

[GaryPanic]

right on your form ahve a field called benefit

now after update on both of projectvalue and costvalue have the following (needs to be on both)

benefit = ProjectValue-CostValue

refresh

job done

in case you don't remmber - on the after update go to the ... and code then basically copy the code...

benefit = the name of the txt field on the form and Profject value and cost are the names of your data inthe table/form

 

[Jon K]

But now, ... , can't find that piece of SQL.

If the relationship between the two tables is one-to-many, try this SQL:-

SELECT tblProjects.ProjectID, tblProjects.ProjectName, First(tblProjects.ProjectValue) AS ProjectValue,
Sum(tblCosts.CostValue) AS CostValue,
[ProjectValue] - [CostValue] AS Benefit
FROM tblProjects INNER JOIN tblCosts ON tblProjects.ProjectID = tblCosts.ProjectID
GROUP BY tblProjects.ProjectID, tblProjects.ProjectName;


If their relationship is one-to-one, try this:-

SELECT tblProjects.ProjectID, tblProjects.ProjectName, tblProjects.ProjectValue,
tblCosts.CostName, tblCosts.CostValue,
tblProjects.ProjectValue - tblCosts.CostValue AS Benefit
FROM tblProjects INNER JOIN tblCosts ON tblProjects.ProjectID = tblCosts.ProjectID;
.

 

[atisz]

Yessssssssssss! It's working!

Thank you guys for the help.
I haven't mentioned very clearly for what I need theese calculations, but Jon K's solution is exactly what I need, and it's working great.

Thank you again
atisz