Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Combine partial duplicate redords into 1 complete record?

Posted on 2004-04-12
3
Medium Priority
?
467 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 1000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

721 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