MS-Access query/module function that will update records based on certain criteria

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

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.

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

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

Pat - I have posted a new thread that focuses more on identifying the records vs. (automatically updating them).

Cheers,
EEH