|
|
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.
|
|
0
|
|
|
|
Reply
|
 Utf
|
12/15/2009 8:46:01 PM |
|
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.
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/15/2009 9:47:01 PM
|
|
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.
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/16/2009 5:55:01 PM
|
|
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.
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/16/2009 6:08:01 PM
|
|
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.
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/16/2009 7:41:01 PM
|
|
"pdehner" <pdehner@discussions.microsoft.com> escribió en el mensaje de
noticias:A093B1EC-BF50-4D58-91F3-359E64DBE2F8@microsoft.com...
> 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.
|
|
|
0
|
|
|
|
Reply
|
edward
|
12/21/2009 5:05:14 PM
|
|
|
5 Replies
169 Views
(page loaded in 0.11 seconds)
Similiar Articles: DoCmd to delete records - microsoft.public.access.formscoding ...Hi I have a command button that when clicked deletes certain records from a subform based on an application number being equal. It deletes all record... Automatically Delete records from Recordset - microsoft.public ...Automatically Delete records from Recordset - microsoft.public ... Delete record using vba - microsoft.public.access Automatically Delete records from Recordset ... Deleting record from bindingsource - microsoft.public.dotnet ...Hi I have a winform app with form control bound to a binding source TblClientsBindingSource. The binding source has a data table as its data so... Delete REcord isnt Available - microsoft.public.access.formscoding ...Ok none of that applies. I seriously thing it has to do with the beforeupdate and afterupdate events. Is there a way to suppress these events if the delete button is ... Delete record using vba - microsoft.public.accessI have a table whose primary key is text (length 50). I need to programmatically delete one record. What is the best way, just use DoCmd.RunSQL or... Delete record in mailmerge - microsoft.public.word.vba.general ...In VBA I can move to the last record in a Word 2007 mailmerge datasource, using ActiveDocument.MailMerge.DataSource.ActiveRecord = wdLastRecord How can I delete this ... Help with using command button to delete a record from a table ...This does ALMOST everything I need. I added the line where you instructed. Now, when I hit the button and delete a name, the name still appears in the combo box as ... Error deleting record - acCmdDeleteRecord isn't available ...I have a form and subform combination for entering two related records, an Inventory item Master definition and an Inventory item sub definition. Th... Creating a 'Delete Record' command button. - microsoft ...I know that I can click the 'delete' button up on the tool bar, but I'd like to create a command button in my form that would allow me to delete the c... Insert, copy, update, delete records through VBA - microsoft ...Hi, I have an excel'07 database of employees where I have to add, update, delete records very often. Since it is not practical for me to open the... SQLCourse - Lesson 7: Deleting RecordsLesson 7: Deleting records; basic use of the DELETE statement. From SQLCourse.com: an interactive online training course for SQL beginners. How to: Delete Records in a Database - Microsoft Corporation ...To delete records from a database, use the TableAdapter.Update method or the TableAdapter.Delete method. Or, if your application does not use TableAdapters, you can ... How to: Insert, Update, and Delete Records From a Table Using ...There are essentially two methods for adding records to a table. The first is to add one record at a time; the second is to add many records at a time. In both cases ... How to Delete Search Records | eHow.comEach time we search for something on search engines like Google, Yahoo! or Bing, the Web browser stores a copy of the search term for future use. It is a good idea to ... SQL DELETE StatementNote: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all ... 7/21/2012 6:54:20 PM
|
|
|
|
|
|
|
|
|
Search |
Post Question |
Groups |
Stream |
About |
Register
16 followers. 