Delete query problem

[Mark Reed]

Hi All,

I have a table with a date field and 6 number fields. The number fields are
not in numerical order across the fields and I want number 1 to be the
lowest number and number 6 to be the highest. I have come up with the idea
to create a new table with a date and number field only. I have then put all
the records from the first table into the new table which has only the 2
fields.
I then run an append query into a 3rd table with the min of ball and the
date from table 2. I then need to delete the date and min of ball records
and then run the append query again and this is where I am having the
problem.

The delete query keep throwing up an alert that I need to specify the table
I wish to delete from. Below is the query:
DELETE Table1.Date, Table1.Ball
FROM Table1 LEFT JOIN [min of ball] ON (Table1.Ball = [min of
ball].MinOfBall) AND (Table1.Date = [min of ball].Date)
WHERE (([table1].[date]=[min of ball].[Date]) AND ([table1].[ball]=[min of
ball].[minofball]));

When viewing the query in datasheet view, it shows all the records I need to
delete but when I run the query I get this error. Could anyone offer any
help please????

[MGFoster]

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

Not sure this will work, but....

DELETE Table1.*
FROM Table1 LEFT JOIN [min of ball]
ON (Table1.Ball = [min of ball].MinOfBall)
AND (Table1.Date = [min of ball].Date)

You just need the Table1.* in the DELETE clause and you don't need the
WHERE clause 'cuz it is the same as the ON clause.

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

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

iQA/AwUBQDKjxIechKqOuFEgEQIYogCgi3tI0oH9zqma56rJorRMZp QdJLUAoMpw
tD/boT7SaSAr4BzchV1ClNDc
=1eUH
-----END PGP SIGNATURE-----
Mark Reed wrote:

Hi All,
< snip > I then need to delete the date and min of ball records
and then run the append query again and this is where I am having the
problem.

The delete query keep throwing up an alert that I need to specify the table
I wish to delete from. Below is the query:
DELETE Table1.Date, Table1.Ball
FROM Table1 LEFT JOIN [min of ball] ON (Table1.Ball = [min of
ball].MinOfBall) AND (Table1.Date = [min of ball].Date)
WHERE (([table1].[date]=[min of ball].[Date]) AND ([table1].[ball]=[min of
ball].[minofball]));

< snip >