combine matching addresses on multiple lines onto one line

**patjones** · Apr 26 '10, 07:08 PM

I think what you want to do in the long run is take a look at restructuring the tables so that data is not duplicated to begin with...in other words normalize the table structure. One way that you could do this would be to have a table that stores each address just once with an address ID, and a customer table that stores just customers with their corresponding address ID. You can then join the two together on the address ID, which should make writing the SQL easier.

Pat

**NeoPa** · Apr 27 '10, 04:23 PM

ZeppHead's point is well made and absolutely worth following. However, there is still the same fundamental issue remaining as the names would need to be concatenated to do what you require. Combining Rows-Opposite of Union may help with that. Bear in mind that your request isn't simply concatenationg the entries, as the Mr/Mrs/Miss/etc data is handled differently (You haven't explained exactly how). You'll need to handle that logic yourself.

For info on database design you could do a lot worse than look up Normalisation and Table structures.

**ADezii** · Apr 27 '10, 05:12 PM

Originally posted by brat33

I am trying to modify the following code from showing all customer names on each line to combining customers with same address onto one line, while leaving the customers who are not duplicating address alone.

Code:

SELECT Customers.Salutation, Customers.[First Name], Customers.[Last Name], Customers.Title, Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Customers INNER JOIN (Trips INNER JOIN [Sales & Payments] ON Trips.[Trip Number] = [Sales & Payments].[Trip Number]) ON Customers.[Customer Number] = [Sales & Payments].[Customer Number]
WHERE (((Customers.[Duplicate Address])=False) AND (([Sales & Payments].[Trip Number])=[Enter Trip Number]) AND ((Customers.Deceased)=False) AND (([Sales & Payments].Canceled)=False) AND ((Customers.Married)=False));
UNION SELECT DISTINCT Customers.Salutation, Customers.[First Name], Customers.[Last Name], Customers.Title, Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Customers INNER JOIN (Trips INNER JOIN [Sales & Payments] ON Trips.[Trip Number] = [Sales & Payments].[Trip Number]) ON Customers.[Customer Number] = [Sales & Payments].[Customer Number]
WHERE ((([Sales & Payments].[Trip Number])=[Enter Trip Number]) AND ((Customers.Deceased)=False) AND (([Sales & Payments].Canceled)=False) AND ((Customers.Married)=True));

Current data looks like:
MR. RICHARD BERNA Address........ ............... ...
MRS. ADRIENNE BERNA Address........ ............... ...
Mr. Duke Beth Address........ ............... ...
Mrs. Nancy Beth Address........ ............... ...
Mrs. RUTH BORGARDT Address........ ............... ...
MR. Mike Cohn Address........ ............... ...
MRS. Janet Cohn Address........ ............... ...
Mrs. SUSAN FREDERITZI Address........ ............... ...
Ms. MARLO GADDO Address........ ............... ...
MRS. DOROTHY HAACKER Address........ ............... ...
MR. DAVID HAACKER Address........ ............... ...

Data needs to look like:
Mr. Richard and Adrienne Berna addresss....... ........
Mr. Duke and Nancy Beth addresss....... ........
Mrs. Ruth Borgardt addresss....... ........
MR. Mike and Janet Cohn addresss....... ........
Mrs. Susan Frederitzi addresss....... ........
Ms. Marlo Gaddo addresss....... ........
MRS. Dorothy and David Haacker addresss....... ........

How can I modify the code to "combine matching address fields to only 1 line and not two lines"? Any suggestions would be great. Thank you very much in advance for all ideas give to me.

If Restructuring and/or Normalizing are not viable Options, there is a code based solution, but it relies heavily on the Addresses being exactly the same, and not factoring in City, State, etc. Case in point, all the Addresses below refer to the same physical Location but would not be considered equal when tested for equality. If you wish to proceed along these lines, let me know.

Code:

2914 South 15th Street
2914 So. 15th Street
2914 S. 15th Street
2914 So. 15 St.
2914 S 15th Street
2914 South 15th St.
2914 So. 15th St.
Etc…

Code:

'considered equal
2914 So. 15th Street
2914 So. 15th Street

**brat33** · Apr 28 '10, 04:05 PM

Originally posted by ADezii

If Restructuring and/or Normalizing are not viable Options, there is a code based solution, but it relies heavily on the Addresses being exactly the same, and not factoring in City, State, etc. Case in point, all the Addresses below refer to the same physical Location but would not be considered equal when tested for equality. If you wish to proceed along these lines, let me know.

Code:

2914 South 15th Street
2914 So. 15th Street
2914 S. 15th Street
2914 So. 15 St.
2914 S 15th Street
2914 South 15th St.
2914 So. 15th St.
Etc…

Code:

'considered equal
2914 So. 15th Street
2914 So. 15th Street

Adezii - yes this is how we want to proceed at this time. This project is very sloppy for a number of reasons, and I will continue to inform the uppers how things "should" be done. If you could help me out that would be great! I will say the data entry people are very meticulous on how they enter their data, and check for consistency very well. They understand that the address have to match 100%. Thank you so much!!

**ADezii** · Apr 28 '10, 04:32 PM

Originally posted by brat33

Adezii - yes this is how we want to proceed at this time. This project is very sloppy for a number of reasons, and I will continue to inform the uppers how things "should" be done. If you could help me out that would be great! I will say the data entry people are very meticulous on how they enter their data, and check for consistency very well. They understand that the address have to match 100%. Thank you so much!!

I'm assuming that all duplicate data currently exists in the Customers Table, am I correct? You also wish to restructure the Customers Table so that all Customers with the same Address exist within in the same Record, correct?

**brat33** · Apr 28 '10, 06:14 PM

All the data does exist within the customers tables, but I do NOT want to combine the duplicate data in the table to one entry, but, want to combine the duplicate data to just one entry on a report. So I will have a report that is similar to:

(combined line) Mr & Mrs Bob Jones Address........ .
not combined) Mrs Pearl Perry Address.....
(combined line) Mr. and Mrs. fName lName Address.....

**ADezii** · Apr 28 '10, 07:29 PM

Originally posted by brat33

All the data does exist within the customers tables, but I do NOT want to combine the duplicate data in the table to one entry, but, want to combine the duplicate data to just one entry on a report. So I will have a report that is similar to:

(combined line) Mr & Mrs Bob Jones Address........ .
not combined) Mrs Pearl Perry Address.....
(combined line) Mr. and Mrs. fName lName Address.....

The only way that I see this happening is by writing to a Temporary Table, then use that Table among the other joined Tables as the Record Source for the Report. The major problem that I see is what Values in the [Customer Number], [Married], and [Deceased] Fields to you use for the single entry that will eliminate the Duplication? In the example below do you use Richard's or Adrienne's Values in these Fields? It could also be that my interpretation is way off on this one.

Code:

MR. RICHARD BERNA Address.......................... 
MRS. ADRIENNE BERNA Address..........................

**brat33** · Apr 28 '10, 07:47 PM

the use of a temp table may be easiest for me to use. Let me play around with that a bit and see what I can come up with. I have had nothing but problems when they gave me this database to modify for them! I am hopeful that I can convert it over soon...I will get back to you if I need more assistance. But the temp table may just be the route for me to go! Thanks...

**ADezii** · Apr 28 '10, 08:26 PM

Originally posted by brat33

the use of a temp table may be easiest for me to use. Let me play around with that a bit and see what I can come up with. I have had nothing but problems when they gave me this database to modify for them! I am hopeful that I can convert it over soon...I will get back to you if I need more assistance. But the temp table may just be the route for me to go! Thanks...

I'll create a Demo for you when I get the chance to illustrate my point.

**ADezii** · Apr 28 '10, 10:01 PM

I played around with it a little, and may have created a monster. Open the Attachment to see.

Attached Files

Customers.zip (20.7 KB, 155 views)

**patjones** · Apr 29 '10, 06:05 PM

I found that this worked, although it doesn't get the order of "Mrs." and "Mr." correct when the "Mrs." record comes first in the Customers table:

Code:

(SELECT Customers.Salutation & " & " & tblCustAlias.Salutation & " " & Customers.FirstName  & " " &  Customers.LastName, Customers.Address
 FROM Customers INNER JOIN Customers AS tblCustAlias ON (tblCustAlias.EmployeeID > Customers.EmployeeID AND Customers.Address = tblCustAlias.Address))
 UNION ALL
(SELECT Customers.Salutation & " " & Customers.FirstName  & " " &  Customers.LastName, Customers.Address
 FROM Customers
 WHERE Address IN (SELECT Address 
                   FROM Customers
                   GROUP BY Address
                   HAVING COUNT(Address) = 1));

The first SELECT picks out the couples by comparing Customers to an alias of Customers and looking at where there are unequal Employee ID's (and hence two records). The second SELECT picks out the single people by looking at the records whose address appears only once in Customers.

Pat

**ADezii** · Apr 29 '10, 07:30 PM

Originally posted by zepphead80

I found that this worked, although it doesn't get the order of "Mrs." and "Mr." correct when the "Mrs." record comes first in the Customers table:

Code:

(SELECT Customers.Salutation & " & " & tblCustAlias.Salutation & " " & Customers.FirstName  & " " &  Customers.LastName, Customers.Address
 FROM Customers INNER JOIN Customers AS tblCustAlias ON (tblCustAlias.EmployeeID > Customers.EmployeeID AND Customers.Address = tblCustAlias.Address))
 UNION ALL
(SELECT Customers.Salutation & " " & Customers.FirstName  & " " &  Customers.LastName, Customers.Address
 FROM Customers
 WHERE Address IN (SELECT Address 
                   FROM Customers
                   GROUP BY Address
                   HAVING COUNT(Address) = 1));

The first SELECT picks out the couples by comparing Customers to an alias of Customers and looking at where there are unequal Employee ID's (and hence two records). The second SELECT picks out the single people by looking at the records whose address appears only once in Customers.

Pat

Really nice approach, zepphead80.

**brat33** · Apr 30 '10, 01:47 PM

Originally posted by zepphead80

I found that this worked, although it doesn't get the order of "Mrs." and "Mr." correct when the "Mrs." record comes first in the Customers table:

Code:

(SELECT Customers.Salutation & " & " & tblCustAlias.Salutation & " " & Customers.FirstName  & " " &  Customers.LastName, Customers.Address
 FROM Customers INNER JOIN Customers AS tblCustAlias ON (tblCustAlias.EmployeeID > Customers.EmployeeID AND Customers.Address = tblCustAlias.Address))
 UNION ALL
(SELECT Customers.Salutation & " " & Customers.FirstName  & " " &  Customers.LastName, Customers.Address
 FROM Customers
 WHERE Address IN (SELECT Address 
                   FROM Customers
                   GROUP BY Address
                   HAVING COUNT(Address) = 1));

The first SELECT picks out the couples by comparing Customers to an alias of Customers and looking at where there are unequal Employee ID's (and hence two records). The second SELECT picks out the single people by looking at the records whose address appears only once in Customers.

Pat

Thank you zepphead80....I was onto something similar, but gave up and used you code snippet. Thank you for helping out... Works well!

Thank you to everyone else for input as well. I am taking all these "comments" about the design and showing that this database really is as bad as I have said it was! :)

Everyone have a great weekend! and THANK YOU AGAIN!!!

**patjones** · Apr 30 '10, 01:56 PM

That's great! That's what the site is for - people can exchange ideas and help out. Good luck in revamping this database. I would say that you should just do it, and then show your superiors how well it works out!

Pat

combine matching addresses on multiple lines onto one line

combine matching addresses on multiple lines onto one line

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment