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
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