Rank registration events in asc time order (1 Viewer)

I have a table "Tag Data" with three fields named – tag_id, reg_time and reader_no.

I add 4 more fields to workout - reg_time_occurrence, reader_no_occurrence, previous_reader_no, next_reader_no (see attached text file, where I have these fields manually completed these fields for couple of tag_ids to show what I am trying to achieve). Can someone please help me with solution to update these four fields automatically.

reg_time_occurrence – in this field I need to calculate the occurrence of registeration time for each tag_id in ascending time order, so for earliest reg time this will updated to 1, then next time will be 2 and so on..for next tag_id this field will start with 1 again.

reader_no_occurrence – in this field I need to calculate the occurrence of each reader no for each tag_id in ascending time order, so earliest registration time on reader number 1 will be 1, next registration time on reader 1 will be 2 and next registration time on reader 4 will be 1 again.

previous_reader_no – for each tag_id I need to know at what reader_no if any, the previous registration took place.

next_reader_no – for each tag_id I need to know at what reader no if any, the next registration took place.

I am not using VBA modules in my access database; all data processing is done with queries. I think solution to my above problem can only be in a vb module, so please let me know if you can, in a bit more detail, how will I include a new module with this code and what will I need to do to trigger this code to populate above four fields.

I have resubmitted this problem with few amendments, hope to get response from someone this time.

Thanks in advance.
GR

This will take care of the first two columns you are after.

SELECT t.tag_id, t.reg_time, t.reader_no,
(SELECT Count(*)+1 FROM tblTag t1 WHERE t1.tag_id = t.tag_id and t1.reg_time < t.reg_time) AS reg_time_occurrence,
(SELECT Count(*)+1 FROM tblTag t1 WHERE t1.reader_no = t.reader_no and t1.reg_time < t.reg_time) AS reader_no_occurrence
FROM tblTag AS t
ORDER BY t.reg_time;


I'm not sure how to handle to next two. Why do you need the previous and next reader to be shown again(as they are techically already being shown)?

Thank you for your help with this.
I will try your query on Monday for first two columns and see how it goes. The reason I want to know the previous and next reader number is that I want to establish the flow or path followed by a tag_id. In brief, tag_id is an RFID transponder letter travelling through a very large mail processing centre. The centre has many readers located at key point of the building. Each reader will record the time as transponder letter passes through its range. Sometimes if first registration is on reader number 18 then on 24 and then on 26 and again on 18, this means an item has gone into a loop and failed to leave the building on time. This helps to pickup patterns and wrong practices. I think knowing previous and next reader number should be ok to start with but I will in future need to know previous 4 or 5 and next 4 or 5 readers. I suppose once I know how to pick up previous and next, I should be able to workout the others.
Hope yourself or someone else will come forward with the solution.
Thanks again.
GR

Thanks for your help, your query has given me the first two coulmns I was looking for. I only had to add the bit in bold. Thanks again. GR

SELECT t.tag_id, t.reg_time, t.reader_no,
(SELECT Count(*)+1 FROM tblTag t1 WHERE t1.tag_id = t.tag_id and t1.reg_time < t.reg_time) AS reg_time_occurrence,
(SELECT Count(*)+1 FROM tblTag t1 WHERE t1.tag_id = t.tag_id AND t1.reader_no = t.reader_no and t1.reg_time < t.reg_time) AS reader_no_occurrence
FROM tblTag AS t
ORDER BY t.reg_time;