How can I pull text from comma separated memo or text field?

[rhaas128]

I am trying to find all records in a table that contain a specific user id from another table. I have roughly 900 entries in the table. So I have a query with both tables.

Table1
Identity


Table2
ReceiverUserIDs


The ReceiverUserIDs field can contain a single user, or multiple users comma separated such as the below:

aaa1111 OR aaa1111,bbb2222,ccc3333

I have:
Field - ReceiverUserIDs
Table - Table2
Total - Where
Criteria - Like [Table1].[Identity]


This will only produce the entries where it finds a matching userid that is not in a comma separated list. So if I am looking for aaa1111 in the below, the results would include both row 1 and 3 instead of just row 1.

[TABLE="width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD]aaa1111[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]bbb2222[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ccc3333,ddd4444,aaa1111[/TD]
[/TR]
</tbody>[/TABLE]


Does this make sense? If so, can anyone assist? Thanks in advance!

 

[xenou]

You must use wildcards for your like:
Criteria - Like "*" & [Table1].[Identity] & "*"

 

[rhaas128]

You must use wildcards for your like:
Criteria - Like "*" & [Table1].[Identity] & "*"

Oh duh!.. Thank you so much!

 

[rhaas128]

You must use wildcards for your like:
Criteria - Like "*" & [Table1].[Identity] & "*"

In addtion to this, if a user id is not found, it just does not display in the query output. I would like to create a new query that only shows users the do not exist in the other table. I tried changing Like to Not Like, but that produced a TON of results that I didn't need. In theory based on my data, there should only be 5-10 of these users vs the 50k that returned. Thoughts?

 

[rhaas128]

Well I said disregard but my new attempt didn't work.

 

[xenou]

can you provide a sample of an id that is included even though it doesn't fit the not like criteria? And really you should post your sql because the way you have described your query it's not really well-formed sql so I'm just guessing on how you actually write the actual sql.

 

[rhaas128]

Ok, well here is a fresh start. Below is the sql I have, but after creating a new workbook with fewer values, it does not appear the Like "*" piece is working as I only get 3 results.

SELECT [AP Role Report].Identity, [SN Role Report].Description, [SN Role Report].Parent, [SN Role Report].Number, [SN Role Report].ApprovalFROM [AP Role Report], [SN Role Report]
WHERE ((([AP Role Report].Identity) Like "*" & [SN Role Report].[ReceiverIDs] & "*"))
GROUP BY [AP Role Report].Identity, [SN Role Report].Description, [SN Role Report].Parent, [SN Role Report].Number, [SN Role Report].Approval;

I'm not sure I need all the group bys, but am not that good with this stuff. Below is a link to my box.com file with the actual database (named Compare.accdb). I know some people don't feel comfortable opening them, but it is there nonetheless.

https://app.box.com/s/f5zauietrnrzairospj740tpcqa0a050

If you download and open, here is the scenario / objectives:

Query1 is the only query currently
-I would like to either group by or sort by ascending of AP Role Report.Identity
-The others I don't think need a group by or anything. Please correct me if I am wrong in this thought.
-The Where clause should search for all AP Role Report.Identity entries and see if they are located within SN Role Report.ReceiverIDs (this is the comma separated list of records).
-All findings should be shown in the query results
-In this database, I have a table labeled ExpectedResults which is obviously what I am attempting to get the query to show.

Query2 is the query that does not exist.
-I would like this query to search for all AP Role Report.Identity entries that are NOT located within SN Role Report.ReceiverIDs (this is the comma separated list of records).
-All findings should be shown in the query results
-In this database, there should only be 1 result which is the AP Role Report.Identity of DDD1111

The module can be ignored right now.

I hope this makes sense now. Any and all help is GREATLY appreciated!