SQL to combine columns of multiple rows into one row

[Guest]

I'm using the following sql to create a list of customer to create mailing
labels. However, some of the rows have duplicate addresses. I like to
create one row and concatenate the First and Last name of the duplicate rows

SELECT DISTINCT tblContacts.FirstName, tblContacts.LastName,
tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode, tblContacts.HomePhone, tblContacts.ContactID,
[FirstName] & " " & [LastNAme] AS expr1
FROM tblContacts INNER JOIN tblPolicy ON tblContacts.ContactID =
tblPolicy.ContactID
WHERE (((tblPolicy.Status)="Active"))
ORDER BY tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode;

Sample Data:

First Name Last Name Address City State ZipCode Home Phone
Bob Smith 123 Pine Help Mo 63333 (123) 123-1234
Mary Smith 123 Pine Help Mo 63333 (123) 123-1234
Joan Lee 456 Oak June Mo 64545 (343) 323-4343
Betty Jones 456 Oak June Mo 64545 (343) 323-4343

Here's the results I'd like to achive:

CombinedName Address City State ZipCode Home Phone
Bob & Mary Smith 123 Pine Help Mo 63333 (123) 123-1234
Joan Lee & Betty Jones 456 Oak June Mo 64545 (343) 323-4343

Is this possible using SQL or will I need to create VBA code to achive?

 

[Tom Ellison]

Dear RLG:

While it is possible to do what you say, it is very probable that it won't
work out very well. The problem will be that data will not likely be
entered with sufficient consistency. I cannot advise you of what to do with
this, so I'll ignore it for the time.

With that said, it appears you have three sets of data to merge. One set
will have matching values in Last Name, Address, City, State, ZipCode, and
Home Phone, with only differing first names. The next set matches all the
above except for Last Name. The third set matches none of these things.

Now, I'm not sure what you would want to do if there were a 3 way match
within either of these sets. Your example shows what to do only where there
is a 2 way match.

First, here is a way to identify the 2 way matches including Last Name:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last Name] AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Some notes about this portion:

- If any of the columns do not match perfectly, the process will fail. If
one Address is "123 Pine" and another is "123 Pine St" then they will not
match. If a space, paren, or hyphen in the Phone is missing or misplaced,
they will not match. This will require extreme precision, nearly
perfection, in the data entry or it will fail.

- If there are 3 persons with the same LastName and the other common
columns, they will not be included. You need to decide what you want to do
with these before programming.

- There is no way to distinguish male and female in this, so putting the
males name first will not be possible. I have put the alphabetically first
name first.

For the second set:

SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Similar notes apply to this portion.

Finally, for the unrepeated persons:

SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

After making sure these work correctly, take them and make a single query
out of them, putting UNION ALL between them:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last Name] AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

Perhaps this is close. I'm not sure it's going to do you much good, but
there it is.

A note about consistency in typing. You have not consistently represented
the names of your columns. For example, some times you have LastName and
some times Last Name. The point is, if it is not so easy for you to be
consistent about something that really has only a single correct technical
spelling, how can you expect users to type in street names with consistency.
It is probably unlikely, unless you start with a database of all correct
street names in each city, and them limit users to selecting the street
names from that list.

Rigorously limiting choices like this is the proven way of getting
consistent data, which can then be used to perform functions like that which
you desire with a high degree of success.

Tom Ellison

I'm using the following sql to create a list of customer to create mailing
labels. However, some of the rows have duplicate addresses. I like to
create one row and concatenate the First and Last name of the duplicate rows

SELECT DISTINCT tblContacts.FirstName, tblContacts.LastName,
tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode, tblContacts.HomePhone, tblContacts.ContactID,
[FirstName] & " " & [LastNAme] AS expr1
FROM tblContacts INNER JOIN tblPolicy ON tblContacts.ContactID =
tblPolicy.ContactID
WHERE (((tblPolicy.Status)="Active"))
ORDER BY tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode;

Sample Data:

First Name Last Name Address City State ZipCode Home Phone
Bob Smith 123 Pine Help Mo 63333 (123) 123-1234
Mary Smith 123 Pine Help Mo 63333 (123) 123-1234
Joan Lee 456 Oak June Mo 64545 (343) 323-4343
Betty Jones 456 Oak June Mo 64545 (343) 323-4343

Here's the results I'd like to achive:

CombinedName Address City State ZipCode Home Phone
Bob & Mary Smith 123 Pine Help Mo 63333 (123) 123-1234
Joan Lee & Betty Jones 456 Oak June Mo 64545 (343) 323-4343

Is this possible using SQL or will I need to create VBA code to achive?

 

[Guest]

Tom, thanks for the reply. I was able to get 2 of the 3 sql's to run. The
one I'm having prolems with is:

SELECT FIRST ([FirstName] & " " & [LastName]) " & " LAST ([FirstName] & " "
& [LastName]) AS CombinedName,
Address, City, State, ZipCode, [HomePhone]
FROM tblContacts
WHERE FIRST([LastName]) <> LAST([LastName])
GROUP BY Address, City, State, ZipCode, [HomePhone]
HAVING COUNT(*) = 2

I'm receiving the following syntax error:

Syntax error (missing operator) in query expression 'FIRST ([FirstName] & "
" & [LastName]) " & " LAST ([FirstName] & " " & [LastName])'

The " & " just before the LAST function is highlighted.

I hope this reply does not post twice as I encounter a problem posting the
first time.

Rlg

Dear RLG:

While it is possible to do what you say, it is very probable that it won't
work out very well. The problem will be that data will not likely be
entered with sufficient consistency. I cannot advise you of what to do with
this, so I'll ignore it for the time.

With that said, it appears you have three sets of data to merge. One set
will have matching values in Last Name, Address, City, State, ZipCode, and
Home Phone, with only differing first names. The next set matches all the
above except for Last Name. The third set matches none of these things.

Now, I'm not sure what you would want to do if there were a 3 way match
within either of these sets. Your example shows what to do only where there
is a 2 way match.

First, here is a way to identify the 2 way matches including Last Name:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last Name] AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Some notes about this portion:

- If any of the columns do not match perfectly, the process will fail. If
one Address is "123 Pine" and another is "123 Pine St" then they will not
match. If a space, paren, or hyphen in the Phone is missing or misplaced,
they will not match. This will require extreme precision, nearly
perfection, in the data entry or it will fail.

- If there are 3 persons with the same LastName and the other common
columns, they will not be included. You need to decide what you want to do
with these before programming.

- There is no way to distinguish male and female in this, so putting the
males name first will not be possible. I have put the alphabetically first
name first.

For the second set:

SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Similar notes apply to this portion.

Finally, for the unrepeated persons:

SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

After making sure these work correctly, take them and make a single query
out of them, putting UNION ALL between them:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last Name] AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

Perhaps this is close. I'm not sure it's going to do you much good, but
there it is.

A note about consistency in typing. You have not consistently represented
the names of your columns. For example, some times you have LastName and
some times Last Name. The point is, if it is not so easy for you to be
consistent about something that really has only a single correct technical
spelling, how can you expect users to type in street names with consistency.
It is probably unlikely, unless you start with a database of all correct
street names in each city, and them limit users to selecting the street
names from that list.

Rigorously limiting choices like this is the proven way of getting
consistent data, which can then be used to perform functions like that which
you desire with a high degree of success.

Tom Ellison

I'm using the following sql to create a list of customer to create mailing
labels. However, some of the rows have duplicate addresses. I like to
create one row and concatenate the First and Last name of the duplicate rows

SELECT DISTINCT tblContacts.FirstName, tblContacts.LastName,
tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode, tblContacts.HomePhone, tblContacts.ContactID,
[FirstName] & " " & [LastNAme] AS expr1
FROM tblContacts INNER JOIN tblPolicy ON tblContacts.ContactID =
tblPolicy.ContactID
WHERE (((tblPolicy.Status)="Active"))
ORDER BY tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode;

Sample Data:

First Name Last Name Address City State ZipCode Home Phone
Bob Smith 123 Pine Help Mo 63333 (123) 123-1234
Mary Smith 123 Pine Help Mo 63333 (123) 123-1234
Joan Lee 456 Oak June Mo 64545 (343) 323-4343
Betty Jones 456 Oak June Mo 64545 (343) 323-4343

Here's the results I'd like to achive:

CombinedName Address City State ZipCode Home Phone
Bob & Mary Smith 123 Pine Help Mo 63333 (123) 123-1234
Joan Lee & Betty Jones 456 Oak June Mo 64545 (343) 323-4343

Is this possible using SQL or will I need to create VBA code to achive?

 

[Guest]

Tom: I resolved the syntax error: I had to add the & before and after the " &
". The change to the select statement is below:

SELECT FIRST ([FirstName] & " " & [LastName]) & " & " & LAST ([FirstName] &
" "
& [LastName]) AS CombinedName,
Address, City, State, ZipCode, [HomePhone]

I ran it and it dod not return any rows. I'm investigating now. But at
least I got the sql to run. Thanks again.

Rlg

Tom, thanks for the reply. I was able to get 2 of the 3 sql's to run. The
one I'm having prolems with is:

SELECT FIRST ([FirstName] & " " & [LastName]) " & " LAST ([FirstName] & " "
& [LastName]) AS CombinedName,
Address, City, State, ZipCode, [HomePhone]
FROM tblContacts
WHERE FIRST([LastName]) <> LAST([LastName])
GROUP BY Address, City, State, ZipCode, [HomePhone]
HAVING COUNT(*) = 2

I'm receiving the following syntax error:

Syntax error (missing operator) in query expression 'FIRST ([FirstName] & "
" & [LastName]) " & " LAST ([FirstName] & " " & [LastName])'

The " & " just before the LAST function is highlighted.

I hope this reply does not post twice as I encounter a problem posting the
first time.

Rlg

Dear RLG:

While it is possible to do what you say, it is very probable that it won't
work out very well. The problem will be that data will not likely be
entered with sufficient consistency. I cannot advise you of what to do with
this, so I'll ignore it for the time.

With that said, it appears you have three sets of data to merge. One set
will have matching values in Last Name, Address, City, State, ZipCode, and
Home Phone, with only differing first names. The next set matches all the
above except for Last Name. The third set matches none of these things.

Now, I'm not sure what you would want to do if there were a 3 way match
within either of these sets. Your example shows what to do only where there
is a 2 way match.

First, here is a way to identify the 2 way matches including Last Name:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last Name] AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Some notes about this portion:

- If any of the columns do not match perfectly, the process will fail. If
one Address is "123 Pine" and another is "123 Pine St" then they will not
match. If a space, paren, or hyphen in the Phone is missing or misplaced,
they will not match. This will require extreme precision, nearly
perfection, in the data entry or it will fail.

- If there are 3 persons with the same LastName and the other common
columns, they will not be included. You need to decide what you want to do
with these before programming.

- There is no way to distinguish male and female in this, so putting the
males name first will not be possible. I have put the alphabetically first
name first.

For the second set:

SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Similar notes apply to this portion.

Finally, for the unrepeated persons:

SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

After making sure these work correctly, take them and make a single query
out of them, putting UNION ALL between them:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last Name] AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

Perhaps this is close. I'm not sure it's going to do you much good, but
there it is.

A note about consistency in typing. You have not consistently represented
the names of your columns. For example, some times you have LastName and
some times Last Name. The point is, if it is not so easy for you to be
consistent about something that really has only a single correct technical
spelling, how can you expect users to type in street names with consistency.
It is probably unlikely, unless you start with a database of all correct
street names in each city, and them limit users to selecting the street
names from that list.

Rigorously limiting choices like this is the proven way of getting
consistent data, which can then be used to perform functions like that which
you desire with a high degree of success.

Tom Ellison

I'm using the following sql to create a list of customer to create mailing
labels. However, some of the rows have duplicate addresses. I like to
create one row and concatenate the First and Last name of the duplicate rows

SELECT DISTINCT tblContacts.FirstName, tblContacts.LastName,
tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode, tblContacts.HomePhone, tblContacts.ContactID,
[FirstName] & " " & [LastNAme] AS expr1
FROM tblContacts INNER JOIN tblPolicy ON tblContacts.ContactID =
tblPolicy.ContactID
WHERE (((tblPolicy.Status)="Active"))
ORDER BY tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode;

Sample Data:

First Name Last Name Address City State ZipCode Home Phone
Bob Smith 123 Pine Help Mo 63333 (123) 123-1234
Mary Smith 123 Pine Help Mo 63333 (123) 123-1234
Joan Lee 456 Oak June Mo 64545 (343) 323-4343
Betty Jones 456 Oak June Mo 64545 (343) 323-4343

Here's the results I'd like to achive:

CombinedName Address City State ZipCode Home Phone
Bob & Mary Smith 123 Pine Help Mo 63333 (123) 123-1234
Joan Lee & Betty Jones 456 Oak June Mo 64545 (343) 323-4343

Is this possible using SQL or will I need to create VBA code to achive?

 

[Tom Ellison]

Dear rlg:

I ventured somewhat into uncharted territory. I expect it needs to be:

SELECT FIRST(FirstName) & " " FIRST(LastName) & " & " &
LAST(FirstName) & " " LAST(LastName) AS CombinedName

I expect this will avoid problems. But it doesn't explain how it is you
have no results.

Can you find a pair of rows that should meet this condition? Please post
the data for that pair.

Tom Ellison

Tom: I resolved the syntax error: I had to add the & before and after the " &
". The change to the select statement is below:

SELECT FIRST ([FirstName] & " " & [LastName]) & " & " & LAST ([FirstName] &
" "
& [LastName]) AS CombinedName,
Address, City, State, ZipCode, [HomePhone]

I ran it and it dod not return any rows. I'm investigating now. But at
least I got the sql to run. Thanks again.

Rlg

Tom, thanks for the reply. I was able to get 2 of the 3 sql's to run. The
one I'm having prolems with is:

SELECT FIRST ([FirstName] & " " & [LastName]) " & " LAST ([FirstName] & " "
& [LastName]) AS CombinedName,
Address, City, State, ZipCode, [HomePhone]
FROM tblContacts
WHERE FIRST([LastName]) <> LAST([LastName])
GROUP BY Address, City, State, ZipCode, [HomePhone]
HAVING COUNT(*) = 2

I'm receiving the following syntax error:

Syntax error (missing operator) in query expression 'FIRST ([FirstName] & "
" & [LastName]) " & " LAST ([FirstName] & " " & [LastName])'

The " & " just before the LAST function is highlighted.

I hope this reply does not post twice as I encounter a problem posting the
first time.

Rlg

Dear RLG:

While it is possible to do what you say, it is very probable that it won't
work out very well. The problem will be that data will not likely be
entered with sufficient consistency. I cannot advise you of what to do with
this, so I'll ignore it for the time.

With that said, it appears you have three sets of data to merge. One set
will have matching values in Last Name, Address, City, State, ZipCode, and
Home Phone, with only differing first names. The next set matches all the
above except for Last Name. The third set matches none of these things.

Now, I'm not sure what you would want to do if there were a 3 way match
within either of these sets. Your example shows what to do only where there
is a 2 way match.

First, here is a way to identify the 2 way matches including Last Name:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last Name] AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Some notes about this portion:

- If any of the columns do not match perfectly, the process will fail. If
one Address is "123 Pine" and another is "123 Pine St" then they will not
match. If a space, paren, or hyphen in the Phone is missing or misplaced,
they will not match. This will require extreme precision, nearly
perfection, in the data entry or it will fail.

- If there are 3 persons with the same LastName and the other common
columns, they will not be included. You need to decide what you want to do
with these before programming.

- There is no way to distinguish male and female in this, so putting the
males name first will not be possible. I have put the alphabetically first
name first.

For the second set:

SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Similar notes apply to this portion.

Finally, for the unrepeated persons:

SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

After making sure these work correctly, take them and make a single query
out of them, putting UNION ALL between them:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last Name] AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

Perhaps this is close. I'm not sure it's going to do you much good, but
there it is.

A note about consistency in typing. You have not consistently represented
the names of your columns. For example, some times you have LastName and
some times Last Name. The point is, if it is not so easy for you to be
consistent about something that really has only a single correct technical
spelling, how can you expect users to type in street names with consistency.
It is probably unlikely, unless you start with a database of all correct
street names in each city, and them limit users to selecting the street
names from that list.

Rigorously limiting choices like this is the proven way of getting
consistent data, which can then be used to perform functions like that which
you desire with a high degree of success.

Tom Ellison


I'm using the following sql to create a list of customer to create mailing
labels. However, some of the rows have duplicate addresses. I like to
create one row and concatenate the First and Last name of the duplicate
rows

SELECT DISTINCT tblContacts.FirstName, tblContacts.LastName,
tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode, tblContacts.HomePhone, tblContacts.ContactID,
[FirstName] & " " & [LastNAme] AS expr1
FROM tblContacts INNER JOIN tblPolicy ON tblContacts.ContactID =
tblPolicy.ContactID
WHERE (((tblPolicy.Status)="Active"))
ORDER BY tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode;

Sample Data:

First Name Last Name Address City State ZipCode Home Phone
Bob Smith 123 Pine Help Mo 63333 (123)
123-1234
Mary Smith 123 Pine Help Mo 63333 (123)
123-1234
Joan Lee 456 Oak June Mo 64545 (343)
323-4343
Betty Jones 456 Oak June Mo 64545 (343)
323-4343

Here's the results I'd like to achive:

CombinedName Address City State ZipCode Home Phone
Bob & Mary Smith 123 Pine Help Mo 63333 (123) 123-1234
Joan Lee & Betty Jones 456 Oak June Mo 64545 (343) 323-4343

Is this possible using SQL or will I need to create VBA code to achive?

 

[John Spencer]

Tom and rlg,

Pardon me, but I think First and Last are aggregate functions (just like Max
and Min) and as such would need to be in the Having clause instead of the
Where clause of the query. I could be mistaken of course (just ask my
wife).

SELECT FIRST([First Name] & " " & [Last Name]) & " " &
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2 AND FIRST([Last Name]) <> LAST([Last Name])

Dear rlg:

I ventured somewhat into uncharted territory. I expect it needs to be:

SELECT FIRST(FirstName) & " " FIRST(LastName) & " & " &
LAST(FirstName) & " " LAST(LastName) AS CombinedName

I expect this will avoid problems. But it doesn't explain how it is you
have no results.

Can you find a pair of rows that should meet this condition? Please post
the data for that pair.

Tom Ellison

Tom: I resolved the syntax error: I had to add the & before and after the " &
". The change to the select statement is below:

SELECT FIRST ([FirstName] & " " & [LastName]) & " & " & LAST ([FirstName] &
" "
& [LastName]) AS CombinedName,
Address, City, State, ZipCode, [HomePhone]

I ran it and it dod not return any rows. I'm investigating now. But at
least I got the sql to run. Thanks again.

Rlg

Tom, thanks for the reply. I was able to get 2 of the 3 sql's to run. The
one I'm having prolems with is:

SELECT FIRST ([FirstName] & " " & [LastName]) " & " LAST ([FirstName]
& " "
& [LastName]) AS CombinedName,
Address, City, State, ZipCode, [HomePhone]
FROM tblContacts
WHERE FIRST([LastName]) <> LAST([LastName])
GROUP BY Address, City, State, ZipCode, [HomePhone]
HAVING COUNT(*) = 2

I'm receiving the following syntax error:

Syntax error (missing operator) in query expression 'FIRST ([FirstName] & "
" & [LastName]) " & " LAST ([FirstName] & " " & [LastName])'

The " & " just before the LAST function is highlighted.

I hope this reply does not post twice as I encounter a problem posting the
first time.

Rlg


:

Dear RLG:

While it is possible to do what you say, it is very probable that it won't
work out very well. The problem will be that data will not likely be
entered with sufficient consistency. I cannot advise you of what to do with
this, so I'll ignore it for the time.

With that said, it appears you have three sets of data to merge. One set
will have matching values in Last Name, Address, City, State,
ZipCode, and
Home Phone, with only differing first names. The next set matches
all the
above except for Last Name. The third set matches none of these things.

Now, I'm not sure what you would want to do if there were a 3 way match
within either of these sets. Your example shows what to do only
where there
is a 2 way match.

First, here is a way to identify the 2 way matches including Last Name:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last Name]
AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Some notes about this portion:

- If any of the columns do not match perfectly, the process will
fail. If
one Address is "123 Pine" and another is "123 Pine St" then they will not
match. If a space, paren, or hyphen in the Phone is missing or misplaced,
they will not match. This will require extreme precision, nearly
perfection, in the data entry or it will fail.

- If there are 3 persons with the same LastName and the other common
columns, they will not be included. You need to decide what you want to do
with these before programming.

- There is no way to distinguish male and female in this, so putting the
males name first will not be possible. I have put the alphabetically first
name first.

For the second set:

SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Similar notes apply to this portion.

Finally, for the unrepeated persons:

SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

After making sure these work correctly, take them and make a single query
out of them, putting UNION ALL between them:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last Name]
AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

Perhaps this is close. I'm not sure it's going to do you much good, but
there it is.

A note about consistency in typing. You have not consistently represented
the names of your columns. For example, some times you have LastName and
some times Last Name. The point is, if it is not so easy for you to be
consistent about something that really has only a single correct technical
spelling, how can you expect users to type in street names with consistency.
It is probably unlikely, unless you start with a database of all correct
street names in each city, and them limit users to selecting the street
names from that list.

Rigorously limiting choices like this is the proven way of getting
consistent data, which can then be used to perform functions like
that which
you desire with a high degree of success.

Tom Ellison


I'm using the following sql to create a list of customer to create mailing
labels. However, some of the rows have duplicate addresses. I
like to
create one row and concatenate the First and Last name of the duplicate
rows

SELECT DISTINCT tblContacts.FirstName, tblContacts.LastName,
tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode, tblContacts.HomePhone, tblContacts.ContactID,
[FirstName] & " " & [LastNAme] AS expr1
FROM tblContacts INNER JOIN tblPolicy ON tblContacts.ContactID =
tblPolicy.ContactID
WHERE (((tblPolicy.Status)="Active"))
ORDER BY tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode;

Sample Data:

First Name Last Name Address City State ZipCode Home Phone
Bob Smith 123 Pine Help Mo 63333
(123)
123-1234
Mary Smith 123 Pine Help Mo 63333 (123)
123-1234
Joan Lee 456 Oak June Mo 64545
(343)
323-4343
Betty Jones 456 Oak June Mo 64545 (343)
323-4343

Here's the results I'd like to achive:

CombinedName Address City State ZipCode Home Phone
Bob & Mary Smith 123 Pine Help Mo 63333 (123) 123-1234
Joan Lee & Betty Jones 456 Oak June Mo 64545 (343) 323-4343

Is this possible using SQL or will I need to create VBA code to achive?

 

[Guest]

Tom and John,

Here is what I ended up doing to achieve the results: (Still desk checking –
but so far so good)

Step 1: Create new make-table query to create table of distinct customer
records

SELECT DISTINCT tblContacts.FirstName, tblContacts.LastName,
tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode, tblContacts.HomePhone, tblContacts.ContactID,
tblContacts.Birthdate INTO tblContacts_ActiveDistinct
FROM tblContacts INNER JOIN tblPolicy ON tblContacts.ContactID =
tblPolicy.ContactID
WHERE (((tblPolicy.Status)="Active"))
ORDER BY tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode;

Step 2: Create new query as outlined by Tom with slight modifications
(thanks a bunch)

SELECT First([FirstName]) & " " & First([LastName]) AS CombinedName,
Address, City, State, ZipCode, HomePhone
FROM tblContacts_ActiveDistinct
GROUP BY FirstName, LastName, Address, City, State, ZipCode, HomePhone
HAVING (((First(FirstName))=Last([FirstName])) AND ((Count(*))=2))
ORDER BY Address, City, State, ZipCode
UNION ALL
SELECT First([FirstName]) & " & " & Last([FirstName]) & " " & [LastName] AS
CombinedName, Address, City, State, ZipCode, HomePhone
FROM tblContacts_ActiveDistinct
GROUP BY LastName, Address, City, State, ZipCode, HomePhone
HAVING (((First(FirstName))<>Last([FirstName])) AND ((Count(*))=2))
ORDER BY Address, City, State, ZipCode
UNION ALL
SELECT First([FirstName] & " " & [LastName]) & " & " & Last([FirstName] & "
" & [LastName]) AS CombinedName, Address, City, State, ZipCode, HomePhone
FROM tblContacts_ActiveDistinct
GROUP BY Address, City, State, ZipCode, HomePhone
HAVING (((First(LastName))<>Last([LastName])) AND ((Count(*))=2))
ORDER BY Address, City, State, ZipCode
UNION ALL SELECT First([FirstName]) & " " & First([LastName]) AS
CombinedName, Address, City, State, ZipCode, HomePhone
FROM tblContacts_ActiveDistinct
GROUP BY Address, City, State, ZipCode, HomePhone
HAVING (((Count(*))=1))
ORDER BY Address, City, State, ZipCode;

Once again, can’t thank you enough for pointing me in the right direction.

Thanks, RLG

Tom and rlg,

Pardon me, but I think First and Last are aggregate functions (just like Max
and Min) and as such would need to be in the Having clause instead of the
Where clause of the query. I could be mistaken of course (just ask my
wife).

SELECT FIRST([First Name] & " " & [Last Name]) & " " &
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2 AND FIRST([Last Name]) <> LAST([Last Name])

Dear rlg:

I ventured somewhat into uncharted territory. I expect it needs to be:

SELECT FIRST(FirstName) & " " FIRST(LastName) & " & " &
LAST(FirstName) & " " LAST(LastName) AS CombinedName

I expect this will avoid problems. But it doesn't explain how it is you
have no results.

Can you find a pair of rows that should meet this condition? Please post
the data for that pair.

Tom Ellison

Tom: I resolved the syntax error: I had to add the & before and after the " &
". The change to the select statement is below:

SELECT FIRST ([FirstName] & " " & [LastName]) & " & " & LAST ([FirstName] &
" "
& [LastName]) AS CombinedName,
Address, City, State, ZipCode, [HomePhone]

I ran it and it dod not return any rows. I'm investigating now. But at
least I got the sql to run. Thanks again.

Rlg

:

Tom, thanks for the reply. I was able to get 2 of the 3 sql's to run. The
one I'm having prolems with is:

SELECT FIRST ([FirstName] & " " & [LastName]) " & " LAST ([FirstName]
& " "
& [LastName]) AS CombinedName,
Address, City, State, ZipCode, [HomePhone]
FROM tblContacts
WHERE FIRST([LastName]) <> LAST([LastName])
GROUP BY Address, City, State, ZipCode, [HomePhone]
HAVING COUNT(*) = 2

I'm receiving the following syntax error:

Syntax error (missing operator) in query expression 'FIRST ([FirstName] & "
" & [LastName]) " & " LAST ([FirstName] & " " & [LastName])'

The " & " just before the LAST function is highlighted.

I hope this reply does not post twice as I encounter a problem posting the
first time.

Rlg


:

Dear RLG:

While it is possible to do what you say, it is very probable that it won't
work out very well. The problem will be that data will not likely be
entered with sufficient consistency. I cannot advise you of what to do with
this, so I'll ignore it for the time.

With that said, it appears you have three sets of data to merge. One set
will have matching values in Last Name, Address, City, State,
ZipCode, and
Home Phone, with only differing first names. The next set matches
all the
above except for Last Name. The third set matches none of these things.

Now, I'm not sure what you would want to do if there were a 3 way match
within either of these sets. Your example shows what to do only
where there
is a 2 way match.

First, here is a way to identify the 2 way matches including Last Name:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last Name]
AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Some notes about this portion:

- If any of the columns do not match perfectly, the process will
fail. If
one Address is "123 Pine" and another is "123 Pine St" then they will not
match. If a space, paren, or hyphen in the Phone is missing or misplaced,
they will not match. This will require extreme precision, nearly
perfection, in the data entry or it will fail.

- If there are 3 persons with the same LastName and the other common
columns, they will not be included. You need to decide what you want to do
with these before programming.

- There is no way to distinguish male and female in this, so putting the
males name first will not be possible. I have put the alphabetically first
name first.

For the second set:

SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Similar notes apply to this portion.

Finally, for the unrepeated persons:

SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

After making sure these work correctly, take them and make a single query
out of them, putting UNION ALL between them:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last Name]
AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

Perhaps this is close. I'm not sure it's going to do you much good, but
there it is.

A note about consistency in typing. You have not consistently represented
the names of your columns. For example, some times you have LastName and
some times Last Name. The point is, if it is not so easy for you to be
consistent about something that really has only a single correct technical
spelling, how can you expect users to type in street names with consistency.
It is probably unlikely, unless you start with a database of all correct
street names in each city, and them limit users to selecting the street
names from that list.

Rigorously limiting choices like this is the proven way of getting
consistent data, which can then be used to perform functions like
that which
you desire with a high degree of success.

Tom Ellison


I'm using the following sql to create a list of customer to create mailing
labels. However, some of the rows have duplicate addresses. I
like to
create one row and concatenate the First and Last name of the duplicate
rows

SELECT DISTINCT tblContacts.FirstName, tblContacts.LastName,
tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode, tblContacts.HomePhone, tblContacts.ContactID,
[FirstName] & " " & [LastNAme] AS expr1
FROM tblContacts INNER JOIN tblPolicy ON tblContacts.ContactID =
tblPolicy.ContactID
WHERE (((tblPolicy.Status)="Active"))
ORDER BY tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode;

Sample Data:

First Name Last Name Address City State ZipCode Home Phone
Bob Smith 123 Pine Help Mo 63333
(123)
123-1234
Mary Smith 123 Pine Help Mo 63333 (123)
123-1234
Joan Lee 456 Oak June Mo 64545
(343)
323-4343
Betty Jones 456 Oak June Mo 64545 (343)
323-4343

Here's the results I'd like to achive:

CombinedName Address City State ZipCode Home Phone
Bob & Mary Smith 123 Pine Help Mo 63333 (123) 123-1234
Joan Lee & Betty Jones 456 Oak June Mo 64545 (343) 323-4343

Is this possible using SQL or will I need to create VBA code to achive?

 

[Tom Ellison]

Dear John:

I do not put ordinary filters in a HAVING clause, although I'm aware that
Jet moves them there. I believe either is acceptable.

Tom Ellison

Tom and rlg,

Pardon me, but I think First and Last are aggregate functions (just like Max
and Min) and as such would need to be in the Having clause instead of the
Where clause of the query. I could be mistaken of course (just ask my
wife).

SELECT FIRST([First Name] & " " & [Last Name]) & " " &
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2 AND FIRST([Last Name]) <> LAST([Last Name])

Dear rlg:

I ventured somewhat into uncharted territory. I expect it needs to be:

SELECT FIRST(FirstName) & " " FIRST(LastName) & " & " &
LAST(FirstName) & " " LAST(LastName) AS CombinedName

I expect this will avoid problems. But it doesn't explain how it is you
have no results.

Can you find a pair of rows that should meet this condition? Please post
the data for that pair.

Tom Ellison

Tom: I resolved the syntax error: I had to add the & before and after

the
" &

". The change to the select statement is below:

SELECT FIRST ([FirstName] & " " & [LastName]) & " & " & LAST

([FirstName]
&

" "
& [LastName]) AS CombinedName,
Address, City, State, ZipCode, [HomePhone]

I ran it and it dod not return any rows. I'm investigating now. But at
least I got the sql to run. Thanks again.

Rlg

:

Tom, thanks for the reply. I was able to get 2 of the 3 sql's to

run.
The

one I'm having prolems with is:

SELECT FIRST ([FirstName] & " " & [LastName]) " & " LAST ([FirstName]
& " "
& [LastName]) AS CombinedName,
Address, City, State, ZipCode, [HomePhone]
FROM tblContacts
WHERE FIRST([LastName]) <> LAST([LastName])
GROUP BY Address, City, State, ZipCode, [HomePhone]
HAVING COUNT(*) = 2

I'm receiving the following syntax error:

Syntax error (missing operator) in query expression 'FIRST

([FirstName]
& "

" & [LastName]) " & " LAST ([FirstName] & " " & [LastName])'

The " & " just before the LAST function is highlighted.

I hope this reply does not post twice as I encounter a problem

posting
the

first time.

Rlg


:

Dear RLG:

While it is possible to do what you say, it is very probable that

it
won't

work out very well. The problem will be that data will not likely be
entered with sufficient consistency. I cannot advise you of what

to
do with

this, so I'll ignore it for the time.

With that said, it appears you have three sets of data to merge.

One
set

will have matching values in Last Name, Address, City, State,
ZipCode, and
Home Phone, with only differing first names. The next set matches
all the
above except for Last Name. The third set matches none of these things.

Now, I'm not sure what you would want to do if there were a 3 way match
within either of these sets. Your example shows what to do only
where there
is a 2 way match.

First, here is a way to identify the 2 way matches including Last Name:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last Name]
AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Some notes about this portion:

- If any of the columns do not match perfectly, the process will
fail. If
one Address is "123 Pine" and another is "123 Pine St" then they

will
not

match. If a space, paren, or hyphen in the Phone is missing or misplaced,
they will not match. This will require extreme precision, nearly
perfection, in the data entry or it will fail.

- If there are 3 persons with the same LastName and the other common
columns, they will not be included. You need to decide what you

want
to do

with these before programming.

- There is no way to distinguish male and female in this, so

putting
the

males name first will not be possible. I have put the

alphabetically
first

name first.

For the second set:

SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Similar notes apply to this portion.

Finally, for the unrepeated persons:

SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

After making sure these work correctly, take them and make a single query
out of them, putting UNION ALL between them:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last Name]
AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

Perhaps this is close. I'm not sure it's going to do you much

good,
but

there it is.

A note about consistency in typing. You have not consistently represented
the names of your columns. For example, some times you have

LastName
and

some times Last Name. The point is, if it is not so easy for you

to
be

consistent about something that really has only a single correct technical
spelling, how can you expect users to type in street names with consistency.
It is probably unlikely, unless you start with a database of all correct
street names in each city, and them limit users to selecting the street
names from that list.

Rigorously limiting choices like this is the proven way of getting
consistent data, which can then be used to perform functions like
that which
you desire with a high degree of success.

Tom Ellison


I'm using the following sql to create a list of customer to

create
mailing

labels. However, some of the rows have duplicate addresses. I
like to
create one row and concatenate the First and Last name of the duplicate
rows

SELECT DISTINCT tblContacts.FirstName, tblContacts.LastName,
tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode, tblContacts.HomePhone, tblContacts.ContactID,
[FirstName] & " " & [LastNAme] AS expr1
FROM tblContacts INNER JOIN tblPolicy ON tblContacts.ContactID =
tblPolicy.ContactID
WHERE (((tblPolicy.Status)="Active"))
ORDER BY tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode;

Sample Data:

First Name Last Name Address City State ZipCode Home Phone
Bob Smith 123 Pine Help Mo 63333
(123)
123-1234
Mary Smith 123 Pine Help Mo 63333 (123)
123-1234
Joan Lee 456 Oak June Mo 64545
(343)
323-4343
Betty Jones 456 Oak June Mo 64545 (343)
323-4343

Here's the results I'd like to achive:

CombinedName Address City State ZipCode Home Phone
Bob & Mary Smith 123 Pine Help Mo 63333 (123) 123-1234
Joan Lee & Betty Jones 456 Oak June Mo 64545 (343) 323-4343

Is this possible using SQL or will I need to create VBA code to achive?

 

[John Spencer]

Dear Tom,

Since First and Last are aggregate functions, I thought they were required
to be in the having clause. Actually, I was surprised that the OP didn't
complain about a syntax error.

I've tried a simple query (Access 2000) with First(ID) in a where clause and
got an error message of "Cannot have an aggregate function in WHERE clause
...."

John Spencer

Dear John:

I do not put ordinary filters in a HAVING clause, although I'm aware that
Jet moves them there. I believe either is acceptable.

Tom Ellison

Tom and rlg,

Pardon me, but I think First and Last are aggregate functions (just like Max
and Min) and as such would need to be in the Having clause instead of the
Where clause of the query. I could be mistaken of course (just ask my
wife).

SELECT FIRST([First Name] & " " & [Last Name]) & " " &
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2 AND FIRST([Last Name]) <> LAST([Last Name])

Dear rlg:

I ventured somewhat into uncharted territory. I expect it needs to be:

SELECT FIRST(FirstName) & " " FIRST(LastName) & " & " &
LAST(FirstName) & " " LAST(LastName) AS CombinedName

I expect this will avoid problems. But it doesn't explain how it is
you
have no results.

Can you find a pair of rows that should meet this condition? Please post
the data for that pair.

Tom Ellison


Tom: I resolved the syntax error: I had to add the & before and after the
" &
". The change to the select statement is below:

SELECT FIRST ([FirstName] & " " & [LastName]) & " & " & LAST ([FirstName]
&
" "
& [LastName]) AS CombinedName,
Address, City, State, ZipCode, [HomePhone]

I ran it and it dod not return any rows. I'm investigating now. But at
least I got the sql to run. Thanks again.

Rlg

:

Tom, thanks for the reply. I was able to get 2 of the 3 sql's to run.
The
one I'm having prolems with is:

SELECT FIRST ([FirstName] & " " & [LastName]) " & " LAST ([FirstName]
&
" "
& [LastName]) AS CombinedName,
Address, City, State, ZipCode, [HomePhone]
FROM tblContacts
WHERE FIRST([LastName]) <> LAST([LastName])
GROUP BY Address, City, State, ZipCode, [HomePhone]
HAVING COUNT(*) = 2

I'm receiving the following syntax error:

Syntax error (missing operator) in query expression 'FIRST ([FirstName]
& "
" & [LastName]) " & " LAST ([FirstName] & " " & [LastName])'

The " & " just before the LAST function is highlighted.

I hope this reply does not post twice as I encounter a problem posting
the
first time.

Rlg


:

Dear RLG:

While it is possible to do what you say, it is very probable that it
won't
work out very well. The problem will be that data will not likely be
entered with sufficient consistency. I cannot advise you of what to
do with
this, so I'll ignore it for the time.

With that said, it appears you have three sets of data to merge. One
set
will have matching values in Last Name, Address, City, State,
ZipCode,
and
Home Phone, with only differing first names. The next set matches
all
the
above except for Last Name. The third set matches none of these
things.

Now, I'm not sure what you would want to do if there were a 3 way
match
within either of these sets. Your example shows what to do only
where
there
is a 2 way match.

First, here is a way to identify the 2 way matches including Last
Name:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last
Name]
AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home
Phone]
HAVING COUNT(*) = 2

Some notes about this portion:

- If any of the columns do not match perfectly, the process will
fail.
If
one Address is "123 Pine" and another is "123 Pine St" then they will
not
match. If a space, paren, or hyphen in the Phone is missing or
misplaced,
they will not match. This will require extreme precision, nearly
perfection, in the data entry or it will fail.

- If there are 3 persons with the same LastName and the other common
columns, they will not be included. You need to decide what you want
to do
with these before programming.

- There is no way to distinguish male and female in this, so putting
the
males name first will not be possible. I have put the alphabetically
first
name first.

For the second set:

SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Similar notes apply to this portion.

Finally, for the unrepeated persons:

SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

After making sure these work correctly, take them and make a
single
query
out of them, putting UNION ALL between them:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last
Name]
AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home
Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

Perhaps this is close. I'm not sure it's going to do you much good,
but
there it is.

A note about consistency in typing. You have not consistently
represented
the names of your columns. For example, some times you have LastName
and
some times Last Name. The point is, if it is not so easy for you to
be
consistent about something that really has only a single correct
technical
spelling, how can you expect users to type in street names with
consistency.
It is probably unlikely, unless you start with a database of all
correct
street names in each city, and them limit users to selecting the
street
names from that list.

Rigorously limiting choices like this is the proven way of getting
consistent data, which can then be used to perform functions like
that
which
you desire with a high degree of success.

Tom Ellison


I'm using the following sql to create a list of customer to create
mailing
labels. However, some of the rows have duplicate addresses. I
like
to
create one row and concatenate the First and Last name of the
duplicate
rows

SELECT DISTINCT tblContacts.FirstName, tblContacts.LastName,
tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode, tblContacts.HomePhone, tblContacts.ContactID,
[FirstName] & " " & [LastNAme] AS expr1
FROM tblContacts INNER JOIN tblPolicy ON tblContacts.ContactID =
tblPolicy.ContactID
WHERE (((tblPolicy.Status)="Active"))
ORDER BY tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode;

Sample Data:

First Name Last Name Address City State ZipCode Home Phone
Bob Smith 123 Pine Help Mo 63333
(123)
123-1234
Mary Smith 123 Pine Help Mo 63333 (123)
123-1234
Joan Lee 456 Oak June Mo 64545
(343)
323-4343
Betty Jones 456 Oak June Mo 64545 (343)
323-4343

Here's the results I'd like to achive:

CombinedName Address City State ZipCode Home
Phone
Bob & Mary Smith 123 Pine Help Mo 63333
(123)
123-1234
Joan Lee & Betty Jones 456 Oak June Mo 64545 (343)
323-4343

Is this possible using SQL or will I need to create VBA code to
achive?

 

[Tom Ellison]

Dear John:

Well, then, probably you are correct. Thanks!

Tom Ellison

Dear Tom,

Since First and Last are aggregate functions, I thought they were required
to be in the having clause. Actually, I was surprised that the OP didn't
complain about a syntax error.

I've tried a simple query (Access 2000) with First(ID) in a where clause and
got an error message of "Cannot have an aggregate function in WHERE clause
..."

John Spencer

Dear John:

I do not put ordinary filters in a HAVING clause, although I'm aware that
Jet moves them there. I believe either is acceptable.

Tom Ellison

Tom and rlg,

Pardon me, but I think First and Last are aggregate functions (just

like
Max

and Min) and as such would need to be in the Having clause instead of the
Where clause of the query. I could be mistaken of course (just ask my
wife).

SELECT FIRST([First Name] & " " & [Last Name]) & " " &
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2 AND FIRST([Last Name]) <> LAST([Last Name])

Dear rlg:

I ventured somewhat into uncharted territory. I expect it needs to be:

SELECT FIRST(FirstName) & " " FIRST(LastName) & " & " &
LAST(FirstName) & " " LAST(LastName) AS CombinedName

I expect this will avoid problems. But it doesn't explain how it is
you
have no results.

Can you find a pair of rows that should meet this condition? Please post
the data for that pair.

Tom Ellison


Tom: I resolved the syntax error: I had to add the & before and

after
the

" &
". The change to the select statement is below:

SELECT FIRST ([FirstName] & " " & [LastName]) & " & " & LAST ([FirstName]
&
" "
& [LastName]) AS CombinedName,
Address, City, State, ZipCode, [HomePhone]

I ran it and it dod not return any rows. I'm investigating now.

But
at

least I got the sql to run. Thanks again.

Rlg

:

Tom, thanks for the reply. I was able to get 2 of the 3 sql's to run.
The
one I'm having prolems with is:

SELECT FIRST ([FirstName] & " " & [LastName]) " & " LAST ([FirstName]
&
" "
& [LastName]) AS CombinedName,
Address, City, State, ZipCode, [HomePhone]
FROM tblContacts
WHERE FIRST([LastName]) <> LAST([LastName])
GROUP BY Address, City, State, ZipCode, [HomePhone]
HAVING COUNT(*) = 2

I'm receiving the following syntax error:

Syntax error (missing operator) in query expression 'FIRST ([FirstName]
& "
" & [LastName]) " & " LAST ([FirstName] & " " & [LastName])'

The " & " just before the LAST function is highlighted.

I hope this reply does not post twice as I encounter a problem posting
the
first time.

Rlg


:

Dear RLG:

While it is possible to do what you say, it is very probable

that
it

won't
work out very well. The problem will be that data will not

likely
be

entered with sufficient consistency. I cannot advise you of

what
to

do with
this, so I'll ignore it for the time.

With that said, it appears you have three sets of data to merge. One
set
will have matching values in Last Name, Address, City, State,
ZipCode,
and
Home Phone, with only differing first names. The next set matches
all
the
above except for Last Name. The third set matches none of these
things.

Now, I'm not sure what you would want to do if there were a 3 way
match
within either of these sets. Your example shows what to do only
where
there
is a 2 way match.

First, here is a way to identify the 2 way matches including Last
Name:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last
Name]
AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home
Phone]
HAVING COUNT(*) = 2

Some notes about this portion:

- If any of the columns do not match perfectly, the process will
fail.
If
one Address is "123 Pine" and another is "123 Pine St" then they will
not
match. If a space, paren, or hyphen in the Phone is missing or
misplaced,
they will not match. This will require extreme precision, nearly
perfection, in the data entry or it will fail.

- If there are 3 persons with the same LastName and the other common
columns, they will not be included. You need to decide what you want
to do
with these before programming.

- There is no way to distinguish male and female in this, so putting
the
males name first will not be possible. I have put the alphabetically
first
name first.

For the second set:

SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2

Similar notes apply to this portion.

Finally, for the unrepeated persons:

SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

After making sure these work correctly, take them and make a
single
query
out of them, putting UNION ALL between them:

SELECT MIN([First Name]) & " & " & MAX([First Name]) & [Last
Name]
AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY [Last Name], Address, City, State, ZipCode, [Home
Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name] & " " & [Last Name]) " & "
LAST([First Name] & " " & [Last Name]) AS CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
WHERE FIRST([Last Name]) <> LAST([Last Name])
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 2
UNION ALL
SELECT FIRST([First Name]) & " " & FIRST([Last Name]) AS
CombinedName,
Address, City, State, ZipCode, [Home Phone]
FROM tblContacts
GROUP BY Address, City, State, ZipCode, [Home Phone]
HAVING COUNT(*) = 1

Perhaps this is close. I'm not sure it's going to do you much good,
but
there it is.

A note about consistency in typing. You have not consistently
represented
the names of your columns. For example, some times you have LastName
and
some times Last Name. The point is, if it is not so easy for

you
to

be
consistent about something that really has only a single correct
technical
spelling, how can you expect users to type in street names with
consistency.
It is probably unlikely, unless you start with a database of all
correct
street names in each city, and them limit users to selecting the
street
names from that list.

Rigorously limiting choices like this is the proven way of getting
consistent data, which can then be used to perform functions like
that
which
you desire with a high degree of success.

Tom Ellison


I'm using the following sql to create a list of customer to create
mailing
labels. However, some of the rows have duplicate addresses. I
like
to
create one row and concatenate the First and Last name of the
duplicate
rows

SELECT DISTINCT tblContacts.FirstName, tblContacts.LastName,
tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode, tblContacts.HomePhone, tblContacts.ContactID,
[FirstName] & " " & [LastNAme] AS expr1
FROM tblContacts INNER JOIN tblPolicy ON tblContacts.ContactID =
tblPolicy.ContactID
WHERE (((tblPolicy.Status)="Active"))
ORDER BY tblContacts.Address, tblContacts.City, tblContacts.State,
tblContacts.ZipCode;

Sample Data:

First Name Last Name Address City State ZipCode Home Phone
Bob Smith 123 Pine Help Mo 63333
(123)
123-1234
Mary Smith 123 Pine Help Mo 63333 (123)
123-1234
Joan Lee 456 Oak June Mo 64545
(343)
323-4343
Betty Jones 456 Oak June Mo 64545 (343)
323-4343

Here's the results I'd like to achive:

CombinedName Address City State ZipCode Home
Phone
Bob & Mary Smith 123 Pine Help Mo 63333
(123)
123-1234
Joan Lee & Betty Jones 456 Oak June Mo 64545 (343)
323-4343

Is this possible using SQL or will I need to create VBA code to
achive?