Combine partial duplicate redords into 1 complete record?

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
ColleenCallahanAsked:
Who is Participating?
 
nico5038Connect With a Mentor Commented:
Just create a GroupBy query for the "main field(s)" like CustNum.
Now use e.g. the MAX function for the other fields to get the "empty fields" and the shorter ones to be filled.

Getting the idea ?

Nic;o)
0
 
ColleenCallahanAuthor 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!
0
 
nico5038Commented:
I know the feeling :-)

Success !

Nic;o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.