Access Gurus - got a minute?

Factual Questions
1

I have three different client lists that need to be combined and deduped. The deduping I can handle… but shmooshing them together is tougher.

Let’s say the file is as follows:

NAME    #     LTR
Bob    10  
Bob                A
Jim                B
Jim    15
Sara   20           C
Sara   20           C

See the problem?

I need to combine the information in each of Bob and Jim’s records then discard the dupes. Sara’s records are totally alike, so I can just discard one.

Obviously my records each contain more data than what you see here, but I am going to assume that if the person’s name and company are the same, they are the same person. So, I’ve created a unique ID for each record containing a combination of company name and last name.

All the other data needs to be “shmooshed.”

Any ideas?

2

If it were me, I would insert one client list in a new table. Then I would loop through the other two recordsets checking to see if a record for that “person” (whatever unique field(s) identify an individual) exists. If it doesn’t, insert the data. If it does, put the non-null fields in the new record in an UPDATE query. This will leave the original record in place, but add in the fields from the new record.

This solution would take some coding, but it’s all pretty straightforward SQL.

3

I am NOT a guru, so this will probably strike the more educated as quite inelegant. However, when I’ve been faced with a similar situation, I’ve used a workaround that should suffice. Use this in case no one else shows up with anything better.

What about using a make table query with IIFs in it? I don’t have access to Access at the moment, so I can’t be sure of the exact syntax, but you could try the following. (also note that I’m not sure where the three tables are coming from. From your example it looks like you have just two tables. This should work for two, and only take a bit more nesting to get three)

CombName:iif(table1.name is not null, table1.name,table2.name)
CombNumber:iif(table1.number is not null, table1.number, table2.number)
And so on…

This is pretty straightforward. If both tables hold identical names, than CombName will always choose the name from the first table. I am assuming, by the way, that both tables have a key field that equates records. When you get to the second field, CombNumber, it looks to see if there is a record in table one. If so, it uses that and goes on to the next field. If table1’s number field is empty (null) than the iif is false, and it uses the value from table two. If you have more tables, just add another iif and nest it after the first.

You can also use this to check for mismatched records. If you have a number in both tables the iif statement as is will just use the first one it finds. But if the two numbers are different, and you want to be made aware of that, just place another nested iif inside with a *table1.field <> table2.field, table1.field, “Hey, pay attention to me. I have an mismatched duplicate” * Of course, the flag you use can be subtler, like table1.field & “A”, so all you have to do to see discrepant records is run a select query for As in that field.

Anyway, I hope that this gives a small modicum of hope.

4

Create a new table using the Make Table Query. In that query make sure the only field is the names. Set the ‘Unique Values’ property of the query to YES. This will create a table with only the names and it will contain no duplicate names. From there you should be able to create two update querys, one to pull the # and the other to pull the Values from the original table to the new table.

5

Just re-read the OP - the Make Table Query should have both the name and the company.

NP:Obituary - Back From The Dead