Delete records based on two sets of criteria

[Access Joe]

Hi everyone,

I have a table laid out as follows:

Record# ID DATA
1 1 LIS
2 1 LIS
3 1 (Blank)
4 2 LIS
5 2 LIS
6 3 (Blank)
7 4 (Blank)
8 4 LIS
9 5 (Blank)
0 5 (Blank)

As you can see, a person's record may appear more than once in the table.
What I want to do: anyone who has just one "LIS" record needs to be
completely removed from the table. For example, I want to remove ALL records
for ID #1, because "LIS" appeared in one of their records. So record #'s
1,2, and 3 would be deleted. Once all is said and done, the only record #'s
that should remain in the above table are 3 & 5 because they don't have 'LIS'
on their record row.

I'm familiar with query design and delete queries, but I can't figure out
how to delete all the patients records when the word LIS appears on just one
of them.

Hope this makes sense, and that someone can help. Thanks so much!

Joe

 

[Evi]

In a field in your query, type

CountLis: Abs([Data]="Lis")

base a Totals query on this query adding only CountLis the ID field, Group
by the ID field and Sum the CountLis field.
The criteria will be that CountLis >0

Delete all the records in the query

Evi

 

[John Spencer]

One way that will work (especially with smaller recordsets is )

SELECT *
FROM SomeTable
WHERE ID NOT IN
(
SELECT ID FROM SomeTable
WHERE Data = "LIS"
)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Evi]

John, you've put the brackets for the subquery on different lines. Is that
OK or was it a freak of the email?
If it is OK to do, it is nice and clear to read like that.
Evi

 

[Access Joe]

Thanks John. Can you tell me where exactly I would go to enter that code?

 

[Access Joe]

Thanks Evi. I tried what you said, but the problem is you can't delete
records in a query if the Grouping is turned on. So how can remove those
records that appear?

In a field in your query, type

CountLis: Abs([Data]="Lis")

base a Totals query on this query adding only CountLis the ID field, Group
by the ID field and Sum the CountLis field.
The criteria will be that CountLis >0

Delete all the records in the query

Evi

Hi everyone,

I have a table laid out as follows:

Record# ID DATA
1 1 LIS
2 1 LIS
3 1 (Blank)
4 2 LIS
5 2 LIS
6 3 (Blank)
7 4 (Blank)
8 4 LIS
9 5 (Blank)
0 5 (Blank)

As you can see, a person's record may appear more than once in the table.
What I want to do: anyone who has just one "LIS" record needs to be
completely removed from the table. For example, I want to remove ALL records
for ID #1, because "LIS" appeared in one of their records. So record #'s
1,2, and 3 would be deleted. Once all is said and done, the only record #'s
that should remain in the above table are 3 & 5 because they don't have 'LIS'
on their record row.

I'm familiar with query design and delete queries, but I can't figure out
how to delete all the patients records when the word LIS appears on just one
of them.

Hope this makes sense, and that someone can help. Thanks so much!

Joe

 

[John Spencer]

It should be ok. Access, bless its heart, will reorganize it and it will become

SELECT *
FROM SomeTable
WHERE (((SomeTable.ID) Not In (SELECT ID FROM SomeTable
WHERE Data = "LIS"
)));

But it will work.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Evi]

Can you put the Totals query into another query (click on it, go to Insert
Query) and change that query to a delete query?

Evi

Thanks Evi. I tried what you said, but the problem is you can't delete
records in a query if the Grouping is turned on. So how can remove those
records that appear?

In a field in your query, type

CountLis: Abs([Data]="Lis")

base a Totals query on this query adding only CountLis the ID field, Group
by the ID field and Sum the CountLis field.
The criteria will be that CountLis >0

Delete all the records in the query

Evi

Hi everyone,

I have a table laid out as follows:

Record# ID DATA
1 1 LIS
2 1 LIS
3 1 (Blank)
4 2 LIS
5 2 LIS
6 3 (Blank)
7 4 (Blank)
8 4 LIS
9 5 (Blank)
0 5 (Blank)

As you can see, a person's record may appear more than once in the table.
What I want to do: anyone who has just one "LIS" record needs to be
completely removed from the table. For example, I want to remove ALL records
for ID #1, because "LIS" appeared in one of their records. So record #'s
1,2, and 3 would be deleted. Once all is said and done, the only

record
#'s

that should remain in the above table are 3 & 5 because they don't

have
'LIS'

on their record row.

I'm familiar with query design and delete queries, but I can't figure out
how to delete all the patients records when the word LIS appears on

just
one

of them.

Hope this makes sense, and that someone can help. Thanks so much!

Joe

 

[Access Joe]

Before I do this, I just noticed another issue. This resulting totals query
is not finding the ID's in which the LIS column was left blank. Remember -
an ID may be entered several times. Sometimes a few ID's will have 'LIS' in
their record set. if that is the case, EVERY other matching ID will then
have to be deleted.

Can you put the Totals query into another query (click on it, go to Insert
Query) and change that query to a delete query?

Evi

Thanks Evi. I tried what you said, but the problem is you can't delete
records in a query if the Grouping is turned on. So how can remove those
records that appear?

In a field in your query, type

CountLis: Abs([Data]="Lis")

base a Totals query on this query adding only CountLis the ID field, Group
by the ID field and Sum the CountLis field.
The criteria will be that CountLis >0

Delete all the records in the query

Evi
Hi everyone,

I have a table laid out as follows:

Record# ID DATA
1 1 LIS
2 1 LIS
3 1 (Blank)
4 2 LIS
5 2 LIS
6 3 (Blank)
7 4 (Blank)
8 4 LIS
9 5 (Blank)
0 5 (Blank)

As you can see, a person's record may appear more than once in the table.
What I want to do: anyone who has just one "LIS" record needs to be
completely removed from the table. For example, I want to remove ALL
records
for ID #1, because "LIS" appeared in one of their records. So record #'s
1,2, and 3 would be deleted. Once all is said and done, the only record
#'s
that should remain in the above table are 3 & 5 because they don't have
'LIS'
on their record row.

I'm familiar with query design and delete queries, but I can't figure out
how to delete all the patients records when the word LIS appears on just
one
of them.

Hope this makes sense, and that someone can help. Thanks so much!

Joe

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

If what you want to do is DELETE (permanently remove) the records that have
"LIS" then the SQL is

DELETE
FROM [YourTableName]
WHERE ID IN (SELECT ID FROM [YourTableName] WHERE [Data] = "LIS")

You should be able to open a new query, not add any table, switch to SQL view
(View: SQL on menu) and paste the above in. Obviously you need to replace
"YourTableName" with the actual name of your table.

Or
-- Create a new query
-- Select your table
-- add the ID field
-- In the criteria for the id field enter the following
IN (SELECT ID FROM [YourTableName] WHERE [Data] = "LIS")
-- Select Query: Delete from the menu
-- Run the query by using Query: Run from the menu

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Access Joe]

Thanks again John. Will this also remove those ID's where the LIS column was
left blank? Remember - an ID may be entered several times. Sometimes a few
ID's will have 'LIS' in their record set. if that is the case, EVERY other
matching ID will then have to be deleted.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

If what you want to do is DELETE (permanently remove) the records that have
"LIS" then the SQL is

DELETE
FROM [YourTableName]
WHERE ID IN (SELECT ID FROM [YourTableName] WHERE [Data] = "LIS")

You should be able to open a new query, not add any table, switch to SQL view
(View: SQL on menu) and paste the above in. Obviously you need to replace
"YourTableName" with the actual name of your table.

Or
-- Create a new query
-- Select your table
-- add the ID field
-- In the criteria for the id field enter the following
IN (SELECT ID FROM [YourTableName] WHERE [Data] = "LIS")
-- Select Query: Delete from the menu
-- Run the query by using Query: Run from the menu

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Thanks John. Can you tell me where exactly I would go to enter that code?

 

[Gary Walter]

PMFBI

I believe you can "hide" the aggregation in the WHERE clause

try first on copy of table (say "yurtable")...

DELETE * FROM yurtable AS T
WHERE T.ID IN
(SELECT Q.ID
FROM yurtable AS Q
WHERE
Max(Q.DATA) = "LIS");

Apolgies again for butting in...

gary

Before I do this, I just noticed another issue. This resulting totals query
is not finding the ID's in which the LIS column was left blank. Remember -
an ID may be entered several times. Sometimes a few ID's will have 'LIS' in
their record set. if that is the case, EVERY other matching ID will then
have to be deleted.

Can you put the Totals query into another query (click on it, go to Insert
Query) and change that query to a delete query?

Evi

Thanks Evi. I tried what you said, but the problem is you can't delete
records in a query if the Grouping is turned on. So how can remove those
records that appear?

:

In a field in your query, type

CountLis: Abs([Data]="Lis")

base a Totals query on this query adding only CountLis the ID field, Group
by the ID field and Sum the CountLis field.
The criteria will be that CountLis >0

Delete all the records in the query

Evi
Hi everyone,

I have a table laid out as follows:

Record# ID DATA
1 1 LIS
2 1 LIS
3 1 (Blank)
4 2 LIS
5 2 LIS
6 3 (Blank)
7 4 (Blank)
8 4 LIS
9 5 (Blank)
0 5 (Blank)

As you can see, a person's record may appear more than once in the table.
What I want to do: anyone who has just one "LIS" record needs to be
completely removed from the table. For example, I want to remove ALL
records
for ID #1, because "LIS" appeared in one of their records. So

record
#'s

1,2, and 3 would be deleted. Once all is said and done, the only record
#'s
that should remain in the above table are 3 & 5 because they don't have
'LIS'
on their record row.

I'm familiar with query design and delete queries, but I can't

figure
out

how to delete all the patients records when the word LIS appears

on
just

one
of them.

Hope this makes sense, and that someone can help. Thanks so much!

Joe

 

[Gary Walter]

please ignore..had not scrolled down to see John's post

PMFBI

I believe you can "hide" the aggregation in the WHERE clause

try first on copy of table (say "yurtable")...

DELETE * FROM yurtable AS T
WHERE T.ID IN
(SELECT Q.ID
FROM yurtable AS Q
WHERE
Max(Q.DATA) = "LIS");

Apolgies again for butting in...

gary

Before I do this, I just noticed another issue. This resulting totals query
is not finding the ID's in which the LIS column was left blank. Remember -
an ID may be entered several times. Sometimes a few ID's will have

'LIS'

in
their record set. if that is the case, EVERY other matching ID will then
have to be deleted.

Can you put the Totals query into another query (click on it, go to Insert
Query) and change that query to a delete query?

Evi

Thanks Evi. I tried what you said, but the problem is you can't delete
records in a query if the Grouping is turned on. So how can remove those
records that appear?

:

In a field in your query, type

CountLis: Abs([Data]="Lis")

base a Totals query on this query adding only CountLis the ID field,
Group
by the ID field and Sum the CountLis field.
The criteria will be that CountLis >0

Delete all the records in the query

Evi
Hi everyone,

I have a table laid out as follows:

Record# ID DATA
1 1 LIS
2 1 LIS
3 1 (Blank)
4 2 LIS
5 2 LIS
6 3 (Blank)
7 4 (Blank)
8 4 LIS
9 5 (Blank)
0 5 (Blank)

As you can see, a person's record may appear more than once in the
table.
What I want to do: anyone who has just one "LIS" record needs to be
completely removed from the table. For example, I want to

remove

ALL

 

[John Spencer]

You could try it and see.

But YES it should. The subquery is returning any id that has at least one
record with "LIS" in the Data field. That then matches all the IDs in the
main query that have that value.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Thanks again John. Will this also remove those ID's where the LIS column was
left blank? Remember - an ID may be entered several times. Sometimes a few
ID's will have 'LIS' in their record set. if that is the case, EVERY other
matching ID will then have to be deleted.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

If what you want to do is DELETE (permanently remove) the records that have
"LIS" then the SQL is

DELETE
FROM [YourTableName]
WHERE ID IN (SELECT ID FROM [YourTableName] WHERE [Data] = "LIS")

You should be able to open a new query, not add any table, switch to SQL view
(View: SQL on menu) and paste the above in. Obviously you need to replace
"YourTableName" with the actual name of your table.

Or
-- Create a new query
-- Select your table
-- add the ID field
-- In the criteria for the id field enter the following
IN (SELECT ID FROM [YourTableName] WHERE [Data] = "LIS")
-- Select Query: Delete from the menu
-- Run the query by using Query: Run from the menu

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Thanks John. Can you tell me where exactly I would go to enter that code?

:

One way that will work (especially with smaller recordsets is )

SELECT *
FROM SomeTable
WHERE ID NOT IN
(
SELECT ID FROM SomeTable
WHERE Data = "LIS"
)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Access Joe wrote:
Hi everyone,

I have a table laid out as follows:

Record# ID DATA
1 1 LIS
2 1 LIS
3 1 (Blank)
4 2 LIS
5 2 LIS
6 3 (Blank)
7 4 (Blank)
8 4 LIS
9 5 (Blank)
0 5 (Blank)

As you can see, a person's record may appear more than once in the table.
What I want to do: anyone who has just one "LIS" record needs to be
completely removed from the table. For example, I want to remove ALL records
for ID #1, because "LIS" appeared in one of their records. So record #'s
1,2, and 3 would be deleted. Once all is said and done, the only record #'s
that should remain in the above table are 3 & 5 because they don't have 'LIS'
on their record row.

I'm familiar with query design and delete queries, but I can't figure out
how to delete all the patients records when the word LIS appears on just one
of them.

Hope this makes sense, and that someone can help. Thanks so much!

Joe

 

[Access Joe]

Worked like a charm. Thank you John!

You could try it and see.

But YES it should. The subquery is returning any id that has at least one
record with "LIS" in the Data field. That then matches all the IDs in the
main query that have that value.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Thanks again John. Will this also remove those ID's where the LIS column was
left blank? Remember - an ID may be entered several times. Sometimes a few
ID's will have 'LIS' in their record set. if that is the case, EVERY other
matching ID will then have to be deleted.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

If what you want to do is DELETE (permanently remove) the records that have
"LIS" then the SQL is

DELETE
FROM [YourTableName]
WHERE ID IN (SELECT ID FROM [YourTableName] WHERE [Data] = "LIS")

You should be able to open a new query, not add any table, switch to SQL view
(View: SQL on menu) and paste the above in. Obviously you need to replace
"YourTableName" with the actual name of your table.

Or
-- Create a new query
-- Select your table
-- add the ID field
-- In the criteria for the id field enter the following
IN (SELECT ID FROM [YourTableName] WHERE [Data] = "LIS")
-- Select Query: Delete from the menu
-- Run the query by using Query: Run from the menu

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Access Joe wrote:
Thanks John. Can you tell me where exactly I would go to enter that code?

:

One way that will work (especially with smaller recordsets is )

SELECT *
FROM SomeTable
WHERE ID NOT IN
(
SELECT ID FROM SomeTable
WHERE Data = "LIS"
)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Access Joe wrote:
Hi everyone,

I have a table laid out as follows:

Record# ID DATA
1 1 LIS
2 1 LIS
3 1 (Blank)
4 2 LIS
5 2 LIS
6 3 (Blank)
7 4 (Blank)
8 4 LIS
9 5 (Blank)
0 5 (Blank)

As you can see, a person's record may appear more than once in the table.
What I want to do: anyone who has just one "LIS" record needs to be
completely removed from the table. For example, I want to remove ALL records
for ID #1, because "LIS" appeared in one of their records. So record #'s
1,2, and 3 would be deleted. Once all is said and done, the only record #'s
that should remain in the above table are 3 & 5 because they don't have 'LIS'
on their record row.

I'm familiar with query design and delete queries, but I can't figure out
how to delete all the patients records when the word LIS appears on just one
of them.

Hope this makes sense, and that someone can help. Thanks so much!

Joe