How to merge customer records 2

[sawilliams]

Hello:

I need to import customer records into a database. I am given rows of data that looks like this:

PREFIX;FNAME;MNAME;LNAME;ADDR1;ADDR2;CITY;STATE;ZIP;PHONE;EMAIL
Mr.;John;M.;Smith;123 Main Street;Apt.4;Anytown;PA;18331;6106812323;
Mr.;John;M.;Smith;123 Main Street;;Anytown;PA;18331;;jsmith@aol.com
;John;M.;Smith;123 Main Street;Apt.4;Anytown;PA;18331;6106812323;jsmith@aol.com​

With Access I can identify the dupes by FNAME + LNAME + ADDR1 but the additional fields vary as to whether there are values or not. Is there a way to "merge" these records to take a value over a null? In other words, if record #1 has a phone number and record #2 does not I want to keep the phone number but, at the same time, if record #1 has an email address and record #2 does not, I want to keep the email address.

The real life situaion is ticket buyers who have multiple orders and with one order we might capture their email address, but with a subsequent order we may not. I have tens of thousands of rows of data to sift thru so it would be great if there is an easy solution out there. In advance, thanks.

 

[dhookom]

You can create a query with only a single line per combination of FNAME, LNAME, and ADDR1.

SELECT Max(PREFIX) as ThePrefix, FNAME, Max(MNAME) as TheMName, LNAME, ADDR1, Max(ADDR2) as TheAddr2,...etc...
FROM SomeTable
GROUP BY FNAME, LNAME, ADDR1

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

SELECT Max(PREFIX) AS thePREFIX
,FNAME,MNAME,LNAME,ADDR1
,Max(ADDR2) AS theADDR2
,Max(CITY) AS theCITY
,Max(STATE) AS theSTATE
,Max(ZIP) AS theZIP
,Max(PHONE) AS thePHONE
,Max(EMAIL) AS theEMAIL
FROM [i]yourTable[/i]
GROUP BY FNAME,MNAME,LNAME,ADDR1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Damn, Duane beat me of this !

 

[sawilliams]

Guys:

Thanks for this -- sooo much. I can't believe your solution(s) were so straightforward and easy. Much appreciated!