Need help selecting and deleting table rows based on criteria

[lilp32]

I am working with a table in Access 2010 which includes test result (0/1) and date of result by subject ID in addition to a number of other fields.

I would like to include all results up until and including the first positive result, but eliminate all subsequent results.

I am not sure about the best way to go about this, so any suggestions would be appreciated.

Example:
Subject 123 has negative results on Jan 1 and Jan 2, a positive result on Jan 3, another positive result on Feb 1, and a negative result on Feb 2. I want to keep the first three and eliminate the last one.

[NeoPa]

It can be done. Use a DELETE query that links to the same table with criteria that checks if the second instance of the table has a record that has a prior date and a test result of True.

Let us know how you get on as I'm just off out. If I need to include more help later then I will.

[lilp32]

I'm afraid I'm not getting this right. Here is what I have, and it returns no rows:

DELETE  Table1.*, Table1_1.ADMIT_ESBL_SURV_POS, Table1_1.ADMIT_CUL_DATETIME
FROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.ID = Table1_1.ID
WHERE (((Table1_1.ADMIT_ESBL_SURV_POS)=1) AND ((Table1_1.ADMIT_CUL_DATETIME)<[Table1].[ADMIT_CUL_DATETIME]));

[zmbd]

OK,
Back up a step.
FIRST, create a normal select query that returns just the records you want to delete. The fewer tables you use to accomplish this the better.

[jbryant]

Run a query and build a table with all data then second query to delete unwanted data. An extra step but easier to follow process and later easier to change. Delete query from a single table is fairly simple.

DELETE mstr.Code, mstr.Department, mstr.Gen, mstr.Equip
FROM alloc_mstr
WHERE ((alloc_mstr.Code)="0065-168" Or (alloc_mstr.Code)="0065-169" );

[NeoPa]

Although it's easier to handle with a make-table query, it's not often necessary to complicate the process that way. Any time you introduce multiple steps into an update process you expose yourself to the issue of synchronising the data. Generally to be avoided if you can.

I haven't too much to offer just now as it's late and my sobriety is in question, but I'm sure we can help take this further atomically if it's possible, and I haven't noticed anything that would preclude that so far.

[lilp32]

It's been awhile, but I had to fix errors in the dataset. I am now back to trying to delete the records. I am having trouble selecting the records I want to delete. I have the fields ID, ADMITDATE, CultureDate and POS_ADMIT. Only those who have POS_ADMIT=1 have a CultureDate. I would like to eliminate all visits after the first CultureDate. I tried to make a select query as mentioned by NeoPa above but I need more guidance on how to join the two instances of the table and specify those criteria.

[NeoPa]

Hi Lil P.

Sorry for the long delay. I've been very busy with work commitments to the extent that I haven't visited Bytes for a week or more. Very unusual for me.

As for this problem, it's not as straightforward as it might first appear, mainly because we are constrained to Access' Jet SQL which is somewhat limited in some areas. Principal of which is its restriction on being able to update anything under various circumstances where other versions of SQL can and do. See Reasons for a Query to be Non-Updatable for more on that.

Anyway, I tried with the following SQL, only to find it was blocked from applying the updates :-( :

DELETE [Table1].*
FROM   [Table1] AS [t1]
       INNER JOIN
       [Table1] AS [t2]
  ON   [t1].[Group]=[t2].[Group]
WHERE  ([t1].[ADMIT_CUL_DATETIME]>[t2].[ADMIT_CUL_DATETIME])
  AND  ([t1].[ADMIT_ESBL_SURV_POS])

This looks to be very similar to the SQL you tried yourself and posted in #3.

So, I looked into using a subquery (See Subqueries in SQL) for use inside the WHERE clause :

DELETE [t1].*
FROM   [Table1] AS [t1]
WHERE  ([ADMIT_CUL_DATETIME]>(SELECT   Min([ADMIT_CUL_DATETIME])
                              FROM     [Table1] AS [t2]
                              WHERE    ([t1].[ID]=[t2].[ID])
                                AND    ([ADMIT_ESBL_SURV_POS])))

NB. I knocked up a test system and tried this out and it worked for me. You data or setup may be a little different but I used your info to create the test system so it should work.