Help Please! Calculation in query

[daltonmillion]

I am wanting to add a Field named [Token] in a query that takes the hourly sum of another field named [Amount]



I have amount time stamped at the user's entry, and I am looking to do a If Statement in [Token] that if the hourly sum of [Amount] is zero then it adds a 1 to [Token] and a timestamp in the corresponding hour.

Ex.

From 9:00am - 9:59am (hour 9)
Sum([Amount]) = 0
therefore,
[Token] = 1 @ hour 9

Essentially i'm just trying to account for every hour, so if anyone has any ideas im open to suggestions. I'm just very novice at this and know next to no syntax for Access.

[ranman256]

I use 2 queries,
Q1 :select sum([amount]) as Total, [name], [hour]

Q2: select * , iif([total]=0,1,[total]) as Token from Q1

[pbaldy]

I was thinking something like this (untested):

SELECT Hour(TimeField) As TheHour, Sum([Amount]) As TheAmount, IIf(Sum([Amount]) = 0, 1, 0) As TheToken
FROM TableName
GROUP BY Hour(TimeField)

[daltonmillion]

I use 2 queries,
Q1 :select sum([amount]) as Total, [name], [hour]

Q2: select * , iif([total]=0,1,[total]) as Token from Q1

This worked! Thank you very much