By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,285 Members | 1,619 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,285 IT Pros & Developers. It's quick & easy.

delete query frustration

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
"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
Nov 12 '05 #4

P: n/a
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
Nov 12 '05 #5

P: n/a
"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
Nov 12 '05 #6

P: n/a
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
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.