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

 

[Pat Hartman]

Zeppelin, it really doesn't matter if you need to use two queries to accomplish something like this. All you will need to run is query2. Query2 takes care of running query1. So you can still use query2 as the recordsource for a form or report.

 

[Zeppelin]

Yep
Already done, thanks