performing calculations through a module 1

[greekpatriott]

Hello, Happy Christmass to everyone.
I have a question of using a module function in a query:

Performance Ratio:
E(t): Earned man hours at time t
A(t): Actual Man hours expended at time t
E(t-1): Earned Mhrs at time t-1 (previous month)
A(t-1): Actual Man hours expended at time t-1

If I have a query showing the months and the corresponding E(t) and A(t). How can I find the corresponding E(t-1) and A(t-1) so that I perform this calculation:

ratio = (E(t)-E(t-1))/(A(t)-A(t-1))

example
Month(mm/dd/yyyy) E(t) A(t) E(t-1) A(t-1)
1/01/2004 50 60 0 0
02/01/2004 70 65 50 60
03/01/2004 60 50 70 65

Cheers

 

[greekpatriott]

Ok I will try to simplify the question
How can I have a function (field E(t-1)) in a query that instead of reading the value E(t) at the corresponding month, it will read the previous value (so the value of previous month), else this value will be 0. Cheers

Example
month E(t) E(t-1): (public function)
1/01/2004 100 0
02/01/2004 70 100
03/01/2004 200 70

 

[greekpatriott]

Thus another difficult question.

 

[PHV]

If I have a query showing the months and the corresponding E(t) and A(t)
Can you please post the SQL code of this query ?
The basic idea is to do a self join like this:
FROM yourQuery X LEFT JOIN yourQuery Y
ON X.Month = DateAdd('m',1,Y.Month)

The X fields for t and the Y fields for t-1.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[greekpatriott]

Thanks PHV.
Actual Spread is A(t)
Earned Value Spread is E(t)

Here is the code
SELECT [Actual Spread].Month, [Actual Spread].[Actual Mhrs], [Earned Value Spread].[Earned Mhrs], ([Earned Value Spread].[Earned Mhrs]/[Actual Spread].[Actual Mhrs]) AS CPR
FROM [Actual Spread] LEFT JOIN [Earned Value Spread] ON [Actual Spread].Month = [Earned Value Spread].Month;

 

[greekpatriott]

made a mistake
Actual Mhrs is A(t)
Earned Mhrs is E(t)

 

[PHV]

Say your query is named qryGetCPR.
SELECT X.Month, X.[Actual Mhrs] AS CurrentA, X.[Earned Mhrs] As CurrentE, Y.[Actual Mhrs] AS PreviousA, Y.[Earned Mhrs] As PreviousE
FROM qryGetCPR X LEFT JOIN qryGetCPR Y ON X.Month = DateAdd('m',1,Y.Month)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[greekpatriott]

Hm excelent. Here are the steps as you suggested. I made these modification on the main query using the dateadd function as you suggested and then a datediff function to go back to the last month date. Then I made another query and doing a self join. I connected the months and then added the fields. Here are the two sql of the queries.

SELECT [Actual Spread].Month, ([Earned Value Spread].[Earned Mhrs]/[Actual Spread].[Actual Mhrs]) AS CPR, [Actual Spread].[Actual Mhrs], [Earned Value Spread].[Earned Mhrs], DateAdd('m',1,[actual spread].[month]) AS x, DateSerial(Year([x]),1+Month([x]),0) AS PreviousMonth
FROM [Actual Spread] LEFT JOIN [Earned Value Spread] ON [Actual Spread].Month = [Earned Value Spread].Month;

SELECT [CPR Cumulative Q].Month, [CPR Cumulative Q].[Actual Mhrs], [CPR Cumulative Q].[Earned Mhrs], [CPR Cumulative Q_1].[Actual Mhrs], [CPR Cumulative Q_1].[Earned Mhrs]
FROM [CPR Cumulative Q] LEFT JOIN [CPR Cumulative Q] AS [CPR Cumulative Q_1] ON [CPR Cumulative Q].Month = [CPR Cumulative Q_1].PreviousMonth;

 

[greekpatriott]

dateserial not datediff to go back to the last month date. (mistake)

 

[greekpatriott]

I forgot to thank you, for this excellent guidance.
Cheers Panos