iif referring to previous record

[=Roman]

Hi

I have a simple table a series of times (in # mins from midnight Jan 1st), and categories

time cat
1 a
10 r
12 f
25 h
100 reset
123 h
140 s

And I want to put a 'block number' that adds one each time it gets to 'reset', i.e. giving

1 a 1
10 r 1
12 f 1
25 h 1
100 reset 2
123 h 2
140 s 2



in excel this would be quite simple, with an if(cell 1 up="Reset",cell above+1,cell above), but I have 31m records, so don't want to have to export and reimport this bit by bit if possible

However, I've not got much of an idea in how to do this, or even if it can be done, using an iif in access?

[CJ_London]

assuming your 'time' field is a long (and by the way, time is a reserved word, so change it to something else), you don't need to store the block number, just calculate when required. Also ensure both time and cat fields are indexed, otherwise the process will be unacceptably slow.

I'm also assuming that time starts from 0 and continues to increment. In a query put the following in a new column

BlockNo: (SELECT count(*) FROM myTable AS T WHERE cat='reset' AND time<=myTable.time)+1

change names to suit

The excel option would be simple if a) you could list 31m records in a worksheet (limit is 1m) and b) it is sorted in the right order in the first place

[=Roman]

Thanks for the reply - that sounds like it makes sense, but I can't seem to get it to work (syntax error).

Here's a an extract of the table with the actual names for reference - and it's OFF for Reset. I don't think it needs the time element to be referenced, as it's just counting on the Network_From field - everything is sorted in the correct order, (i.e. by household and person, then by time).


BlockNo: (SELECT count(*) FROM [Tbl_Final_Switching] AS T WHERE [Tbl_Final_Switching]![Network_From] ='OFF')+1

[CJ_London]

Access is not a 'big excel' it is a completely different beast

1. you havent used the same syntax as I provided - you use . not !
2. you are missing some of the criteria

I don't think it needs the time element to be referenced, as it's just counting on the Network_From field - everything is sorted in the correct order, (i.e. by household and person, then by time).

no it's not - interpret the code, it is counting where the switch time is less than the record switchtime
3. you are incorrectly referring to the main table not the aliased table. Try

BlockNo: (SELECT count(*) FROM Tbl_Final_Switching AS T WHERE Network_From ='OFF' AND Switch_Time<=Tbl_Final_Switching.Switch_Time)+1

the sql to your query should look something like

SELECT *, (SELECT count(*) FROM Tbl_Final_Switching AS T WHERE Network_From ='OFF' AND Switch_Time<=Tbl_Final_Switching.Switch_Time)+1 AS BlockNo
FROM Tbl_Final_Switching
ORDER BY Switch_Time

Note for the purposes of calculating the blockno, you do not need to sort/order the table except for checking purposes that you are getting the correct result or for reporting purposes if required.