Solved

Combine partial duplicate redords into 1 complete record?

Posted on 2004-04-12
3
411 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 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

20 Experts available now in Live!

Get 1:1 Help Now