Should Contain versus Must Contain

[Zeppelin]

This is complicated to explain so please bear with me.

I am pulling up data about service call cases using Access XP. A case may be assigned to one or more groups or one or more individuals. I want to pull up the data for one group or individual and see what other groups or individuals are involved with that case.

Lets say case #22 had John Smith, Suzi Quatro and Ozzy Osborne registerd as people who worked this case. If I simply use .. Like "John Smith" in the criteria I will see John Smiths entry but I will not see the other two entries. If I dont use a criteria then I will see all 1000 cases where John Smith was only a memeber of a couple.

This to me is just like the advanced find you have on any website today. I need to figure out how to use the logic of "Must Contain" not "Only Contains" which is what the criteria of "Like" is doing. I cant type in all the Like " " and Like " " and Like " " as there are too many combinations.

Help me obewon kanobie!

 

[simongallop]

How is the data stored? Do you have a table with case and case ID and then another table with userID and CaseID? If so then you can create a query selecting all users where caseID = x.

Let us know how you store the data and we can help

 

[Zeppelin]

All the data I have available from a materialized view, single table.

Better example:

CASE

10 John Smith <dates> <other data>
11 Harry Jonsone <dates> <other data>
11 Suzi Quatro <dates> <other data>
12 Sherlock Holmes <dates> <other data>
13 Harry Jonsone <dates> <other data>
13 John Smith <dates> <other data>
14 Kate Shine <dates> <other data>

What I need to do is say give me all the cases where John Smith is involved AND show everybody else who was involved. Thus the desired result would be:

10 John Smith <dates> <other data>
13 Harry Jonsone <dates> <other data>
13 John Smith <dates> <other data>

Note I want Harry Jonsone on case 13 because he was involved with case 13 along with John Smith.

 

[simongallop]

Create 2 queries:

Query1: Select all records where user name is 'John Smith'

Query2: Select all records from table where CaseID = CaseID from query1

HTH

 

[Zeppelin]

I had my heat set on one query

 

[simongallop]

2 column Table (expand code to suit)

Table: Case_Data
Fields: Case_ID, Name

SELECT Case_Data.Case_ID, Case_Data_1.Name
FROM Case_Data INNER JOIN Case_Data AS Case_Data_1 ON Case_Data.Case_ID = Case_Data_1.Case_ID
WHERE (Case_Data.Name="John Smith");

Basicaly, in the query design select the table twice, linking on ID number. In the above example I selected ID and Name from the first copy and name from the second. I then place 'John Smith' in the first name field but deselected the show check box.

HTH

 

[Zeppelin]

Yep
Already done, thanks