Conditional Sum query or module

[striker83716]

I am trying to create a crosstab query that will sum hours between dates, based on system failures. In the table below, the first three columns are from the table, the last two columns, is the data as I would like it to appear.
I am posting here, because I believe I will need some VBA for this. Thanks in advance!

DateofObservance hours idsys System1 system2
2/13/2008 72 72 72
2/14/2008 72 144 144
2/15/2008 72 1 0 216
2/16/2008 72 72 288
2/17/2008 72 3 144 0
2/18/2008 72 216 72
2/19/2008 72 288 144

 

[SeeThru]

How are the last two columns calculated? They must be based on the existing data, but I can't see where the numbers are coming from.

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[Remou]

How about:

SELECT t.DateofObservance, 
   t.hours, 
   t.idsys, 
   GetSys1([Hours],[idsys]) AS Ans1, 
   GetSys2([Hours],[idsys]) AS Ans2
FROM test t
ORDER BY t.DateofObservance;

Option Compare Database

Dim sys1 As Long
Dim sys2 As Long

Function GetSys1(hrs, ids)

If Nz(ids, 0) <> 1 Then
    sys1 = sys1 + hrs
Else
    sys1 = 0
End If

GetSys1 = sys1

End Function

Function GetSys2(hrs, ids)

If Nz(ids, 0) <> 3 Then
    sys2 = sys2 + hrs
Else
    sys2 = 0
End If

GetSys2 = sys2

End Function