Finding records that match comma delimeted memo field

[timp]

Hi

I'm hoping someone can help a frustrated newbie with an Access sql conundrum.

I have a memo field in one table which contains a string of comma delimited numbers, which are the autonumber IDs of people who have met a previous search criteria. I want to do a query that finds other fields in the table in which their ID is the Primary Key.

i.e if the memo field in table 1 contains the string 1,2,4 and table 2 contains:
RoleID, emailadd
1,a@email.com
2,b@email.com
3,c@email.com
4,d@email.com

I want my search results to be:
a@email.com
b@email.com
d@email.com

but not c@email.com because 3 isn't in the comma delimited memo field.

Is this possible?

Thanks in advance for any help!

TimP

 

[ShiftyJaffa]

Hi

You should be able to do this using the INSTR command. In the query below I had to add comma's to the beginning and end of your memo field so that when I search for ",1," is finds it. If you don't include comma's either end of your number then you would get RoleID 1 appearing when your String is 8,9,10

You don't say whether your tables are linked, but something like this should do the job.

SELECT Table1.MyString, Table2.emailadd FROM Table1, Table2 WHERE (((InStr("," & [MyString] & ",","," & [RoleID] & ","))>0));

You could always add a 'WHERE' statement to localise which memo string you are looking at.

Hope that helps

Paul

 

[timp]

Hi Paul

Thanks for your reply. I’m pretty sure that what you suggested is along the right lines, but it isn’t quite doing what I need – but I suspect that’s because I didn’t give you enough information!

The query you suggested is doing part of the job, but not all, but I think this is on account of me oversimplifying what I was trying to do. So I was hoping if I explained it a bit better you wouldn’t mind helping out again!

Although I posted to an Access forum, the querying is actually being done by an ASP page, but I figured that if I could get an MSAccess query to do what I need, then it is a short step to making the page do it.

These pages are running a mailing list selector. I have a table of contacts tblRoles which contains a primary key (autonumber) RoleID together with various fields about that person including email and snail mail fields.

I have a table called tblCampaigns which contains details of the latest campaign, PK CampID, fields including the campaignname, media etc.

I have a table called tblxCampPers , PK ListID, which cross references which campaign went to whom. In an ideal world this list would be properly normalised so that one person, one campaign would form one line of the table. However, because of the lack of stored procedures in Access and the fact that the mailing list is generated dynamically using refining search criteria and could contain anything from 1 to a few hundred recipients, it is not possible for my page to create these multiple inserts. Hence the fact that for one list (ListID) there is a field containing the CampID and a memo field containing any number of comma delimited RoleIDs for each.
e.g.
ListID CampID MailingList
1 4 1,4,7

So having created the insert which inserts a record into the table above (to enable us later to see who got what) the next page needs to generate the mailing list itself. So I want to parse the memo field of tblxCampPers and then select a sub-set of tblRoles in which filters only values from tblRole which appear in the parsed list, for one specific row in tblxCampPers where ListID = whatever.
So rsMailingList might look like:
RoleID Email
1 a@bcd.com
4 b@bcd.com
7 c@bcd.com

What the query at the moment seems to do is return only one email address – i.e. it returns the memo field and one email address from RoleID.

I should be able to figure this out, but my head is full of cold and I just can’t seem to get my head around how to modify the syntax (I’ve never used InStr before).

If you are prepared to explain further, it would really be appreciated.

Thanks again for the help so far.

 

[ShiftyJaffa]

Hi Timp

I think I see what you mean. The query as it stands should work fine. I've re-created your tables and fields and produced a query which lists out results as you specify.

SELECT tblRoleID.RoleID, tblRoleID.EmailAddress FROM tblRoleID, tblxCampPers WHERE (((InStr("," & [MailingList] & ",","," & [RoleID] & ","))>0));

This assumes there is only 1 record in tblxCampers. If there is more than 1 record in tblxCampers, try

SELECT tblRoleID.RoleID, tblRoleID.EmailAddress FROM tblRoleID, tblxCampPers WHERE tblxCampers.ListID = nn AND (((InStr("," & [MailingList] & ",","," & [RoleID] & ","))>0))

Where nn is the record you want.

INSTR Just returns the first position of a string within another string, so for example INSTR ("ShiftyJaffa" , "Jaffa") would return 7 or INSTR(",1,2,3,4," , ",2,") would return 3. If it returns 0 then the 2nd string does not exist in the first.

Hope that helps

Paul

 

[timp]

Ah, I see now. It is always so much easier when you understand what the code is actually doing!

Thanks very much for all your help.

Regards

TP