Selective Table Delete question

[John Baker]

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

[MGFoster]

Re: Selective Table Delete question

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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

John Baker wrote:[color=blue]
> 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.[/color]

[John Baker]

Re: Selective Table Delete question

Thanks:

This is what it looks like now:

DELETE History.*
FROM History INNER JOIN [History LatestUpdateDat e] ON History.weekupd ated = [History
LatestUpdateDat e].MaxOfweekupdat ed;


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:
[color=blue]
>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.[/color]

[MGFoster]

Re: Selective Table Delete question

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try one of these:

DELETE DISTINCTROW History.*
FROM History INNER JOIN [History LatestUpdateDat e] ON
HIstory.weekupd ated = [History
LatestUpdateDat e].MaxOfweekupdat ed;

OR

DELETE *
FROM History H
WHERE weekupdated EXISTS
(SELECT * FROM [History LatestUpdateDat e]
WHERE MaxOfweekupdate d = H.WeekUpdated)

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

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

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

iQA/AwUBQfXamYechKq OuFEgEQIYwwCg4/NKkTEyLHn/DiLDPG1qAD9Z5s4 AoKE6
x67wp6cG7supMaQ wX6hr//g3
=6qVa
-----END PGP SIGNATURE-----


John Baker wrote:[color=blue]
> Thanks:
>
> This is what it looks like now:
>
> DELETE History.*
> FROM History INNER JOIN [History LatestUpdateDat e] ON History.weekupd ated = [History
> LatestUpdateDat e].MaxOfweekupdat ed;
>
>
> 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:
>
>[color=green]
>>DELETE A.*[/color]
>[color=green]
>>FROM A INNER JOIN B ON A.ID = B.ID[/color]
>[color=green]
>>Put the A.* (table_name.*) as the only column in the design grid.[/color]
>
>[/color]