delete query frustration

[Mark Reed]

Hi all,
Please help. I have a table with 2 fields of which I am trying to change
a select query into a delete query.

the select query is:

SELECT Table1.Date, Min(Table1.Ball) AS MinOfBall
FROM Table1
GROUP BY Table1.Date;

I am having trouble getting a delete query to work as I don't know how to
set the criteria.

Any help will be a god send

Mark

[Wayne Morgan]

What criteria do you want?

Example:
Delete * From Table1 Where Table1.Field1=23
or
Delete * From Table1 Where Table1.Field1="cats"

--
Wayne Morgan
Microsoft Access MVP
"Mark Reed" <ma*********@ntlworld.com> wrote in message
news:Ngw_b.18486$ft.17163@newsfe1-win...

Hi all,
Please help. I have a table with 2 fields of which I am trying to change a select query into a delete query.

the select query is:

SELECT Table1.Date, Min(Table1.Ball) AS MinOfBall
FROM Table1
GROUP BY Table1.Date;

I am having trouble getting a delete query to work as I don't know how to
set the criteria.

Any help will be a god send

Mark

[Mike Storr]

On Mon, 23 Feb 2004 23:46:49 -0000, Mark Reed wrote:

Hi all,
Please help. I have a table with 2 fields of which I am trying to change
a select query into a delete query.

the select query is:

SELECT Table1.Date, Min(Table1.Ball) AS MinOfBall
FROM Table1
GROUP BY Table1.Date;

I am having trouble getting a delete query to work as I don't know how to
set the criteria.

Any help will be a god send

Mark

The criteria for all queries work the same. If a record contains the
matches you specify, then it will return or act upon that record.
The query builder makes it very simple to add your criteria. Take an hour
and flip thought the help file on how to use it.
--
Mike Storr
www.veraccess.com

[Allen Agee]

"Mark Reed" <ma*********@ntlworld.com> wrote in message news:<Ngw_b.18486$ft.17163@newsfe1-win>...

Hi all,
Please help. I have a table with 2 fields of which I am trying to change
a select query into a delete query.

the select query is:

SELECT Table1.Date, Min(Table1.Ball) AS MinOfBall
FROM Table1
GROUP BY Table1.Date;

I am having trouble getting a delete query to work as I don't know how to
set the criteria.

Any help will be a god send

Mark

Mark,

The Group By makes your Select query non-updateable.

You might try using a subquery like:

DELETE FROM Table1 WHERE [Date] IN (SELECT Table1.[Date],
Min(Table1.Ball) AS MinOfBall FROM Table1 Group By Table1.[Date])

You might also want to avoid keywords like [Date] in your field names.

Regards,

Allen

[Mark Reed]

Hi Allen,
I have tried your query but I get the following error message....."you
have written a subquery that can return more than one field without using
the exists reserved word in the main query's FROM clause. Revise the select
statement of the subquery to request only one field".

I had to change it slightly as there were no fields selected to delete.

Also, I can't find any reference within the help files to "exists".

Mark

"Allen Agee" <ag****@yahoo.com> wrote in message
news:45*************************@posting.google.co m...
"Mark Reed" <ma*********@ntlworld.com> wrote in message
news:<Ngw_b.18486$ft.17163@newsfe1-win>...

Hi all,
Please help. I have a table with 2 fields of which I am trying to change a select query into a delete query.

the select query is:

SELECT Table1.Date, Min(Table1.Ball) AS MinOfBall
FROM Table1
GROUP BY Table1.Date;

I am having trouble getting a delete query to work as I don't know how to
set the criteria.

Any help will be a god send

Mark

Mark,

The Group By makes your Select query non-updateable.

You might try using a subquery like:

DELETE FROM Table1 WHERE [Date] IN (SELECT Table1.[Date],
Min(Table1.Ball) AS MinOfBall FROM Table1 Group By Table1.[Date])

You might also want to avoid keywords like [Date] in your field names.

Regards,

Allen

[Allen Agee]

"Mark Reed" <ma*********@ntlworld.com> wrote in message news:<6MG_b.662$bJ1.237@newsfe1-win>...

Hi Allen,
I have tried your query but I get the following error message....."you
have written a subquery that can return more than one field without using
the exists reserved word in the main query's FROM clause. Revise the select
statement of the subquery to request only one field".

I had to change it slightly as there were no fields selected to delete.

Also, I can't find any reference within the help files to "exists".

Mark

Mark,

That's what I get for hitting the send button before testing the
query!

It looks like your original query, SELECT Table1.[Date],
Min(Table1.Ball) AS MinOfBall FROM Table1 Group By Table1.[Date], is
trying to delete the minimum [Ball] for each Date.

To be safe, we ought to use the primary key of Table1 to prevent
deleting too many records. We can split the process into a select
query (qrySel) and our delete query (qryDel).

qrySel would look like:
SELECT First(Table1.ID) AS FirstOfID, Table1.date, Min(Table1.ball) AS
MinOfball FROM Table1 GROUP BY Table1.date;

qryDel would look like:
DELETE * FROM Table1 WHERE id in (Select FirstOfID from qrySel);

It's a good idea to make a backup before testing delete queries like
this.

Regards,
Allen

[Mark Reed]

Thanks very very much Allen, works a treat
"Allen Agee" <ag****@yahoo.com> wrote in message news:45*************************@posting.google.co m...
"Mark Reed" <ma*********@ntlworld.com> wrote in message news:<6MG_b.662$bJ1.237@newsfe1-win>...

Hi Allen,
I have tried your query but I get the following error message....."you
have written a subquery that can return more than one field without using
the exists reserved word in the main query's FROM clause. Revise the select
statement of the subquery to request only one field".

I had to change it slightly as there were no fields selected to delete.

Also, I can't find any reference within the help files to "exists".

Mark

Mark,

That's what I get for hitting the send button before testing the
query!

It looks like your original query, SELECT Table1.[Date],
Min(Table1.Ball) AS MinOfBall FROM Table1 Group By Table1.[Date], is
trying to delete the minimum [Ball] for each Date.

To be safe, we ought to use the primary key of Table1 to prevent
deleting too many records. We can split the process into a select
query (qrySel) and our delete query (qryDel).

qrySel would look like:
SELECT First(Table1.ID) AS FirstOfID, Table1.date, Min(Table1.ball) AS
MinOfball FROM Table1 GROUP BY Table1.date;

qryDel would look like:
DELETE * FROM Table1 WHERE id in (Select FirstOfID from qrySel);

It's a good idea to make a backup before testing delete queries like
this.

Regards,
Allen