Calculating a total for column that has multiple conditions

[themartellekid]

I want to calculate totals for Phases from rows that contain multiple Phases.
Table is similar to this:
Fieldshase total_usage
A 10
AC 5
BC 6
C 4
Phase AC = half to A and half to C

My first question is how to approach the problem? I thought of using CASE
as in
select total_usage
case when Phase = "A" then totalA-totalA+total_usage
case when Phase = "AC" then split = total_usae/2, totalA=totalA+split,
totalC=totalC+split

I also considered IIF but that didn't seem right.

Any help will be appreciated. I am a novice.

 

[KARL DEWEY]

case when Phase = "AC" then split = total_usae/2, totalA=totalA+split,
totalC=totalC+split

Where does 'totalA' and 'totalC' come from?

 

[themartellekid]

I didn't really know how to approach the issue. I was thinking of writing a
vba function that would use CASE, and call the function from the select
statement. I did not make that clear. The variables sould be dim within the
function. Please tell me if this is a dead end approach. Would you use an
IIF? Or some other method?

Thanks.

 

[John Spencer]

If Phases are always designated by a single letter and multiple phases are
recorded using the single letters combined together with no extraneous spaces,
you might be able to use a query like the following especially if there is a
limited set.

SELECT
SUM(IIF(Phase like "*A*",Total_Usage/Len(Phase),Null))as A_Use
, SUM(IIF(Phase like "*B*",Total_Usage/Len(Phase),Null))as B_Use
, SUM(IIF(Phase like "*C*",Total_Usage/Len(Phase),Null))as C_Use
, SUM(IIF(Phase like "*D*",Total_Usage/Len(Phase),Null))as D_Use
FROM YourTable

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[KARL DEWEY]

I still do not understand where 'totalA' and 'totalC' comes from?

 

[themartellekid]

John,

That is exactly what I needed. I changed the sql slightly to make it work
with my specific data.

Thanks,
Monte

 

[themartellekid]

Karl,

I guess I didn't explain my question clearly. I knew that some how I needed
to keep track of a running total so envisioned using some kind of variables
but was not sure of the syntax.

Thank you for responding.

Monte