Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2009
    Posts
    30

    Unanswered: Eliminate rows based on other field

    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.
    Thanks.

  2. #2
    Join Date
    Oct 2011
    Posts
    21
    Under your query in design form. put =good. Under the good field. That will fix it. if you need the great in there to, then put ="good" or "great" it should work.

  3. #3
    Join Date
    Feb 2009
    Posts
    30
    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.

  4. #4
    Join Date
    Oct 2011
    Posts
    21
    Are you talking about deleting all the records with bad in them? Totally deleting?

  5. #5
    Join Date
    Oct 2011
    Posts
    21
    Ok i think i understand. So if you have good bad or whatever for that patient, you don't want it to show up?

  6. #6
    Join Date
    Feb 2009
    Posts
    30
    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.

  7. #7
    Join Date
    Oct 2011
    Posts
    21
    I don't know sequel very well, but I think that is the key to your question. I told sindho, so hopefully he will be able to help you out. He is very good : )

  8. #8
    Join Date
    Feb 2009
    Posts
    30
    Thank you. I'll wait to hear from sindho

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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?
    Have a nice day!

  10. #10
    Join Date
    Feb 2009
    Posts
    30
    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
    WHERE (((A.PAT_NUM)=[B].[PAT_NUM]))))<>False));

  11. #11
    Join Date
    Feb 2009
    Posts
    30
    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.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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:
    Code:
    SELECT DISTINCT [Patient #] 
        FROM [Notes]
        WHERE [Notes].[Note] = 'BAD';
    The DISTINCT predicate was used to retrieve each [Patient #] value only once.

    3. For deleting you can use:
    Code:
    DELETE *
        FROM [A]
        WHERE [A].[Patient #] IN (SELECT DISTINCT [Patient #]
                                      FROM [Notes]
                                      WHERE [Notes].[Note] = 'BAD'
                                 );
    Have a nice day!

  13. #13
    Join Date
    Feb 2009
    Posts
    30
    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.

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  15. #15
    Join Date
    Feb 2009
    Posts
    30
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •