Delete records based on multiple criteria

[Cydney]

For the records shown below, I need to delete the records where a person is
in "travel mode" (ExpCat = "Travel Costs") has meals (ExpType="Meals-Travel")
on the SAME day (ExpDate) as his lodging (ExpType="Lodging"). In this
example, the meals on 10/8 would be deleted, but not the meals on 10/6 and
10/7 because on those days he also had lodging charges.

How can I accomplish this through a query?

Empl ExpDate ExpCat ExpType Bill Amount
Joe Frank 6/30/2008 Travel Costs Air Transportation 377
Joe Frank 10/3/2008 Travel Costs Air Transportation 84
Joe Frank 10/6/2008 Travel Costs Lodging 160
Joe Frank 10/6/2008 Travel Costs Meals-travel 24.29
Joe Frank 10/7/2008 Travel Costs Lodging 160
Joe Frank 10/7/2008 Travel Costs Meals-travel 11.5
Joe Frank 10/7/2008 Travel Costs Meals-travel 21.56
Joe Frank 10/8/2008 Travel Costs Auto Rentals 100.12
Joe Frank 10/8/2008 Travel Costs Meals-travel 20.17

--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

 

[John W. Vinson]

For the records shown below, I need to delete the records where a person is
in "travel mode" (ExpCat = "Travel Costs") has meals (ExpType="Meals-Travel")
on the SAME day (ExpDate) as his lodging (ExpType="Lodging"). In this
example, the meals on 10/8 would be deleted, but not the meals on 10/6 and
10/7 because on those days he also had lodging charges.

How can I accomplish this through a query?

A Subquery with an EXISTS clause would do it:

DELETE * FROM tablename
WHERE [ExpCat] = "Travel Costs"
AND [ExpType] = "Meals-Travel"
WHERE EXISTS
(SELECT X.Empl FROM tablename AS X
WHERE X.Empl = tablename.Empl
AND X.ExpDate = tablename.ExpDate
AND X.ExpType = "Lodging")

 

[John Spencer]

You said you want to delete records where the person had Lodging expenses on
the same day, but then your example does the opposite. You deleted the
expenses on the date where the person did NOT have Lodging expenses.

John Vinson's query would delete the Meals-Travel expenses on the days WITH
lodging. If it worked - but he had one too many WHERE clauses The second
WHERE should be an AND

DELETE * FROM tablename
WHERE [ExpCat] = "Travel Costs"
AND [ExpType] = "Meals-Travel"
AND EXISTS
(SELECT X.Empl FROM tablename AS X
WHERE X.Empl = tablename.Empl
AND X.ExpDate = tablename.ExpDate
AND X.ExpType = "Lodging")

With a small set of records, you could use the following query to delete
Meals-Travel records where there was no lodging expense on the same day for
the employee.

DELETE * FROM tablename
WHERE [ExpCat] = "Travel Costs"
AND [ExpType] = "Meals-Travel"
AND NOT EXISTS
(SELECT X.Empl FROM tablename AS X
WHERE X.Empl = tablename.Empl
AND X.ExpDate = tablename.ExpDate
AND X.ExpType = "Lodging")

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

For the records shown below, I need to delete the records where a person is
in "travel mode" (ExpCat = "Travel Costs") has meals (ExpType="Meals-Travel")
on the SAME day (ExpDate) as his lodging (ExpType="Lodging"). In this
example, the meals on 10/8 would be deleted, but not the meals on 10/6 and
10/7 because on those days he also had lodging charges.

How can I accomplish this through a query?

A Subquery with an EXISTS clause would do it:

DELETE * FROM tablename
WHERE [ExpCat] = "Travel Costs"
AND [ExpType] = "Meals-Travel"
WHERE EXISTS
(SELECT X.Empl FROM tablename AS X
WHERE X.Empl = tablename.Empl
AND X.ExpDate = tablename.ExpDate
AND X.ExpType = "Lodging")

 

[Cydney]

You saw through my misrepresentation and STILL provided me a useable answer!
I meant to say the meals on 10/8 would NOT be deleted.
This worked perfectly. Thank you!

--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

You said you want to delete records where the person had Lodging expenses on
the same day, but then your example does the opposite. You deleted the
expenses on the date where the person did NOT have Lodging expenses.

John Vinson's query would delete the Meals-Travel expenses on the days WITH
lodging. If it worked - but he had one too many WHERE clauses The second
WHERE should be an AND

DELETE * FROM tablename
WHERE [ExpCat] = "Travel Costs"
AND [ExpType] = "Meals-Travel"
AND EXISTS
(SELECT X.Empl FROM tablename AS X
WHERE X.Empl = tablename.Empl
AND X.ExpDate = tablename.ExpDate
AND X.ExpType = "Lodging")

With a small set of records, you could use the following query to delete
Meals-Travel records where there was no lodging expense on the same day for
the employee.

DELETE * FROM tablename
WHERE [ExpCat] = "Travel Costs"
AND [ExpType] = "Meals-Travel"
AND NOT EXISTS
(SELECT X.Empl FROM tablename AS X
WHERE X.Empl = tablename.Empl
AND X.ExpDate = tablename.ExpDate
AND X.ExpType = "Lodging")

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

For the records shown below, I need to delete the records where a person is
in "travel mode" (ExpCat = "Travel Costs") has meals (ExpType="Meals-Travel")
on the SAME day (ExpDate) as his lodging (ExpType="Lodging"). In this
example, the meals on 10/8 would be deleted, but not the meals on 10/6 and
10/7 because on those days he also had lodging charges.

How can I accomplish this through a query?

A Subquery with an EXISTS clause would do it:

DELETE * FROM tablename
WHERE [ExpCat] = "Travel Costs"
AND [ExpType] = "Meals-Travel"
WHERE EXISTS
(SELECT X.Empl FROM tablename AS X
WHERE X.Empl = tablename.Empl
AND X.ExpDate = tablename.ExpDate
AND X.ExpType = "Lodging")

 

[John W. Vinson]

John Vinson's query would delete the Meals-Travel expenses on the days WITH
lodging. If it worked - but he had one too many WHERE clauses The second
WHERE should be an AND

D'OH!

Thanks for the catch, John. Need some more coffee it's clear!

 

[John Spencer]

Well, now wonder you're having problems. You should never, ever drink clear
coffee - the dark stuff works better and is tastier.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County