By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,596 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

combine matching addresses on multiple lines onto one line

P: 36
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT Customers.Salutation, Customers.[First Name], Customers.[Last Name], Customers.Title, Customers.Address, Customers.City, Customers.State, Customers.Zip
  2. 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]
  3. 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));
  4. UNION SELECT DISTINCT Customers.Salutation, Customers.[First Name], Customers.[Last Name], Customers.Title, Customers.Address, Customers.City, Customers.State, Customers.Zip
  5. 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]
  6. WHERE ((([Sales & Payments].[Trip Number])=[Enter Trip Number]) AND ((Customers.Deceased)=False) AND (([Sales & Payments].Canceled)=False) AND ((Customers.Married)=True));
  7.  

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.
Apr 26 '10 #1

✓ answered by patjones

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:

Expand|Select|Wrap|Line Numbers
  1. (SELECT Customers.Salutation & " & " & tblCustAlias.Salutation & " " & Customers.FirstName  & " " &  Customers.LastName, Customers.Address
  2.  FROM Customers INNER JOIN Customers AS tblCustAlias ON (tblCustAlias.EmployeeID > Customers.EmployeeID AND Customers.Address = tblCustAlias.Address))
  3.  UNION ALL
  4. (SELECT Customers.Salutation & " " & Customers.FirstName  & " " &  Customers.LastName, Customers.Address
  5.  FROM Customers
  6.  WHERE Address IN (SELECT Address 
  7.                    FROM Customers
  8.                    GROUP BY Address
  9.                    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

Share this Question
Share on Google+
22 Replies


patjones
Expert 100+
P: 931
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
Apr 26 '10 #2

NeoPa
Expert Mod 15k+
P: 31,186
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.
Apr 27 '10 #3

ADezii
Expert 5K+
P: 8,599
@brat33
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.
Expand|Select|Wrap|Line Numbers
  1. 2914 South 15th Street
  2. 2914 So. 15th Street
  3. 2914 S. 15th Street
  4. 2914 So. 15 St.
  5. 2914 S 15th Street
  6. 2914 South 15th St.
  7. 2914 So. 15th St.
  8. Etc…
Expand|Select|Wrap|Line Numbers
  1. 'considered equal
  2. 2914 So. 15th Street
  3. 2914 So. 15th Street
Apr 27 '10 #4

P: 36
@ADezii
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!!
Apr 28 '10 #5

ADezii
Expert 5K+
P: 8,599
@brat33
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?
Apr 28 '10 #6

P: 36
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.....
Apr 28 '10 #7

ADezii
Expert 5K+
P: 8,599
@brat33
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.
Expand|Select|Wrap|Line Numbers
  1. MR. RICHARD BERNA Address.......................... 
  2. MRS. ADRIENNE BERNA Address.......................... 
Apr 28 '10 #8

P: 36
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...
Apr 28 '10 #9

ADezii
Expert 5K+
P: 8,599
@brat33
I'll create a Demo for you when I get the chance to illustrate my point.
Apr 28 '10 #10

ADezii
Expert 5K+
P: 8,599
I played around with it a little, and may have created a monster. Open the Attachment to see.
Attached Files
File Type: zip Customers.zip (20.7 KB, 76 views)
Apr 28 '10 #11

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. (SELECT Customers.Salutation & " & " & tblCustAlias.Salutation & " " & Customers.FirstName  & " " &  Customers.LastName, Customers.Address
  2.  FROM Customers INNER JOIN Customers AS tblCustAlias ON (tblCustAlias.EmployeeID > Customers.EmployeeID AND Customers.Address = tblCustAlias.Address))
  3.  UNION ALL
  4. (SELECT Customers.Salutation & " " & Customers.FirstName  & " " &  Customers.LastName, Customers.Address
  5.  FROM Customers
  6.  WHERE Address IN (SELECT Address 
  7.                    FROM Customers
  8.                    GROUP BY Address
  9.                    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
Apr 29 '10 #12

ADezii
Expert 5K+
P: 8,599
@zepphead80
Really nice approach, zepphead80.
Apr 29 '10 #13

P: 36
@zepphead80
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!!!
Apr 30 '10 #14

patjones
Expert 100+
P: 931
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
Apr 30 '10 #15

P: 36
I am back at it, and am trying to add another table to perform another check. I get the following error:
The specified field 'Trips.[Trip Number]' could refer to more than one table listed in the FROM clause of your SQL statement.
I have the table name specified in front of the column name, and am not sure why I continue to get this error. I am not using Name AutoCorrect in this database. It is turned off, but I still cannot seem to get this to work. Here is my query and what I was attempting to modify....
Expand|Select|Wrap|Line Numbers
  1. (SELECT Customers.Salutation & " & " & tblCustAlias.Salutation & " " & Customers.[First Name]  & " " &  Customers.[Last Name], Customers.Address, Customers.City, Customers.State, Customers.Zip
  2. FROM Customers INNER JOIN Customers AS tblCustAlias ON (tblCustAlias.[Customer Number] > Customers.[Customer Number] AND Customers.Address = tblCustAlias.Address), [sales & Payments], Trips
  3. WHERE Customers.[Customer Number] = [Sales & Payments].[Customer Number] OR  tblCustAlias.[Customer Number] = [Sales & Payments].[Customer Number] AND [Sales & Payments].[Trip Number] = (Trips.[Enter Trip Number])) 
  4. UNION ALL (SELECT Customers.Salutation & " " & Customers.[First Name]  & " " &  Customers.[Last Name], Customers.Address, Customers.City, Customers.State, Customers.Zip
  5. FROM Customers,  [sales & Payments]
  6. WHERE Address IN (SELECT Address 
  7.               FROM Customers
  8.               GROUP BY Address
  9.               HAVING COUNT(Address) = 1) AND Customers.[Customer Number] = [Sales & Payments].[Customer Number]);
If anyone is still available to continue helping with this ugly mess I would appreciate it!
May 3 '10 #16

patjones
Expert 100+
P: 931
I don't see Trips.[Trip Number] anywhere in the query though.

In the WHERE clause, when you write [Sales & Payments].[Trip Number] = Trips.[Enter Trip Number], where is "[Enter Trip Number]" coming from? It looks like an entry from an input box somewhere...

Pat
May 4 '10 #17

NeoPa
Expert Mod 15k+
P: 31,186
Spot on Pat. The Trips. part of that should be removed. I suspect it was added later, probably to a bunch of references. This one shouldn't have had this added.
May 4 '10 #18

P: 36
My bad! The error message does read: The specified field 'Trips.[Enter Trip Number]' could refer to more than one table listed in the FROM clause of your SQL statement.

Can I not add a parameter query input function into this type of SQL statement? With the joins, and unions and everything else that is going on inside the SQL, that is...
May 4 '10 #19

patjones
Expert 100+
P: 931
Well is "[Enter Trip Number]" coming from a text box on a form or what? And how are you executing the SQL?

If you're executing the SQL within some Visual Basic code, the quick and easy way is to concatenate the text box value with the rest of the query. Slightly more work, but more secure, would be to define the SQL as a QueryDef and use a parameter for [Enter Trip Number].

Pat
May 4 '10 #20

NeoPa
Expert Mod 15k+
P: 31,186
brat33: Can I not add a parameter query input function into this type of SQL statement?
It's hard to know exactly what you mean by that, but I suspect you haven't tried the suggestion from post #18. I can't be sure, but it sounds as if just doing that would answer your latest question.
May 4 '10 #21

P: 36
I am a little slow this morning! I got it figured out. I removed the word Trips, had to add a few parens, change an AND to an OR but now it is displaying data!
May 4 '10 #22

NeoPa
Expert Mod 15k+
P: 31,186
That's fine, but please try to be a little more careful before posting in future :) I'm sure you wouldn't want to be the cause of anyone wasting their time unnecessarily.
May 4 '10 #23

Post your reply

Sign in to post your reply or Sign up for a free account.