Delete records

I have a table called residency
I create a query (named notdate) that uses the residency table and compares
to another table. I want to take the results of the query and delete the
records residency table.
Do I need to create another table then create a delete query?
I can not seem to get the delete query to work using the notdate query.
Any thoughts and suggestions would be appreciated.

Post the SQL for what you are trying.

Chances are that you need to use the query as a sub-query in the Where
clause of the table where you want to delete records. I could look something
like this:

DELETE RC_Main.*
FROM RC_Main
WHERE RC_Main.ID In (select ID from [Pending Deletion IDs]);

Make a backup of the database first......
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"pdehner" wrote:

I have a table called residency
I create a query (named notdate) that uses the residency table and compares
to another table. I want to take the results of the query and delete the
records residency table.
Do I need to create another table then create a delete query?
I can not seem to get the delete query to work using the notdate query.
Any thoughts and suggestions would be appreciated.

I have a select query that is called residencyquery that pulls records that
have tdate as blank. Those records are the ones I want deleted in my
residency table. UCI is a unquie field in residency table.

I have delete query that states:

DELETE residency.*
FROM residency
WHERE residency.uci In ([residencyquery]);

Every time I run the delete query it asks for the parameter value (pop up
screen) for residencyquery.

What am I doing wrong?
Help is greatly appreciated.

"Jerry Whittle" wrote:

Post the SQL for what you are trying.

Chances are that you need to use the query as a sub-query in the Where
clause of the table where you want to delete records. I could look something
like this:

DELETE RC_Main.*
FROM RC_Main
WHERE RC_Main.ID In (select ID from [Pending Deletion IDs]);

Make a backup of the database first......
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"pdehner" wrote:

I have a table called residency
I create a query (named notdate) that uses the residency table and compares
to another table. I want to take the results of the query and delete the
records residency table.
Do I need to create another table then create a delete query?
I can not seem to get the delete query to work using the notdate query.
Any thoughts and suggestions would be appreciated.

You need the SQL statement from residencyquery in there. You just can't call
the query that way. It would look something like:

DELETE residency.*
FROM residency
WHERE residency.uci In
(SELECT uci
FROM YourTable
WHERE tdate is null) ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"pdehner" wrote:

I have a select query that is called residencyquery that pulls records that
have tdate as blank. Those records are the ones I want deleted in my
residency table. UCI is a unquie field in residency table.

I have delete query that states:

DELETE residency.*
FROM residency
WHERE residency.uci In ([residencyquery]);

Every time I run the delete query it asks for the parameter value (pop up
screen) for residencyquery.

What am I doing wrong?
Help is greatly appreciated.

"Jerry Whittle" wrote:

Post the SQL for what you are trying.

Chances are that you need to use the query as a sub-query in the Where
clause of the table where you want to delete records. I could look something
like this:

DELETE RC_Main.*
FROM RC_Main
WHERE RC_Main.ID In (select ID from [Pending Deletion IDs]);

Make a backup of the database first......
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"pdehner" wrote:

I have a table called residency
I create a query (named notdate) that uses the residency table and compares
to another table. I want to take the results of the query and delete the
records residency table.
Do I need to create another table then create a delete query?
I can not seem to get the delete query to work using the notdate query.
Any thoughts and suggestions would be appreciated.

Thanks exactly what I needed. It works!


"Jerry Whittle" wrote:

You need the SQL statement from residencyquery in there. You just can't call
the query that way. It would look something like:

DELETE residency.*
FROM residency
WHERE residency.uci In
(SELECT uci
FROM YourTable
WHERE tdate is null) ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"pdehner" wrote:

I have a select query that is called residencyquery that pulls records that
have tdate as blank. Those records are the ones I want deleted in my
residency table. UCI is a unquie field in residency table.

I have delete query that states:

DELETE residency.*
FROM residency
WHERE residency.uci In ([residencyquery]);

Every time I run the delete query it asks for the parameter value (pop up
screen) for residencyquery.

What am I doing wrong?
Help is greatly appreciated.

"Jerry Whittle" wrote:

Post the SQL for what you are trying.

Chances are that you need to use the query as a sub-query in the Where
clause of the table where you want to delete records. I could look something
like this:

DELETE RC_Main.*
FROM RC_Main
WHERE RC_Main.ID In (select ID from [Pending Deletion IDs]);

Make a backup of the database first......
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"pdehner" wrote:

I have a table called residency
I create a query (named notdate) that uses the residency table and compares
to another table. I want to take the results of the query and delete the
records residency table.
Do I need to create another table then create a delete query?
I can not seem to get the delete query to work using the notdate query.
Any thoughts and suggestions would be appreciated.

"pdehner" escribió en el mensaje de
...
I have a table called residency
I create a query (named notdate) that uses the residency table and
compares
to another table. I want to take the results of the query and delete the
records residency table.
Do I need to create another table then create a delete query?Name ArtistComposer Album Grouping Genre Size Time DiscNumber Disc Count Track Number Track Count Year Date Modified Date Added Bit
Rate Sample Rate Volume Adjustment Kind Equalizer Comments Play Count Last
Played Skip Count Last Skipped My Rating Location
Beyonce - Irreplaceable 58509024 253 5/15/2009 10:42 PM 5/20/2009
12:26 PM QuickTime movie file C:\Users\jose\Shared\Beyonce -
Irreplaceable.mpg
Bon Jovi - Its My Life 46045504 264 5/15/2009 8:05 PM 5/20/2009
12:26 PM QuickTime movie file C:\Users\jose\Shared\Bon Jovi - Its
My Life.mpg
CBS new series for el chapo sinaloa music video fans 29488408 2675/15/20098:27 PM 5/20/2009 12:26 PM 78 QuickTime movie file
C:\Users\jose\Shared\CBS new series for el chapo sinaloa music video
fans.mov
commercial for hip hop music video 2008 fans 24009216 1175/17/20096:35 PM 5/20/2009 12:26 PM QuickTime movie file
C:\Users\jose\Shared\commercial for hip hop music video 2008 fans.mpg
Emenem - Like Toy Soldiers 72543660 314 5/17/2009 6:09 PM5/20/200912:26 PM QuickTime movie file C:\Users\jose\Shared\Emenem - Like
Toy Soldiers .mpeg
Eminem - Stan (Full Uncut Version) 85225728 488 5/15/2009 8:18 PM5/20/200912:26 PM QuickTime movie file C:\Users\jose\Shared\Eminem - Stan
(Full Uncut Version).mpeg
Eminem - Superman (Music Video) 50165764 294 5/15/2009 9:30 PM5/20/200912:26 PM QuickTime movie file C:\Users\jose\Shared\Eminem -
Superman (Music Video).mpg
FaveReds video for el chapo sinaloa music video lovers 21750272 1105/15/20098:29 PM 5/20/2009 12:26 PM QuickTime movie file
C:\Users\jose\Shared\FaveReds video for el chapo sinaloa music video
lovers.mpg
Guns n' Roses - Knocking On Heaven's Door 54114340 310 5/15/2009
8:06 PM 5/20/2009 12:26 PM QuickTime movie fileC:\Users\jose\Shared\Gunsn' Roses - Knocking On Heaven's Door.mpg
Guns N' Roses - Sweet Child O' Mine 44785668 265 5/17/2009 7:54 PM5/20/200912:26 PM QuickTime movie file C:\Users\jose\Shared\Guns N' Roses -
Sweet Child O' Mine.mpg
HeckleU for mexicanos music video 2009 pg fans 42780820 4445/15/20099:17 PM 5/20/2009 12:26 PM 78 QuickTime movie file
C:\Users\jose\Shared\HeckleU for mexicanos music video 2009 pg fans.mov
Lil John & The Eastside Boyz ft. Ice Cube - Roll Call 53919124 2335/17/20097:53 PM 5/20/2009 12:26 PM QuickTime movie file
C:\Users\jose\Shared\Lil John & The Eastside Boyz ft. Ice Cube - Roll
Call.mpg
Nickelback - If Everyone Cared 40478108 232 5/17/2009 8:46 PM5/20/200912:27 PM QuickTime movie file C:\Users\jose\Shared\Nickelback - If
Everyone Cared.mpg
Papa Roach - Broken Home 39674560 227 5/19/2009 8:36 PM 5/20/2009
12:27 PM QuickTime movie file C:\Users\jose\Shared\Papa Roach -
Broken Home.mpg
papa roach - scars 36742440 210 5/19/2009 8:22 PM 5/20/2009 12:27
PM QuickTime movie file C:\Users\jose\Shared\papa roach -
scars.mpg
Papa_Roach_-_Getting_Away_With_Murder_-_videopimp 34234844 1965/19/20098:23 PM 5/20/2009 12:27 PM QuickTime movie file
C:\Users\jose\Shared\Papa_Roach_-_Getting_Away_With_Murder_-_videopimp.mpeg
Sean Paul - Just Gimmie The Light (Best Music Video - Raggae Hip Hop Dance)88395668 192 5/15/20099:38 PM 5/20/2009 12:27 PM QuickTime movie file
C:\Users\jose\Shared\Sean Paul - Just Gimmie The Light (Best Music Video -
Raggae Hip Hop Dance).mpg
Sean Paul - Temperature 52204012 217 5/15/2009 10:18 PM 5/20/2009
12:27 PM QuickTime movie file C:\Users\jose\Shared\Sean Paul -
Temperature.mpg
Shell race video for mexicanos music video 2009 pg fans 5312510 1145/15/20098:44 PM 5/20/2009 12:27 PM 81 QuickTime movie file 1 11/10/2009 4:33 PM
C:\Users\jose\Shared\Shell race video for mexicanos music video 2009 pg
fans.mov
starburst candy advertisement for el chapo sinaloa music video fans23314944 114 5/15/20097:44 PM 5/20/2009 12:27 PM QuickTime movie file
C:\Users\jose\Shared\starburst candy advertisement for el chapo sinaloa
music video fans.mpg
starburst flavors ad for mexicanos music video 2009 pg fans 12244479114 5/15/20098:52 PM 5/20/2009 12:27 PM 78 QuickTime movie file
C:\Users\jose\Shared\starburst flavors ad for mexicanos music video 2009 pg
fans.mov
2pac - Changes(uncensored) 48250164 277 5/29/2009 1:04 PM11/4/200910:53 PM QuickTime movie file C:\Users\jose\Shared\2pac -
Changes(uncensored).mpg
50 Cent - In da Club 43084824 234 5/15/2009 9:27 PM 5/20/2009
12:26 PM QuickTime movie file C:\Users\jose\Shared\50 Cent - In da
Club.mpg

I can not seem to get the delete query to work using the notdate query.
Any thoughts and suggestions would be appreciated.