Can you concatenate recordset values in one field?

[tim-p]

Hi I have an Access db which stores, amongst other things, details of small
mailing lists, people who have received a particular mailing. Because it is
Access, and therefore no stored procedures to do the insert, the identities of
the people who have received a mailing are held in a memo field as a comma
delimeted string. e.g. ListID Date MailingList 1
01/04/2005 3, 7, 99, 107 2 13/04/2005 2, 15,
27, 136 etc. It is easy enough to extract the recipients from one
particular list: select one listID, set the field Mailing list as a variable:
varMailingList2 = (rsDraftList.Fields.Item('MailingList').Value) then select
from the people table all those peopleIDs who are in varMailingList2: SELECT *
FROM qryCampaigns_maillist WHERE RoleID IN (' & varMailingList2 & ')
However, I now need to find all people contacted from any list between two
dates. Therefore my method above falls over because there could be one or more
records in recordset rsDraftList (or indeed none). Anyone have any thoughts on
the easiest way to do this? Can you concatenate instances in a recordset?
Thanks in advance for any suggestions.

[Paul Whitham TMM]

Why are you storing all of these values in a single field. Wouldn't it be
easier to use separate fields for each element, then your searching would be
a lot simpler to do

--
Regards

Paul Whitham
Macromedia Certified Professional for Dreamweaver MX2004
Valleybiz Internet Design
[url]www.valleybiz.net[/url]

Team Macromedia Volunteer for Ultradev/Dreamweaver MX
[url]www.macromedia.com/support/forums/team_macromedia[/url]

"tim-p" <webforumsuser@macromedia.com> wrote in message
news:d30p88$308$1@forums.macromedia.com...

> Hi I have an Access db which stores, amongst other things, details of

small

> mailing lists, people who have received a particular mailing. Because it

is

> Access, and therefore no stored procedures to do the insert, the

identities of

> the people who have received a mailing are held in a memo field as a comma
> delimeted string. e.g. ListID Date MailingList

1

> 01/04/2005 3, 7, 99, 107 2 13/04/2005 2,

15,

> 27, 136 etc. It is easy enough to extract the recipients from one
> particular list: select one listID, set the field Mailing list as a

variable:

> varMailingList2 = (rsDraftList.Fields.Item('MailingList').Value) then

select

> from the people table all those peopleIDs who are in varMailingList2:

SELECT *

> FROM qryCampaigns_maillist WHERE RoleID IN (' &amp; varMailingList2 &amp;

')

> However, I now need to find all people contacted from any list between two
> dates. Therefore my method above falls over because there could be one or

more

> records in recordset rsDraftList (or indeed none). Anyone have any

thoughts on

> the easiest way to do this? Can you concatenate instances in a recordset?
> Thanks in advance for any suggestions.
>

[tim-p]

Hi Paul Thanks for the reply. I went round the houses in terms of how the
data should be stored on this one. The process that the user goes through to
select the list has governed how the data has to be stored: - The user is a
marketing agency who use this as their new business prospecting system. They
have a number of search criteria on a search form, which allows them to choose
people who work in certain industries, have a certain job responsibility and so
on. - The search results page may therefore contain none, one or more results
(sometimes up to hundreds). Next to each name is a check box, default checked,
so they can uncheck people that, for whatever reason, they don't want to mail.
This search results page is also a form. - Once they are happy with who they
are mailing, they submit the form which creates a new mailing list in the form
of an excel spreadsheet and makes the database insert to record that the
propspect was sent the mailing. In an ideal world (ie. using SQL Server) I
would have used an SP to do the insert and inserted ListID and ProspectID as
one instance per row. However, as far as I know, there is no way of doing this
with Access except by using the Command object, but this only works if you know
the total number of records to be inserted (and in practical terms is limited
to a few dozen inserts). So the way I implemented it is to attach the
ProspectID to the checkbox value and insert the value of all checked checkboxes
into a memo field. Subsequent pages which retrieve the names of recipients of
the mailing then have to parse the memo text, extracting the appropriate IDs.
Not ideal, but the best workaround I could come up with. This is easy enough
when you just want to find out who received one mailing, but becomes less tidy
when you want to know which mailing(s) one person received out of many. So I
also can't use a separate field in the mailing list table for each recipient
(recipient1, recipient2 etc.) because there could be up to hundreds of
recipients. Hope this makes sense!

[Paul Whitham TMM]

If all you need to track each time is which customers received a mailing
then what I would do is have a master table holding all their information,
and a second table that simply had three fields namely

Mailing ID - autonumber
CustomerID - link to the customers table
MailDate - date of the mailing.

Then if you wished to see which mailing a customer had it is a simple matter
of querying this table.

--
Regards

Paul Whitham
Macromedia Certified Professional for Dreamweaver MX2004
Valleybiz Internet Design
[url]www.valleybiz.net[/url]

Team Macromedia Volunteer for Ultradev/Dreamweaver MX
[url]www.macromedia.com/support/forums/team_macromedia[/url]

"tim-p" <webforumsuser@macromedia.com> wrote in message
news:d32rgl$41h$1@forums.macromedia.com...

> Hi Paul Thanks for the reply. I went round the houses in terms of how

the

> data should be stored on this one. The process that the user goes through

to

> select the list has governed how the data has to be stored: - The user is

a

> marketing agency who use this as their new business prospecting system.

They

> have a number of search criteria on a search form, which allows them to

choose

> people who work in certain industries, have a certain job responsibility

and so

> on. - The search results page may therefore contain none, one or more

results

> (sometimes up to hundreds). Next to each name is a check box, default

checked,

> so they can uncheck people that, for whatever reason, they don't want to

mail.

> This search results page is also a form. - Once they are happy with who

they

> are mailing, they submit the form which creates a new mailing list in the

form

> of an excel spreadsheet and makes the database insert to record that the
> propspect was sent the mailing. In an ideal world (ie. using SQL Server)

I

> would have used an SP to do the insert and inserted ListID and ProspectID

as

> one instance per row. However, as far as I know, there is no way of doing

this

> with Access except by using the Command object, but this only works if you

know

> the total number of records to be inserted (and in practical terms is

limited

> to a few dozen inserts). So the way I implemented it is to attach the
> ProspectID to the checkbox value and insert the value of all checked

checkboxes

> into a memo field. Subsequent pages which retrieve the names of recipients

of

> the mailing then have to parse the memo text, extracting the appropriate

IDs.

> Not ideal, but the best workaround I could come up with. This is easy

enough

> when you just want to find out who received one mailing, but becomes less

tidy

> when you want to know which mailing(s) one person received out of many.

So I

> also can't use a separate field in the mailing list table for each

recipient

> (recipient1, recipient2 etc.) because there could be up to hundreds of
> recipients. Hope this makes sense!
>

[tim-p]

Hi Paul Thanks for the reply. Yes, what you are saying is similar to what I
described abouve as my ideal structure. Ideally I would have a table with the
mail shot details and a table holding all mail recipients: tblMailing:
MailingID (autonumber), DateCreated, MailingName + some other details
tblMaillist: MailingID, RecipientID tblMailList would then contain all
recipients of all mailshots in one table, dead easy to search. Easily
achievable in SQL Server because your insert into tblMailList can be done by
stored procedure. But as far as I know there is no way of doing this insert
in Access. The number of rows in this table to be inserted is variable,
generated from a repeat region, could be anything from 1 to thousands. If you
know the number of rows you can do it with the MM Command object, but even then
only for a small number of records. Do you know of any way to do this
multi-line insert with Access? Regards TP

[tim-p]

Hi

Having finally sorted this out I thought I'd better post back the answer,
worked out after looking at many other posts in other forums.

I did succeed in making my asp page insert multiple records inAccess, but
using the command object and making the insert behaviour into a loop.

What I did was this:

My search results are in a repeat region, which itself is inside a form. There
is a hidden field which has the value
<%=rsRecordsetName.Fields.Item("fieldname").value% > -i.e. because it is in the
repeat region each record in that region returns its own value to the form
element. The form does not insert into the database itself, but instead is set
to have the page which does the insert as its target. If you were to do an
insert from this first page it would insert a comma delimeted string into the
target db field corresponding to that form element.

It is page two which does the insert. I set up a command object to do the
insert using the standard DW object, mapping each form field onto its
corresponding db field. This produces code like this:

<%

if(Request.Form("RoleID") <> "") then Command1__varRole =
Request.Form("RoleID")

if(Request.Form("CampID") <> "") then Command1__varCamp =
Request.Form("CampID")

if(Request.Form("DraftList") <> "") then Command1__varDraftList =
Request.Form("DraftList")

%>
<%

set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_connMomentaDB_STRING
Command1.CommandText = "INSERT INTO tblxCamp_X_Role_X_DraftList (RoleID,
CampID, DraftList) VALUES (" + Replace(Command1__varRole, "'", "''") + ", " +
Replace(Command1__varCamp, "'", "''") + ", " + Replace(Command1__varDraftList,
"'", "''") + " ) "
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()

%>

Then you modify the standard behaviour to loop through the multi-instance
field until it has finished.

My code:

<%

if(Request.Form("RoleID") <> "") then Command1__varRole =
Request.Form("RoleID")

if(Request.Form("CampID") <> "") then Command1__varCamp =
Request.Form("CampID")

if(Request.Form("DraftList") <> "") then Command1__varDraftList =
Request.Form("DraftList")

%>
<%
Dim allRoles
allRoles = Split(Command1__varRole,", ")
'This takes the comma-separated list and makes an array
For Each X In allRoles
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_connMomentaDB_STRING
Command1.CommandText = "INSERT INTO tblxCamp_X_Role_X_DraftList (RoleID,
CampID, DraftList) VALUES (" + Replace(X, "'", "''") + ", " +
Replace(Command1__varCamp, "'", "''") + ", " + Replace(Command1__varDraftList,
"'", "''") + " ) "
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = false
Command1.Execute()
set Command1 = nothing
Next
%>

This splits the field that contains the multiple entries separated by commas
and inserts the spilt string items until it has looped through them all. It
also inserts two other fields, which as the same for all records.

Note that you can't use this method to split two different strings.

Hope this helps someone in the future.

TP