Solved

Combine partial duplicate redords into 1 complete record?

Posted on 2004-04-12
3
422 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
0
Comment
Question by:ColleenCallahan
  • 2
3 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 250 total points
ID: 10808547
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
 

Author Comment

by:ColleenCallahan
ID: 10814622
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10814719
I know the feeling :-)

Success !

Nic;o)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now