SQL to COMBINE RECORDS

[Guest]

I have a table that is imported from Excel that I use to keep track of
customers and their email addresses. I then made a query from that table in
an attempt to combine all the email addresses together into one record. The
query is called Query1 and the field is EmailAddress. Each email address in
the table has a semicolon behind it. I am trying to get the effect of
"(e-mail address removed);[email protected];[email protected]" Instead I get the following
(e-mail address removed);[email protected];
(e-mail address removed);[email protected];
(e-mail address removed);[email protected];

My SQL is the following:

SELECT [CustomerInputTable.EmailAddress] & [CustomerInputTable.EmailAddress]
AS Expr1
FROM CustomerInputTable;

 

[Dirk Goldgar]

In

I have a table that is imported from Excel that I use to keep track of
customers and their email addresses. I then made a query from that
table in an attempt to combine all the email addresses together into
one record. The query is called Query1 and the field is EmailAddress.
Each email address in the table has a semicolon behind it. I am
trying to get the effect of
"(e-mail address removed);[email protected];[email protected]" Instead I get the
following (e-mail address removed);[email protected];
(e-mail address removed);[email protected];
(e-mail address removed);[email protected];

My SQL is the following:

SELECT [CustomerInputTable.EmailAddress] &
[CustomerInputTable.EmailAddress] AS Expr1
FROM CustomerInputTable;

You can't do that with SQL alone. You need to open a recordset on the
query, loop through the records and append each email address to a
string, and then close the recordset and do whatever you want to do with
that string.

I posted the following code recently in a response to a similar
question. It doesn't expect there to be a trailing semicolon on each
email address field, so it inserts one. Other than that, it's similar
to what you would need to do:

'----- start of example code -----

Dim rsRecips As DAO.Recordset
Dim strTo As String

Set rsRecips = CurrentDb.OpenRecordset("YourQuery")
With rsRecips
Do Until .EOF
If Len(!EmailAddress & vbNullString) > 0 Then
strTo = strTo & ";" & !EmailAddress
End If
.MoveNext
Loop
.Close
End With

If Len(strTo) > 0 Then

strTo = Mid$(strTo, 2) ' drop leading ";"

DoCmd.SendObject To:=strTo, _
' ... rest of SendObject arguments needes here'

End If

'----- end of example code -----

 

[Guest]

What is it you are trying to achieve here? Are you trying to combine all the
email addresses per customer into a single row in a table e.g. have all
contacts per customer together in one row? Having multiple values in one
column in one row of a table means the table would not be normalized as First
Normal Form requires values to be atomic, i.e. each column position in each
row of a table can contain only one value.

Where you have multiple values for one instance of a particular entity type
you should put these in separate rows in a related table. So you might have
a table Customers with a primary key CustomerID and columns for the
attributes of the customers entity type such as the customer name, address
etc. You can then have a table EmailAddresses say with columns CustomerID
and EmailAddress. In this case CustomerID is a foreign key referencing the
primary key of Customers. For data input you might use a Customers form with
an EmailAddresses subform within it, linking them on the CustomerID columns.
For a report you could use a report/subreport in the same way, but you'd be
more likely to join the tables in a query and base the report on the query,
grouping the report by customer.

If at present you have a table with multiple rows per customer its
relatively simple to 'decompose' it into related tables like so: Firstly
create the empty tables, using an autonumber column as the CustomerID primary
key column in Customers. Then append one row per customer from the original
table by means of the SELECT DISTINCT option in the append query. Then join
the original table to the new Customers table on one or more columns which
uniquely identify each customer in the original table, e.g. the customer name
column(s), plus the address column(s) if more then one customer has the same
name, and append the CustomerID from Customers and the EmailAddress columns
from the original table into the new EmailAddresses table.

The above might not accurately reflect your scenario, but hopefully
illustrates the principles involved.

Ken Sheridan
Stafford, England

I have a table that is imported from Excel that I use to keep track of
customers and their email addresses. I then made a query from that table in
an attempt to combine all the email addresses together into one record. The
query is called Query1 and the field is EmailAddress. Each email address in
the table has a semicolon behind it. I am trying to get the effect of
"(e-mail address removed);[email protected];[email protected]" Instead I get the following
(e-mail address removed);[email protected];
(e-mail address removed);[email protected];
(e-mail address removed);[email protected];

My SQL is the following:

SELECT [CustomerInputTable.EmailAddress] & [CustomerInputTable.EmailAddress]
AS Expr1
FROM CustomerInputTable;

 

[Guest]

Dirk, I am fairly new to this, but I am trying. The sample code you provided
should be associated with what. I am not for sure exactly how to use
recordsets. Would this be used in conjunction with a Form, another query,
table....Just not sure. I understand what the code is doing, I just am not
sure where to put it.

Thanks,

In

I have a table that is imported from Excel that I use to keep track of
customers and their email addresses. I then made a query from that
table in an attempt to combine all the email addresses together into
one record. The query is called Query1 and the field is EmailAddress.
Each email address in the table has a semicolon behind it. I am
trying to get the effect of
"(e-mail address removed);[email protected];[email protected]" Instead I get the
following (e-mail address removed);[email protected];
(e-mail address removed);[email protected];
(e-mail address removed);[email protected];

My SQL is the following:

SELECT [CustomerInputTable.EmailAddress] &
[CustomerInputTable.EmailAddress] AS Expr1
FROM CustomerInputTable;

You can't do that with SQL alone. You need to open a recordset on the
query, loop through the records and append each email address to a
string, and then close the recordset and do whatever you want to do with
that string.

I posted the following code recently in a response to a similar
question. It doesn't expect there to be a trailing semicolon on each
email address field, so it inserts one. Other than that, it's similar
to what you would need to do:

'----- start of example code -----

Dim rsRecips As DAO.Recordset
Dim strTo As String

Set rsRecips = CurrentDb.OpenRecordset("YourQuery")
With rsRecips
Do Until .EOF
If Len(!EmailAddress & vbNullString) > 0 Then
strTo = strTo & ";" & !EmailAddress
End If
.MoveNext
Loop
.Close
End With

If Len(strTo) > 0 Then

strTo = Mid$(strTo, 2) ' drop leading ";"

DoCmd.SendObject To:=strTo, _
' ... rest of SendObject arguments needes here'

End If

'----- end of example code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

Ken, thanks for replying. What I am actually trying to do is take an Excel
sheet with multiple customers, each with their own email address. I am trying
to take each of the email addresses and combine them into one record so they
will "run together." At that point I could make that a Hyperlink and with one
click email all customers at one time. I thought maybe I could use the "&"
symbol to combine the addresses as you sould somthing like Soft "&" ware =
software. Hope that helps explain more what I am trying to accomplish.

Thanks

What is it you are trying to achieve here? Are you trying to combine all the
email addresses per customer into a single row in a table e.g. have all
contacts per customer together in one row? Having multiple values in one
column in one row of a table means the table would not be normalized as First
Normal Form requires values to be atomic, i.e. each column position in each
row of a table can contain only one value.

Where you have multiple values for one instance of a particular entity type
you should put these in separate rows in a related table. So you might have
a table Customers with a primary key CustomerID and columns for the
attributes of the customers entity type such as the customer name, address
etc. You can then have a table EmailAddresses say with columns CustomerID
and EmailAddress. In this case CustomerID is a foreign key referencing the
primary key of Customers. For data input you might use a Customers form with
an EmailAddresses subform within it, linking them on the CustomerID columns.
For a report you could use a report/subreport in the same way, but you'd be
more likely to join the tables in a query and base the report on the query,
grouping the report by customer.

If at present you have a table with multiple rows per customer its
relatively simple to 'decompose' it into related tables like so: Firstly
create the empty tables, using an autonumber column as the CustomerID primary
key column in Customers. Then append one row per customer from the original
table by means of the SELECT DISTINCT option in the append query. Then join
the original table to the new Customers table on one or more columns which
uniquely identify each customer in the original table, e.g. the customer name
column(s), plus the address column(s) if more then one customer has the same
name, and append the CustomerID from Customers and the EmailAddress columns
from the original table into the new EmailAddresses table.

The above might not accurately reflect your scenario, but hopefully
illustrates the principles involved.

Ken Sheridan
Stafford, England

I have a table that is imported from Excel that I use to keep track of
customers and their email addresses. I then made a query from that table in
an attempt to combine all the email addresses together into one record. The
query is called Query1 and the field is EmailAddress. Each email address in
the table has a semicolon behind it. I am trying to get the effect of
"(e-mail address removed);[email protected];[email protected]" Instead I get the following
(e-mail address removed);[email protected];
(e-mail address removed);[email protected];
(e-mail address removed);[email protected];

My SQL is the following:

SELECT [CustomerInputTable.EmailAddress] & [CustomerInputTable.EmailAddress]
AS Expr1
FROM CustomerInputTable;

 

[Dirk Goldgar]

In

Dirk, I am fairly new to this, but I am trying. The sample code you
provided should be associated with what. I am not for sure exactly
how to use recordsets. Would this be used in conjunction with a Form,
another query, table....Just not sure. I understand what the code is
doing, I just am not sure where to put it.

It all depends on what it is you want to do. It seems to me most likely
that you want to be able to send an e-mail to all your customers. To do
that, in a basic way, you could put the code in the Click event
procedure for a command button on a form. Add the button to the form,
give it a name, choose "[Event Procedure]" for its On Click property,
and then click the build button at the end of the property line (the
button's caption is "..."). That will open the VB Editor to the shell
of a Click event procedure looking something like this:

Private Sub YourButtonName_Click()

End Sub

You would then fill in the code I posted between the "Private Sub" and
"End Sub" lines, so it looks something like this:

'------ begin example code ------
Private Sub YourButtonName_Click()

Dim rsRecips As DAO.Recordset
Dim strTo As String

Set rsRecips = CurrentDb.OpenRecordset("YourCustomerTable")
With rsRecips
Do Until .EOF
If Len(!EmailAddress & vbNullString) > 0 Then
strTo = strTo & ";" & !EmailAddress
End If
.MoveNext
Loop
.Close
End With

If Len(strTo) > 0 Then
strTo = Mid$(strTo, 2) ' drop leading ";"

DoCmd.SendObject _
To:=strTo, _
EditMessage:=True

End If

End Sub
'------ end example code ------

In the above, you would replace "YourButtonName" with the name of your
command button, "YourCustomerTable" with the name (in quotes) of the
table that contains your customers' email addresses, and replace
"EmailAddress" (not in quotes) with the name of the e-mail address field
in that table.

 

[Guest]

Dirk, I did as you said below and received a "compile error."
rsRecips As DAO.Recordset, this is highlighted with the error reading,
"User-defined type not defined." Anymore help would be greatly appreciated.

Thanks in advance,

In

I have a table that is imported from Excel that I use to keep track of
customers and their email addresses. I then made a query from that
table in an attempt to combine all the email addresses together into
one record. The query is called Query1 and the field is EmailAddress.
Each email address in the table has a semicolon behind it. I am
trying to get the effect of
"(e-mail address removed);[email protected];[email protected]" Instead I get the
following (e-mail address removed);[email protected];
(e-mail address removed);[email protected];
(e-mail address removed);[email protected];

My SQL is the following:

SELECT [CustomerInputTable.EmailAddress] &
[CustomerInputTable.EmailAddress] AS Expr1
FROM CustomerInputTable;

You can't do that with SQL alone. You need to open a recordset on the
query, loop through the records and append each email address to a
string, and then close the recordset and do whatever you want to do with
that string.

I posted the following code recently in a response to a similar
question. It doesn't expect there to be a trailing semicolon on each
email address field, so it inserts one. Other than that, it's similar
to what you would need to do:

'----- start of example code -----

Dim rsRecips As DAO.Recordset
Dim strTo As String

Set rsRecips = CurrentDb.OpenRecordset("YourQuery")
With rsRecips
Do Until .EOF
If Len(!EmailAddress & vbNullString) > 0 Then
strTo = strTo & ";" & !EmailAddress
End If
.MoveNext
Loop
.Close
End With

If Len(strTo) > 0 Then

strTo = Mid$(strTo, 2) ' drop leading ";"

DoCmd.SendObject To:=strTo, _
' ... rest of SendObject arguments needes here'

End If

'----- end of example code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

In

Dirk, I did as you said below and received a "compile error."
rsRecips As DAO.Recordset, this is highlighted with the error reading,
"User-defined type not defined." Anymore help would be greatly
appreciated.

You must be using Access 2000 or 2002. While in the VB Editor, click
menu items Tools -> References..., locate the entry for "Microsoft DAO
3.6 Object Library", and put a check mark in the box next to it. Then
close the References dialog and try compiling again.

 

[Guest]

For a more flexible solution you could have a multi-select list box on a
form, which would then enable you to select as few or as many customers to
email each time ( you can have a 'Select All' button on the form to save time
when emailing everybody). This would require a little bit of code to iterate
through the list box's ItemsSelected collection, but we can walk you through
creating that if you want to give it a try.

Ken Sheridan
Stafford, England

Ken, thanks for replying. What I am actually trying to do is take an Excel
sheet with multiple customers, each with their own email address. I am trying
to take each of the email addresses and combine them into one record so they
will "run together." At that point I could make that a Hyperlink and with one
click email all customers at one time. I thought maybe I could use the "&"
symbol to combine the addresses as you sould somthing like Soft "&" ware =
software. Hope that helps explain more what I am trying to accomplish.

Thanks

What is it you are trying to achieve here? Are you trying to combine all the
email addresses per customer into a single row in a table e.g. have all
contacts per customer together in one row? Having multiple values in one
column in one row of a table means the table would not be normalized as First
Normal Form requires values to be atomic, i.e. each column position in each
row of a table can contain only one value.

Where you have multiple values for one instance of a particular entity type
you should put these in separate rows in a related table. So you might have
a table Customers with a primary key CustomerID and columns for the
attributes of the customers entity type such as the customer name, address
etc. You can then have a table EmailAddresses say with columns CustomerID
and EmailAddress. In this case CustomerID is a foreign key referencing the
primary key of Customers. For data input you might use a Customers form with
an EmailAddresses subform within it, linking them on the CustomerID columns.
For a report you could use a report/subreport in the same way, but you'd be
more likely to join the tables in a query and base the report on the query,
grouping the report by customer.

If at present you have a table with multiple rows per customer its
relatively simple to 'decompose' it into related tables like so: Firstly
create the empty tables, using an autonumber column as the CustomerID primary
key column in Customers. Then append one row per customer from the original
table by means of the SELECT DISTINCT option in the append query. Then join
the original table to the new Customers table on one or more columns which
uniquely identify each customer in the original table, e.g. the customer name
column(s), plus the address column(s) if more then one customer has the same
name, and append the CustomerID from Customers and the EmailAddress columns
from the original table into the new EmailAddresses table.

The above might not accurately reflect your scenario, but hopefully
illustrates the principles involved.

Ken Sheridan
Stafford, England

I have a table that is imported from Excel that I use to keep track of
customers and their email addresses. I then made a query from that table in
an attempt to combine all the email addresses together into one record. The
query is called Query1 and the field is EmailAddress. Each email address in
the table has a semicolon behind it. I am trying to get the effect of
"(e-mail address removed);[email protected];[email protected]" Instead I get the following
(e-mail address removed);[email protected];
(e-mail address removed);[email protected];
(e-mail address removed);[email protected];

My SQL is the following:

SELECT [CustomerInputTable.EmailAddress] & [CustomerInputTable.EmailAddress]
AS Expr1
FROM CustomerInputTable;

 

[Guest]

Ken, that sounds Great, more of what I need and would like to do. I'm ready
whenever you are.

Thanks!

For a more flexible solution you could have a multi-select list box on a
form, which would then enable you to select as few or as many customers to
email each time ( you can have a 'Select All' button on the form to save time
when emailing everybody). This would require a little bit of code to iterate
through the list box's ItemsSelected collection, but we can walk you through
creating that if you want to give it a try.

Ken Sheridan
Stafford, England

Ken, thanks for replying. What I am actually trying to do is take an Excel
sheet with multiple customers, each with their own email address. I am trying
to take each of the email addresses and combine them into one record so they
will "run together." At that point I could make that a Hyperlink and with one
click email all customers at one time. I thought maybe I could use the "&"
symbol to combine the addresses as you sould somthing like Soft "&" ware =
software. Hope that helps explain more what I am trying to accomplish.

Thanks

What is it you are trying to achieve here? Are you trying to combine all the
email addresses per customer into a single row in a table e.g. have all
contacts per customer together in one row? Having multiple values in one
column in one row of a table means the table would not be normalized as First
Normal Form requires values to be atomic, i.e. each column position in each
row of a table can contain only one value.

Where you have multiple values for one instance of a particular entity type
you should put these in separate rows in a related table. So you might have
a table Customers with a primary key CustomerID and columns for the
attributes of the customers entity type such as the customer name, address
etc. You can then have a table EmailAddresses say with columns CustomerID
and EmailAddress. In this case CustomerID is a foreign key referencing the
primary key of Customers. For data input you might use a Customers form with
an EmailAddresses subform within it, linking them on the CustomerID columns.
For a report you could use a report/subreport in the same way, but you'd be
more likely to join the tables in a query and base the report on the query,
grouping the report by customer.

If at present you have a table with multiple rows per customer its
relatively simple to 'decompose' it into related tables like so: Firstly
create the empty tables, using an autonumber column as the CustomerID primary
key column in Customers. Then append one row per customer from the original
table by means of the SELECT DISTINCT option in the append query. Then join
the original table to the new Customers table on one or more columns which
uniquely identify each customer in the original table, e.g. the customer name
column(s), plus the address column(s) if more then one customer has the same
name, and append the CustomerID from Customers and the EmailAddress columns
from the original table into the new EmailAddresses table.

The above might not accurately reflect your scenario, but hopefully
illustrates the principles involved.

Ken Sheridan
Stafford, England

:

I have a table that is imported from Excel that I use to keep track of
customers and their email addresses. I then made a query from that table in
an attempt to combine all the email addresses together into one record. The
query is called Query1 and the field is EmailAddress. Each email address in
the table has a semicolon behind it. I am trying to get the effect of
"(e-mail address removed);[email protected];[email protected]" Instead I get the following
(e-mail address removed);[email protected];
(e-mail address removed);[email protected];
(e-mail address removed);[email protected];

My SQL is the following:

SELECT [CustomerInputTable.EmailAddress] & [CustomerInputTable.EmailAddress]
AS Expr1
FROM CustomerInputTable;

 

[Guest]

Create an unbound form and add a list box, lstCustomers, to it. Set the list
box's MultiSelect property to either Simple or Extended. The first means
that you can select multiple items by simple clicking one at a time, the
second allows you to use Ctrl+Click or Shift+Click in the standard way to
select multiple items or a contiguous range of items.

For the RowSource property of the list box use something like the following:

SELECT [EmailAddress], [CustomerName] FROM [CustomerInputTable] ORDER BY
[CustomerName];

Set other properties of the control as follows:

ColumnCount 2
BoundColumn 1
ColumnWidths 0cm; 8cm (or rough equivalent in inches. The important thing
is that the first dimension is zero to hide the EmailAddressColumn so that
only the CustomerName column will show in the list box. If you want to show
several columns in the list then include these in the RowSource and amend the
ColumnCount and ColumnWidths properties accordingly, experimenting with the
ColumnWidths dimensions to get the best fit, but keeping the first dimension
as zero)

If you want to enter the subject and message text for the email in the form
add text boxes txtSubject and txtMessage.

Add a command button to the form to open or send directly the email with
code as follows, which iterates through the ItemsSelected collection of the
list box and builds the list of addresses from the hidden first column:

Dim varItem As Variant
Dim strEmailAddressList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstCustomers

If ctrl.ItemsSelected.Count > 0 Then
' loop through selected items and build address list
For Each varItem In ctrl.ItemsSelected
strEmailAddressList = strEmailAddressList & ";" &
ctrl.ItemData(varItem)
Next varItem

' remove leading semi colon
strEmailAddressList = Mid(strEmailAddressList, 2)

' send to email application
DoCmd.SendObject _
To:=strEmailAddressList, _
Subject:=Me.txtSubject, _
MessageText:=Me.txtMessage, _
EditMessage:=True

Else
MsgBox "No customers selected", vbInformation, "Warning"
End If

The above will open the email in your email application ready for sending,
so you can edit it, format the text etc before doing so. If you want it sent
automatically immediately without the opportunity for any editing change the
EditMessage argument to False. If you haven't included text boxes for the
subject and message text in the form, but prefer to enter these in the email
application then omit the two lines with the Subject and MessageText
arguments from the above code.

If you want a 'Select All' button on the form put the following in its Click
event procedure:

Dim n As Integer

For n = 0 To Me.lstCustomers.ListCount - 1
Me.lstCustomers.Selected(n) = True
Next n

For a button to clear all selections from the list:

Dim n As Integer

For n = 0 To Me.lstCustomers.ListCount - 1
Me.lstCustomers.Selected(n) = False
Next n

Ken Sheridan
Stafford, England

Ken, that sounds Great, more of what I need and would like to do. I'm ready
whenever you are.

Thanks!

For a more flexible solution you could have a multi-select list box on a
form, which would then enable you to select as few or as many customers to
email each time ( you can have a 'Select All' button on the form to save time
when emailing everybody). This would require a little bit of code to iterate
through the list box's ItemsSelected collection, but we can walk you through
creating that if you want to give it a try.

Ken Sheridan
Stafford, England

Ken, thanks for replying. What I am actually trying to do is take an Excel
sheet with multiple customers, each with their own email address. I am trying
to take each of the email addresses and combine them into one record so they
will "run together." At that point I could make that a Hyperlink and with one
click email all customers at one time. I thought maybe I could use the "&"
symbol to combine the addresses as you sould somthing like Soft "&" ware =
software. Hope that helps explain more what I am trying to accomplish.

Thanks

:

What is it you are trying to achieve here? Are you trying to combine all the
email addresses per customer into a single row in a table e.g. have all
contacts per customer together in one row? Having multiple values in one
column in one row of a table means the table would not be normalized as First
Normal Form requires values to be atomic, i.e. each column position in each
row of a table can contain only one value.

Where you have multiple values for one instance of a particular entity type
you should put these in separate rows in a related table. So you might have
a table Customers with a primary key EmailAddress and columns for the
attributes of the customers entity type such as the customer name, address
etc. You can then have a table EmailAddresses say with columns EmailAddress
and EmailAddress. In this case EmailAddress is a foreign key referencing the
primary key of Customers. For data input you might use a Customers form with
an EmailAddresses subform within it, linking them on the EmailAddress columns.
For a report you could use a report/subreport in the same way, but you'd be
more likely to join the tables in a query and base the report on the query,
grouping the report by customer.

If at present you have a table with multiple rows per customer its
relatively simple to 'decompose' it into related tables like so: Firstly
create the empty tables, using an autonumber column as the EmailAddress primary
key column in Customers. Then append one row per customer from the original
table by means of the SELECT DISTINCT option in the append query. Then join
the original table to the new Customers table on one or more columns which
uniquely identify each customer in the original table, e.g. the customer name
column(s), plus the address column(s) if more then one customer has the same
name, and append the EmailAddress from Customers and the EmailAddress columns
from the original table into the new EmailAddresses table.

The above might not accurately reflect your scenario, but hopefully
illustrates the principles involved.

Ken Sheridan
Stafford, England

:

I have a table that is imported from Excel that I use to keep track of
customers and their email addresses. I then made a query from that table in
an attempt to combine all the email addresses together into one record. The
query is called Query1 and the field is EmailAddress. Each email address in
the table has a semicolon behind it. I am trying to get the effect of
"(e-mail address removed);[email protected];[email protected]" Instead I get the following
(e-mail address removed);[email protected];
(e-mail address removed);[email protected];
(e-mail address removed);[email protected];

My SQL is the following:

SELECT [CustomerInputTable.EmailAddress] & [CustomerInputTable.EmailAddress]
AS Expr1
FROM CustomerInputTable;