MS-Access query/module function that will update records based on certain criteria
Experts:
In need some help with an query/module function that will update records (in MS-Access) based on certain criteria.
Please view attached XLS with some sample data. I included the three "cases" (scenario) in the call outs. Ultimately, I may have more than these 3 cases; however, for right now, achieving the update/conversion based on the 3 samples would be a great start.
Again, as a picture is worth a 1000 words, please see the "before" and "after" for the sample data. I'd like to accomplish the VBA in MS-Access.
Thank you,
EEH
Update-Query.xlsx
Update-Query.accdb
In need some help with an query/module function that will update records (in MS-Access) based on certain criteria.
Please view attached XLS with some sample data. I included the three "cases" (scenario) in the call outs. Ultimately, I may have more than these 3 cases; however, for right now, achieving the update/conversion based on the 3 samples would be a great start.
Again, as a picture is worth a 1000 words, please see the "before" and "after" for the sample data. I'd like to accomplish the VBA in MS-Access.
Thank you,
EEH
Update-Query.xlsx
Update-Query.accdb
You would sort the recordset on the five fields followed by date and time. You have to work two records at a time in a VBA loop so you can move backward and forward as you update records. What EXACTLY makes two records potential duplicates? Matching on 5 fields makes sense but the other logic you describe does not. Are you saying that for 2b you want to then match on FirstName, Age, Race, and Gender and update PayGrade? What PayGrade rules? What if there are more than two records? What if there are more than two variations? It looks like 2a is a red herring and is basically all other records.
So if you have two cases, 5 match and 4 match, you can try to do them in the same loop (use a simple 4 match query) or you can use two queries. The 5 match query is simple enough but the 4 match query needs to exclude any record in the 5 match query so it needs a left join to the 5 match query.
Or, maybe you want to stack these by doing the 4-match process first and updating those records and then doing the 5-match which might include records updated by the 4-match. I have no idea what makes sense because I can't figure out what problem you are trying to solve. It looks like you have some set of data without any personnel identifier and you are trying to figure out what transactions apply to what persons.
You posted this problem in another thread. What happened to that one? Did you get an answer and close it or did you abandon it? Please close the other thread if you haven't and add a link to this one.
So if you have two cases, 5 match and 4 match, you can try to do them in the same loop (use a simple 4 match query) or you can use two queries. The 5 match query is simple enough but the 4 match query needs to exclude any record in the 5 match query so it needs a left join to the 5 match query.
Or, maybe you want to stack these by doing the 4-match process first and updating those records and then doing the 5-match which might include records updated by the 4-match. I have no idea what makes sense because I can't figure out what problem you are trying to solve. It looks like you have some set of data without any personnel identifier and you are trying to figure out what transactions apply to what persons.
You posted this problem in another thread. What happened to that one? Did you get an answer and close it or did you abandon it? Please close the other thread if you haven't and add a link to this one.
Pat:
Thank you for chiming in... first things first... while some of the data may have been similar, the nature of this question is rather different (i.e., the othe question was focused on creating an ID number based on alike criteria). Anyhow, please allow me to clarify:
What EXACTLY makes two records potential duplicates?
- Absolutely a fair question... unfortunately, there's no simple answer. In most cases, if a record has to be updated, the majority of users perform the update function correctly. However, in some cases, a subset of users (due to lack of training or attention), create a new record instance for the same employee (not important as to what needs to be updated).
Allow me to recap:
1. If all demographics (Name, Age, Race, Gender, Paygrade) AND incident date are the same, I am confident all underlying records are the same. Thus, I want to update the incident time.
2. For records (ID = 7 and ID = 8), all demographics EXCEPT "Race" are the same. So, this could be either a "typo" (i.e., Michael is either Caucasian or Black). To ensure it's not a type, the Date can be used to further validate whether or not we're talking about the same person. In this case, ID=7 was created on 10-05 while ID=8 was created on 10-15. So, these are two distinct records and no change should occur.
3. Similar to the previous bullet, ID=8 and ID=9 are equal EXCEPT for "Age" and "PayGrade". Again, "Date" should be used to distinguish between records. In the event both were created on the 15th October, however, I would want to update one of the records.
4. For 2c, 4 out of 5 demographics are the same. Again, it could be that a user incorrectly added the paygrade. Given that date is the same as well, I want to update "paygrade" with the latest entry. So, in this case, let's go w/ the 2nd record (i.e., "E5).
Alternatively to running updates, if I had a query that outputs all "discrepancies) -- e.g., 15 discrepancies out of 2,000 records -- it certainly would be easier to manually update the records if necessary.
I was just hoping to automate this process vs. having to run various queries one after the after to identify/update records to be changed.
I hope the additional information provided more details about the process.
Thank you,
EEH
Thank you for chiming in... first things first... while some of the data may have been similar, the nature of this question is rather different (i.e., the othe question was focused on creating an ID number based on alike criteria). Anyhow, please allow me to clarify:
What EXACTLY makes two records potential duplicates?
- Absolutely a fair question... unfortunately, there's no simple answer. In most cases, if a record has to be updated, the majority of users perform the update function correctly. However, in some cases, a subset of users (due to lack of training or attention), create a new record instance for the same employee (not important as to what needs to be updated).
Allow me to recap:
1. If all demographics (Name, Age, Race, Gender, Paygrade) AND incident date are the same, I am confident all underlying records are the same. Thus, I want to update the incident time.
2. For records (ID = 7 and ID = 8), all demographics EXCEPT "Race" are the same. So, this could be either a "typo" (i.e., Michael is either Caucasian or Black). To ensure it's not a type, the Date can be used to further validate whether or not we're talking about the same person. In this case, ID=7 was created on 10-05 while ID=8 was created on 10-15. So, these are two distinct records and no change should occur.
3. Similar to the previous bullet, ID=8 and ID=9 are equal EXCEPT for "Age" and "PayGrade". Again, "Date" should be used to distinguish between records. In the event both were created on the 15th October, however, I would want to update one of the records.
4. For 2c, 4 out of 5 demographics are the same. Again, it could be that a user incorrectly added the paygrade. Given that date is the same as well, I want to update "paygrade" with the latest entry. So, in this case, let's go w/ the 2nd record (i.e., "E5).
Alternatively to running updates, if I had a query that outputs all "discrepancies) -- e.g., 15 discrepancies out of 2,000 records -- it certainly would be easier to manually update the records if necessary.
I was just hoping to automate this process vs. having to run various queries one after the after to identify/update records to be changed.
I hope the additional information provided more details about the process.
Thank you,
EEH
1. "Thus, I want to update the incident time." How can you be sure that there were not two incidents?
2, 3. People are smarter than computers about pattern matching. You must SPECIFICALLY describe what constitutes a match if you expect to automate this. Since ID=8 is participating in two potential matches, should rule 2 be applied first and then 1 and 3 applied or would that record never get to rule 3?
As I said, you can automate the 5 field matches. But you would need to create separate queries to do all possible 4 field matches and then all possible 3 field matches - and 2 or 1 which probably don't make much sense. That should be 4! (factorial) and 3! (factorial) queries all of which need to be joined at least to the 5 match query so you don't include them. But, you may want to exclude any record in any query if it was returned by any other query.
Doing this automatically requires working essentially with your eyes closed. You have to figure out the matching patterns you want to work with and ensure that any given record does not end up in multiple sets unless it makes sense to consider the same record multiple times.
You should probably fix the process that generates these sloppy records first to prevent future issues. Including the person's ID in the input would go a long way toward fixing the problem. That way the recordset would not include demographic data at all unless one of the fields was changed by the transaction. In that case if PayGrade was changed, the other four fields would be null.
2, 3. People are smarter than computers about pattern matching. You must SPECIFICALLY describe what constitutes a match if you expect to automate this. Since ID=8 is participating in two potential matches, should rule 2 be applied first and then 1 and 3 applied or would that record never get to rule 3?
As I said, you can automate the 5 field matches. But you would need to create separate queries to do all possible 4 field matches and then all possible 3 field matches - and 2 or 1 which probably don't make much sense. That should be 4! (factorial) and 3! (factorial) queries all of which need to be joined at least to the 5 match query so you don't include them. But, you may want to exclude any record in any query if it was returned by any other query.
Doing this automatically requires working essentially with your eyes closed. You have to figure out the matching patterns you want to work with and ensure that any given record does not end up in multiple sets unless it makes sense to consider the same record multiple times.
You should probably fix the process that generates these sloppy records first to prevent future issues. Including the person's ID in the input would go a long way toward fixing the problem. That way the recordset would not include demographic data at all unless one of the fields was changed by the transaction. In that case if PayGrade was changed, the other four fields would be null.
Pat:
Allow me to respond in bullet format:
- Yes, there may be multiple incidents; however, we want them all linked to a single record (vs. having several records of the same person).
- Also, in my actual data set, we have more than just 5 demographics to distinguish between individuals.
- I would think that rule #1 should be applied first. It would be great to have a methodology for checking 5 demographics and then updating the time for a 2nd or 3rd or nth match (assuming all days are the same). If the method works, I will (afterwards) add an additional 2 or 3 criteria (demographics) to ensure that all matching records (of the same day) have the time updated correctly.
- I am okay with running multiple passes (queries) to check for the validation. Not sure how to approach that topic though.
For right now, I'd be thrilled to have a process that ends up converting data listed in cell range A3:H14 into data shown in A46:57.
Thank you for your continued assistance.
EEH
Allow me to respond in bullet format:
- Yes, there may be multiple incidents; however, we want them all linked to a single record (vs. having several records of the same person).
- Also, in my actual data set, we have more than just 5 demographics to distinguish between individuals.
- I would think that rule #1 should be applied first. It would be great to have a methodology for checking 5 demographics and then updating the time for a 2nd or 3rd or nth match (assuming all days are the same). If the method works, I will (afterwards) add an additional 2 or 3 criteria (demographics) to ensure that all matching records (of the same day) have the time updated correctly.
- I am okay with running multiple passes (queries) to check for the validation. Not sure how to approach that topic though.
For right now, I'd be thrilled to have a process that ends up converting data listed in cell range A3:H14 into data shown in A46:57.
Thank you for your continued assistance.
EEH
Pat:
For starters, I'd be thrilled to have the first case taken care of... that is, I'd be thrilled if I had a way to update/synchronize the times where date, age, gender, race, and paygrade equals.
- So, based on those five criteria, I would like to take the "1st time" and update other times to match first time.
- If I had 2 matching records and the 1st record's time is empty, I would like to update first record with 2nd record's time.
- Similarly, if I had 3 matching records and any two of them don't have a time, I would like to update them w/ the record which has a time.
- Finally, if none of the matching records have a time, no change needs to occur.
Ultimately, my goal is to be able to use a "GroupBy" query so that I will not inflate my record count once I insert the [IncidentTime] field into the query.
Thank you,
EEH
For starters, I'd be thrilled to have the first case taken care of... that is, I'd be thrilled if I had a way to update/synchronize the times where date, age, gender, race, and paygrade equals.
- So, based on those five criteria, I would like to take the "1st time" and update other times to match first time.
- If I had 2 matching records and the 1st record's time is empty, I would like to update first record with 2nd record's time.
- Similarly, if I had 3 matching records and any two of them don't have a time, I would like to update them w/ the record which has a time.
- Finally, if none of the matching records have a time, no change needs to occur.
Ultimately, my goal is to be able to use a "GroupBy" query so that I will not inflate my record count once I insert the [IncidentTime] field into the query.
Thank you,
EEH
Premium Content
You need an Expert Office subscription to comment.Start Free Trial