473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Selective Table Delete question

Hi:

I have a table with many months of data on it. I am attempting to create a delete
transaction, based on matching a date in the table with a date in another table. The match
works perfectly in a Select Query, but when I change the query to a delete, it asks me to
specify which table I wish to delete from. I just cannot figure out how to do this, and
would appreciate a hint! I am using the Access Query interface NOT the SQL interface.

Thanks in Advance

JOhn Baker
Nov 13 '05 #1
3 1430
DELETE A.*
FROM A INNER JOIN B ON A.ID = B.ID

Put the A.* (table_name.*) as the only column in the design grid.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

John Baker wrote:
Hi:

I have a table with many months of data on it. I am attempting to create a delete
transaction, based on matching a date in the table with a date in another table. The match
works perfectly in a Select Query, but when I change the query to a delete, it asks me to
specify which table I wish to delete from. I just cannot figure out how to do this, and
would appreciate a hint! I am using the Access Query interface NOT the SQL interface.

Nov 13 '05 #2
Thanks:

This is what it looks like now:

DELETE History.*
FROM History INNER JOIN [History LatestUpdateDate] ON History.weekupdated = [History
LatestUpdateDate].MaxOfweekupdated;
I get the error "Could not delete from specified tables". I guess I am still doing
something wrong, but dont know what!

John!

MGFoster <me@privacy.com> wrote:
DELETE A.*
FROM A INNER JOIN B ON A.ID = B.ID

Put the A.* (table_name.*) as the only column in the design grid.


Nov 13 '05 #3
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try one of these:

DELETE DISTINCTROW History.*
FROM History INNER JOIN [History LatestUpdateDate] ON
HIstory.weekupdated = [History
LatestUpdateDate].MaxOfweekupdated;

OR

DELETE *
FROM History H
WHERE weekupdated EXISTS
(SELECT * FROM [History LatestUpdateDate]
WHERE MaxOfweekupdated = H.WeekUpdated)

JET SQL is finicky about updateable queries in DELETE & UPDATE queries.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQfXamYechKqOuFEgEQIYwwCg4/NKkTEyLHn/DiLDPG1qAD9Z5s4AoKE6
x67wp6cG7supMaQwX6hr//g3
=6qVa
-----END PGP SIGNATURE-----
John Baker wrote:
Thanks:

This is what it looks like now:

DELETE History.*
FROM History INNER JOIN [History LatestUpdateDate] ON History.weekupdated = [History
LatestUpdateDate].MaxOfweekupdated;
I get the error "Could not delete from specified tables". I guess I am still doing
something wrong, but dont know what!

John!

MGFoster <me@privacy.com> wrote:

DELETE A.*

FROM A INNER JOIN B ON A.ID = B.ID

Put the A.* (table_name.*) as the only column in the design grid.


Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Vic | last post by:
Hi all, When I test the Delete multi table function in MySQL, DELETE table_name ...] FROM table-references I accidentally delete all data in one table. All data in that table are gone...
55
by: Steve Jorgensen | last post by:
In a recent thread, RKC (correctly, I believe), took issue with my use of multiple parameters in a Property Let procedure to pass dimensional arguments on the basis that, although it works, it's...
3
by: Rich N | last post by:
I want to move several records from one table to a new table. My database is set up so that there's the "regular" database, and the ".be" database. In which do I use the Make Table Query?
2
by: Robert | last post by:
I have a simple database with 3 tables, Companies, Contacts and Indusrty. Companies CompanyID (Primary Key) CompanyName Industry Contacts ContactID (Primary Key) FirstName CompanyiD ...
16
by: Lyle Fairfield | last post by:
There is an MS-SQL table named Bugs_Comments_and_Suggestions. There is a form named Bugs_Comments_and_Suggestions. To allow John Doe to use this form, we GRANT him LOGIN and ACCESS permissions...
1
by: Solitus | last post by:
I have databinding problem with a repeater <ItemTemplate> <tr> <!-- fixed content --> </tr> <tr> <!-- optional content --> </tr> </ItemTemplate>
3
by: Alex Satrapa | last post by:
There's some mention in the (old!) documentation that constraints such as foreign keys won't include data from inheriting tables, eg: CREATE TABLE foo ( id SERIAL PRIMARY KEY ); CREATE TABLE...
7
by: Lisa | last post by:
I have an Access 2000 application that uses the following function to re-link my tables when I switch from my Current back end to a Dummy back end. I also use it to refresh my links. Function...
4
by: alex.hatzisavas | last post by:
Dear all, I'm trying to do a selective deletion from an 'Archive' table based on dates. Here is the setup: Table 'Archive' has a date field ( ). Another table ('Cutoffs') defines the Low...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.