help with creating a calculated field

[gwog]

I use Access 97.

I have a table (tblJoinedData) which contains data I need to perform a complicated calculation on.

Basically the table contains data for the following (given in field names):

Item
Loc
Month (ie 1-12 numeric field)
Period (ie P1 - P12 text field)
PP
SR
AP
PP_AP (This number is a comparison for AP to PP - how much they planned to make vs what they actual made)

Here is some sample data:

Item Loc Month Period PP SR AP PP_AP
10001 CLDC 1 P1 100 100 50 -50
10001 CLDC 2 P2 500 400 300 -200
10001 CLDC 3 P3 100 100 400 300

What I need to do is creating a "rolling" sum of the PP_AP value.

In other words for the example above the new field (MTD) would look like this for each line above:

Item Loc Month Period PP SR AP PP_AP MTD
10001 CLDC 1 P1 100 100 50 -50 -50
10001 CLDC 2 P2 500 400 300 -200 -250
10001 CLDC 3 P3 100 100 400 300 50

So the value for each PP_AP gets added to the sum of MTD for the prior Month.

If anyone could help me figure out a way to accomplish this I would greatly appreciate it.

Thank-you in advance.

Lisa.

 

[PHV]

Something like this ?
SELECT A.Item, A.Loc, A.Month, A.Period, A.PP, A.SR, A.AP, A.PP_AP, Sum(B.A.PP_AP) AS MTD
FROM tblJoinedData AS A INNER JOIN tblJoinedData AS B ON (A.Item=B.Item) AND (A.Loc=B.Loc) AND (A.Month>=B.Month)
GROUP BY A.Item, A.Loc, A.Month, A.Period, A.PP, A.SR, A.AP, A.PP_AP
ORDER BY 1, 2, 3;

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

 

[gwog]

PH,

Thanks for the response.

Can you explain how you get Sum(B.A.PP_AP) AS MTDI thought if I just added the tblJoinedData to the query twice and used:

sum([tblJoinedData_1]![PP_AP])

But it would work but it won't run the query.

Here is the SQL of my query:

SELECT tblJoinedData.Item, tblJoinedData.Loc, tblJoinedData.MONTH, tblJoinedData.PERIOD, tblJoinedData.PP, tblJoinedData.SR, tblJoinedData.AP, tblJoinedData.PP_AP, Sum([tblJoinedData_1]![PP_AP]) AS MTD
FROM tblJoinedData INNER JOIN tblJoinedData AS tblJoinedData_1 ON (tblJoinedData.Item = tblJoinedData_1.Item) AND (tblJoinedData.Loc = tblJoinedData_1.Loc) AND (tblJoinedData.MONTH = tblJoinedData_1.MONTH);



Thanks.

Lisa.

 

[gwog]

PH,

Nevermind - I figured out what I was doing wrong.

Thank-You so much - your code was excellent!

Lisa.

 

[gwog]

I am getting results but not perfect.... it is adding data together but it adds month 10 to month 1 then adds month 11 then month 12 then month 2 then month 3 etc.

How do I get it to add months in order 1+2+3+4 etc?

Here is the code I am using:

SELECT A.month, A.PERIOD, A.Item, A.Location, A.Category, A.Capacity_Group, A.PLANNED, A.SCHED, A.PPVSSR, A.ACTUAL, A.PPVSAP, Sum(B.PPVSSR) AS MTD, Sum(B.PPVSAP) AS ATD, A.Category AS Cat, A.Capacity_Group AS CapacityGroup
FROM [A-3-COMBINED DATA] AS A INNER JOIN [A-3-COMBINED DATA] AS B ON (A.PERIOD >= B.PERIOD) AND (A.Item = B.Item) AND (A.Location = B.Location) AND (A.month >= B.month)
GROUP BY A.month, A.PERIOD, A.Item, A.Location, A.Category, A.Capacity_Group, A.PLANNED, A.SCHED, A.PPVSSR, A.ACTUAL, A.PPVSAP, A.Category, A.Capacity_Group
ORDER BY 1, 2, 3;


Thanks

 

[lespaul]

when you get the month information you have to format it to a two digit month in order for this to work properly.

leslie


Leslie