Hi Folks,
Wondering if someone can help me with the necessary structure and/or keywords to get this rolling. Assume I have a base table in access, the table is sorted in a particular fashion so that logically you can step thru the records comparing the current record with the previous record to determine what you need to. How would I create a vba module that will load this base table, step thru the records in the sorted method comparing the current record with the previous so that if a certain criteria is found/met it records that in either another table or can append/update a field in the existing table? For example: if I have a 1000 row table in access named [1000RowTable] that contains a normalized representation of UserID, ServiceDate, ServiceProcedure and that table is sorted in ascending order for those fields ... I would want to load that table into vba and have vba go thru the sorted table row by row comparing the current UserID with the previous UserID, if the Current UserID matches the Previous UserID then perhaps I would want a 'Yes' in a new table along with the fields that were used in the compare and if 'No' then perhaps execute some other code. Any help in terms of formatting or VBA keywords/actions that I would need to use would be awesome. Obviously when the last record, I would want to properly close out of the recordset. I am just trying to figure out how to load a recordset in terms of an access table and how to hold a record whilst comparing it to a previous record in a sorted dataset and executing code if certain criteria match or not and then closing up afterwards. I have cheated and used excel to help with a bunch of stuff when I needed to compare current with previous but would like to learn how to do something similar with VBA.
Thank you,
Joe
Wondering if someone can help me with the necessary structure and/or keywords to get this rolling. Assume I have a base table in access, the table is sorted in a particular fashion so that logically you can step thru the records comparing the current record with the previous record to determine what you need to. How would I create a vba module that will load this base table, step thru the records in the sorted method comparing the current record with the previous so that if a certain criteria is found/met it records that in either another table or can append/update a field in the existing table? For example: if I have a 1000 row table in access named [1000RowTable] that contains a normalized representation of UserID, ServiceDate, ServiceProcedure and that table is sorted in ascending order for those fields ... I would want to load that table into vba and have vba go thru the sorted table row by row comparing the current UserID with the previous UserID, if the Current UserID matches the Previous UserID then perhaps I would want a 'Yes' in a new table along with the fields that were used in the compare and if 'No' then perhaps execute some other code. Any help in terms of formatting or VBA keywords/actions that I would need to use would be awesome. Obviously when the last record, I would want to properly close out of the recordset. I am just trying to figure out how to load a recordset in terms of an access table and how to hold a record whilst comparing it to a previous record in a sorted dataset and executing code if certain criteria match or not and then closing up afterwards. I have cheated and used excel to help with a bunch of stuff when I needed to compare current with previous but would like to learn how to do something similar with VBA.
Thank you,
Joe