We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Combine partial duplicate redords into 1 complete record?

ColleenCallahan
on
Medium Priority
502 Views
Last Modified: 2008-02-01
Hi all and thanks in advance for your time!

I have semi-duplicate records in a db, how can I write a query or whatever to "Aggregate" the 2 into 1 record?:

CustNum   Name                                                 Address               City                  State     Zip        Phone                  TermDate    CreditLimit
L50077   THE MONEY STORE AUTO FINANCE INC   P O BOX 161179                           CA                    (xxx) xxx-xxxx                          0
L50077   THE MONEY STORE AUTO FIN                 P O BOX 161179   SACRAMENTO   CA       95816                                02/26/96

In the above example I want to end up with the following "aggregate" record-
CustNum   Name                                                 Address               City                  State     Zip        Phone                  TermDate    CreditLimit
L50077   THE MONEY STORE AUTO FINANCE INC   P O BOX 161179   SACRAMENTO   CA        95816    (xxx) xxx-xxxx       02/26/96     0

I have an excel sheet from the customers old data that we got to the above(duplicated) table in Access.  I tried doing an aggregate function in Excel but there's no way to tell it to..."ignore blank fields in one record and take the 2nd record's info instead"

Can anyone help me on this please?

Thanks,
Dave
Comment
Watch Question

Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thanks that worked.  I did MAX(field1),.....MAX(fieldNth) in the select statment along with GROUP BY CUSTNUM and it rolled my duplicate partials into 1 coherent(correct) record.

Btw, I am doing a data converstion and would so love to SLAP the creator of the flatfile db Im getting the data from.  Apparently, they never heard of indexes and normalization :)

Thanks again!!!

Accepted Answer!

Commented:
I know the feeling :-)

Success !

Nic;o)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.