Re: help with delete query
From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 04/29/04
- Next message: jamiewalker_at_wp.shawcable.net: "Bin Laden Captured - photos and video"
- Previous message: Scott: "Query won't list more than 25 records"
- In reply to: Allen Clark: "Re: help with delete query"
- Next in thread: Gary Walter: "Re: help with delete query"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 29 Apr 2004 23:39:03 +0800
Still easiest to make a table that has the valid stuff.
-- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Allen Clark" <acent1@nospamplease.com> wrote in message news:ejjO8nfLEHA.1052@TK2MSFTNGP12.phx.gbl... > Sorry for the confusion, but this is not to delete duplicates. This is to > delete totally useless records that have no valid information in them at > all. I am converting from Access to SQL and I don't want to try to bring > over thousands of records that were incorrectly added. I use the previously > mentioned criteria to identify which records are blank. Now, all I would > like to do is to delete all of these identified records. > > As always, any assistance is greatly appreciated. > > Thanks in advance, > Allen > > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message > news:40908165$0$16584$5a62ac22@freenews.iinet.net.au... > > Hi Allen > > > > It is generally easier to make a new table with the deduplicated records > > than to try to get Access to delete some of the duplicates from the > existing > > table but retain one of them. > > > > Change your query from a Delete query to a Make Table query (Make Table on > > Query menu). > > > > -- > > Allen Browne - Microsoft MVP. Perth, Western Australia. > > Tips for Access users - http://allenbrowne.com/tips.html > > Reply to group, rather than allenbrowne at mvps dot org. > > > > "Allen Clark" <acent1@nospamplease.com> wrote in message > > news:OM%23FBlZLEHA.1388@TK2MSFTNGP09.phx.gbl... > > > I have an Access 2000 db with many linked tables. I have determined > that > > if > > > the persons.first, persons.last, persons.zip, and email.emailaddr fields > > are > > > all blank, the record was incorrectly entered and I would like to get > rid > > of > > > any record that matches these criteria. When I build the delete query, > > the > > > SQL that is generated is as follows: > > > > > > DELETE Persons.* > > > FROM Persons LEFT JOIN EMail ON Persons.id = EMail.Id > > > WHERE (((IsNull([persons].[first]))<>False) AND > > > ((IsNull([persons].[last]))<>False) AND > ((IsNull([persons].[zip]))<>False) > > > AND ((IsNull([email].[emailaddr]))<>False)); > > > > > > > > > If this same selection criteria is used in a simple query with a left > > outer > > > join, and select only the Persons.ID field, I get a listing of 1676 > > records. > > > If I convert it to a delete query, the only message I get is "could not > > > delete from specified tables". > > > > > > Any ideas on what is going on here? > > > > > > Thanks in advance, > > > Allen > > > > > >
- Next message: jamiewalker_at_wp.shawcable.net: "Bin Laden Captured - photos and video"
- Previous message: Scott: "Query won't list more than 25 records"
- In reply to: Allen Clark: "Re: help with delete query"
- Next in thread: Gary Walter: "Re: help with delete query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
