Droping specific records from Access query 1

[dbfNeophyte]

I am running a query against my data and want to drop all records that are associated with an outcome of 413 (see example below)

Client Title Outcomes
Joe Smith 1B 413, 315, 412

Jane Jones 1B 315, 412

In the example above, I would not want Joe Smith to be in the results of the query due to his outcome of 413. I have tried <>413 in the criteria of the query, and it drops only that outcome not Joe Smith. I tried distinct in the SQL statement and Joe Smith continues to show up with outcomes of 315 and 412. I know there is a way to drop the person from the query results if they have the outcome of 413, however I do not have the knowledge base for that, any help is greatly appreciated - in advance.

Thanks,

dbfNeophyte

 

[RickSpr]

When you ask questions about how to modify a query, code, etc., you should always include the query's SQL or the code you've written. We can't tell how your query is structured just by looking at the results.

See the link in my signature line for more tips on asking questions that get answers without unnecessary delays.

I hope that &quot;Outcomes&quot; as shown above is not an actual column in a table. If it is, you've given it a compound value, which is frowned on in relational databases because it complicates searching for a particular value, as in this case.

Outcome (singular, not plural) should be in a child table having a one-to-many relationship to the table that contains Client. Your query could then be something like this:
SELECT ClientTable.Client, Title
FROM ClientTable
WHERE Client NOT IN
(SELECT OutcomeTable.Client
FROM OutcomeTable
WHERE Outcome = '413')

This may not work at all for you, though. I can't give you a more specific solution because I don't know how many tables you have, what their names are, how they're related, or what columns are in each table.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[dbfNeophyte]

Hi Rick:
I appoligize for the lack of information and will try your approach today as time permits.
Here is the SQL for my query:
SELECT DISTINCT qryYYHSDipGEDAttain.KEY_CLT, qryYYHSDipGEDAttain.CLDO, qryYYHSDipGEDAttain.CLCOUNSID, qryYYHSDipGEDAttain.CLLASTNAME, qryYYHSDipGEDAttain.CLFIRSTNAME, qryYYHSDipGEDAttain.CLTITLE, cdexit.CLEXITCODE, cdexit.CLEXITDT, cdoutcome.CLOTCODE
FROM (qryYYHSDipGEDAttain LEFT JOIN cdoutcome ON qryYYHSDipGEDAttain.KEY_CLT = cdoutcome.KEY_CLT) LEFT JOIN cdexit ON qryYYHSDipGEDAttain.KEY_CLT = cdexit.KEY_CLT
GROUP BY qryYYHSDipGEDAttain.KEY_CLT, qryYYHSDipGEDAttain.CLDO, qryYYHSDipGEDAttain.CLCOUNSID, qryYYHSDipGEDAttain.CLLASTNAME, qryYYHSDipGEDAttain.CLFIRSTNAME, qryYYHSDipGEDAttain.CLTITLE, cdexit.CLEXITCODE, cdexit.CLEXITDT, cdoutcome.CLOTCODE
HAVING (((qryYYHSDipGEDAttain.CLTITLE)=&quot;1B&quot AND ((cdexit.CLEXITCODE)>=&quot;500&quot AND ((cdexit.CLEXITDT) Between #7/1/2002# And #3/31/2003#) AND ((cdoutcome.CLOTCODE) Is Null Or (cdoutcome.CLOTCODE)<>&quot;413&quot);

You are correct on the way this is setup (Outcome (singular, not plural) is in a child table called cdoutcome and the outcomes (315, 412, 413 and more) are in a column called CLOTCODE having a one-to-many relationship to the table that contains Client information (cdclt). My database contains 18 client tables and 39 tables that contain definitions plus 5 misc tables and of course they are all related. This is a program that has been built for the agency that I am employed with. I have linked into this database with access and create reports to use for case management - keeps life interesting and fun. Thanks again, and will get back to with results later.


Virgil (dbfNeophyte)

 

[dbfNeophyte]

Hi Rick:
I have attempted including the SQL you sent, however my SQL skills are lacking and I could not get it to work. Also, I did not mention in my last reply, I am running Access 2000. Thanks for any help you can offer and for the link you sent.

Virgil (dbfNeophyte)

 

[RickSpr]

This should be what you need:
SELECT DISTINCT qryYYHSDipGEDAttain.KEY_CLT, qryYYHSDipGEDAttain.CLDO, qryYYHSDipGEDAttain.CLCOUNSID, qryYYHSDipGEDAttain.CLLASTNAME, qryYYHSDipGEDAttain.CLFIRSTNAME, qryYYHSDipGEDAttain.CLTITLE, cdexit.CLEXITCODE, cdexit.CLEXITDT, cdoutcome.CLOTCODE
FROM (qryYYHSDipGEDAttain LEFT JOIN cdoutcome ON qryYYHSDipGEDAttain.KEY_CLT = cdoutcome.KEY_CLT) LEFT JOIN cdexit ON qryYYHSDipGEDAttain.KEY_CLT = cdexit.KEY_CLT
WHERE (((qryYYHSDipGEDAttain.CLTITLE)=&quot;1B&quot AND ((cdexit.CLEXITCODE)>=&quot;500&quot AND ((cdexit.CLEXITDT) Between #7/1/2002# And #3/31/2003#) AND ((qryYYHSDipGEDAttain.KEY_CLT) NOT IN (SELECT cdoutcome.KEY_CLT FROM cdoutcome WHERE cdoutcome.CLOTCODE=&quot;413&quot));

Notes:
1. Using GROUP BY to group by all the columns you're selecting has the same effect as using DISTINCT. Use DISTINCT in this case, because it's easier to comprehend.
2. Your query didn't do what you want because it composes a row for each combination of client and outcome, and then eliminates the rows where the outcome was 413. If the client had other outcomes, they were in separate rows and thus weren't eliminated.
3. My query works by creating a temporary table of clients who have an outcome of 413. The temporary table contains only the client keys. The main query then eliminates clients who appear in the temporary table. The difference is that your query eliminated client/outcome combinations, while mine eliminates clients.

BTW, the link is just something in my signature line. It appears on all my posts, and wasn't directed to you specifically.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[dbfNeophyte]

Hello Rick;
Yes! This works very well. I am going to have to take some time with this to develop an understanding of the differences in what you are doing and what I did. Your approach makes much more sense. Thank you very much for your time and effort.

Virgil