There's probably an easy way to do this, but I can't figure it out. It's hard for me to describe so it's hard to search on what I'm looking for, but will do the best I can.
I have 3 tables that I'm linking.
Table 1 = Patient demographic
Table 2 = Patient stay (pat. # is unique identifier)
Table 3 = Notes (can have several notes for that stay) and one of the fields is a Note code.
If I combine table 1 and 2, I get let's say 100 records when I run the query. When I add Table 3, I then get a result of 250 records when I run the query and that's because a patient can have several notes.
Ultimately I'm trying to get rid of ALL patients with a Note Code of "BAD".
So for example, if I have the following Name, Patient #, Note:
Smith 123456 GOOD
Smith 123456 BAD
Smith 123456 GREAT
Jones 567890 BAD
Utley 555555 GOOD
Utley 555555 GREAT
For result of the query, I want to have just the patient Utley show up. I want ALL records of Smith to be eliminated because one of the Notes is BAD. Jones is eliminated because his Note is BAD.
I can't get the result I want by eliminating BAD in the criteria for Note Code, because Smith would still appear because of the GOOD and GREAT. I cannot for whatever reason, figure out a strategy of how to eliminate all of the records.
I'd appreciate any suggestions you can throw my way. Hopefully coding will not be involved in the solution, since it can be a bit of a struggle for me.
If you're talking about putting "GOOD" in the criteria, that's not going to get me what I need. I'm trying to get rid of all Rows with the same Patient # if at least one of the Notes says "BAD". In my example, I don't want Smith to appear at all in my results for the query, because "BAD" is in one of the Notes. There are lots of Note codes, I just gave a small random example. Sorry if I'm not explaining myself very good.
I basically want to get a report out, so if we delete them out of a table that I create or delete them out in a query of a table, either will work. I want to delete that patient and all of his records (or don't want to show all of his records) if even one of his records has BAD in the Note code. So in my example I want 0 lines to show up on my report for Smith.
Just to be sure: Do you actually want to delete all rows in all three tables ([Patient Demographic], [Patient Stay] and [Notes]) for a patient with one "BAD" value in the [Note] column of the [Notes] table?
I've been playing with this a lot today and have gotten to a certain point and I'll explain. Need just a little more help.
This is a vendor database, so I can't delete from the table, so what I did was created a table "A" based on a query pulling from these 3 tables. I also created a table "B" with all the Patient numbers that I want deleted from Table "A". That would delete out like All of the Smiths in my example since one of the patient numbers in table B is 123456. I'll include the SQL below. I'm pulling out all the patients with this query that I want to delete from Table "A" when I view it, but when I run it, it says it can't delete it. Looking at the help, it tells me I can't have it opened in Read only. I then opened it in Exclusive and it still won't delete it from the table. I'm getting close, but no cigar yet.
DELETE A.*, Exists (SELECT *
FROM A INNER JOIN B ON A.PAT_NUM = B.PAT_NUM
WHERE (((A.pat_num)=[B].[PAT_NUM]))) AS Expr1
FROM A INNER JOIN B ON A.PTNAME = B.PTNAME
WHERE (((Exists (SELECT *
FROM A INNER JOIN B ON A.PAT_NUM = B.PAT_NUM
I created a quick query to see if I could delete from table "A" and I was able to delete a record. So, not sure why I'm getting the message "Could not delete from specified tables" from the above query.
1. If you want to delete from table A, you do not need to use the tables [Patient demographic] and [Patient stay] in the query at all. You simply need to retrieve all [Patient #] values wich have 'BAD' in the [Note] column.
2. The selection of rows to be deleted can be made with:
SELECT DISTINCT [Patient #]
WHERE [Notes].[Note] = 'BAD';
The DISTINCT predicate was used to retrieve each [Patient #] value only once.
3. For deleting you can use:
WHERE [A].[Patient #] IN (SELECT DISTINCT [Patient #]
WHERE [Notes].[Note] = 'BAD'
You are correct, I do not have to delete it from all the tables, I just need to delete it from the 1 table, Table A. I've been simplifying it throughout the day, so I gave too much data in the beginning of this thread. I thank you for your help. I will take a look at your suggestion and try to work it into my situation this weekend or at latest Monday. Thanks again for your help on this.
You've made me very happy today, it worked! This forum has been so helpful for the handful of times I've used it with both seeking from other peoples problems or posting my own. Thank you so much, I'm very grateful. I'm not sure if I could have gotten it without your help. Your solution was much simpler than mine.