loop in query

[anil]

hi all
I am facing the wierd problem of cummulative addition.
the table looks like
[SampleID] [SampleDate] [ParameterTestMethods] [ParameterMethodCost]
[ParameterName] [ParameterTestCost] [ P_CoST]
10172 9/08/2006 MAJOR
$12.00 MAGNESIUM $3.00 $15.00
10172 9/08/2006 MAJOR
$12.00 SODIUM $3.00 $18.00
10172 9/08/2006 MAJOR
$12.00 CALCIUM $3.00 $21.00
10172 9/08/2006 MAJOR
$12.00 SULPHATE_ICPMS $3.00 $24.00
10172 9/08/2006 TRACE_PLUS
$24.00 ZINC_ICPMS $3.20 $27.20
10172 9/08/2006 TRACE_PLUS
$24.00 MANGANESE_ICPMS $3.20 $30.40
10172 9/08/2006 TRACE_PLUS
$24.00 MERCURY_ICPMS $3.20 $33.60
10172 9/08/2006 TRACE_PLUS
$24.00 NICKEL_ICPMS $3.20 $36.80
here the P_Cost is the sum of ParameterMethodCost and
parametertestingcost(12+3).The next parameterName in that same method
is sum of ParameterMethodCost and last 2 parameters(12+3+3).
as the method changes the loop startes again--(24+3.20) -----
(24+3.20+3.20+3.20+ ----)
can someone help me how to do this in query or if we need any function
..
Thanks
anil

 

[Jeff L]

How to do what? What is your question?

 

[anil]

sorry jeff
it is all messed up.
Actually i need to calculate P_Cost which which depends on the
ParameterTestMethod.
Suppose parameterTestMehod is Major and its cost is $12.00 and there
are 2 ParameterName (each with testing cost $ 3.00) then P_cost of
first parametername will become (12+3 =15) and cost of 2nd
ParameterName will become (15+3=18 or 12+3+3=18)

So i need to look at parameterTestMethod and then calculate the P_cost
as commulative addition.I will try the table again

[SampleId] [ParamMethod] [PMCost] [ParamName] [PNCost] [P_cost]
10172 Major 12 mag 3
? (12+3)
10172 Major 12 sod 3
? (12+3+3)
10172 Major 12 cal 3
? (12+3+3+3)
10172 Major 12 X 3
? (12+3+3+3+3)
10173 Trace_X 24 A 3.2
? (24+3.2)
10173 Trace_X 24 B 3.2
? (24+3.2+3.2)
and so on..........
So we need to calculate P_cost = ? , which is calculated as shown in
( ) .
can you help in that
Hope I made more clear this time
thanks
anil

 

[Jeff L]

Here's what I've got.
Make a module in access and paste this into it:

Public Function RunningSum(SampleId As Integer, PMCost As Double,
ParamName As String) As Double

RunningSum = DSum("PNCost", "Table1", "ParamName <= '" & ParamName & "'
And SampleID = " & SampleId) + PMCost

End Function

Change Table1 to your table name in the DSum statement above.

Now make a query that outputs [SampleId] [ParamMethod] [PMCost]
[ParamName] [PNCost]. You should not have a field in your table called
P_Cost because it is a calculated field. Sort the query by SampleId
and ParamName ascending. In a blank field of the query, put
PCost: RunningSum([SampleID],[PMCost],[ParamName])
Run the query.

Hope that helps!

 

[anil]

Thanks jeff
You r great.
it was wonderful
anil

Here's what I've got.
Make a module in access and paste this into it:

Public Function RunningSum(SampleId As Integer, PMCost As Double,
ParamName As String) As Double

RunningSum = DSum("PNCost", "Table1", "ParamName <= '" & ParamName & "'
And SampleID = " & SampleId) + PMCost

End Function

Change Table1 to your table name in the DSum statement above.

Now make a query that outputs [SampleId] [ParamMethod] [PMCost]
[ParamName] [PNCost]. You should not have a field in your table called
P_Cost because it is a calculated field. Sort the query by SampleId
and ParamName ascending. In a blank field of the query, put
PCost: RunningSum([SampleID],[PMCost],[ParamName])
Run the query.

Hope that helps!

sorry jeff
it is all messed up.
Actually i need to calculate P_Cost which which depends on the
ParameterTestMethod.
Suppose parameterTestMehod is Major and its cost is $12.00 and there
are 2 ParameterName (each with testing cost $ 3.00) then P_cost of
first parametername will become (12+3 =15) and cost of 2nd
ParameterName will become (15+3=18 or 12+3+3=18)

So i need to look at parameterTestMethod and then calculate the P_cost
as commulative addition.I will try the table again

[SampleId] [ParamMethod] [PMCost] [ParamName] [PNCost] [P_cost]
10172 Major 12 mag 3
? (12+3)
10172 Major 12 sod 3
? (12+3+3)
10172 Major 12 cal 3
? (12+3+3+3)
10172 Major 12 X 3
? (12+3+3+3+3)
10173 Trace_X 24 A 3.2
? (24+3.2)
10173 Trace_X 24 B 3.2
? (24+3.2+3.2)
and so on..........
So we need to calculate P_cost = ? , which is calculated as shown in
( ) .
can you help in that
Hope I made more clear this time
thanks
anil