Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...I think the forum is a great idea, especially for those of us in consulting engineering. Keep up the good work!..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Other topics Forum

How to merge customer records Helpful Member!(2) 

thread181-1687527
Read More Threads Like This One
sawilliams (TechnicalUser)
5 Jul 12 16:29
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.

ponder
Helpful Member!  dhookom (Programmer)
5 Jul 12 17:14
You can create a query with only a single line per combination of FNAME, LNAME, and ADDR1.

CODE --> SQL

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

Helpful Member!  PHV (MIS)
5 Jul 12 17:18
What about this ?

CODE --> SQL

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 yourTable
GROUP BY FNAME,MNAME,LNAME,ADDR1

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

PHV (MIS)
5 Jul 12 17:19
Damn, Duane beat me of this !
sawilliams (TechnicalUser)
6 Jul 12 8:32
Guys:

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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Other topics

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close