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

help with delete query based on select query that selects the most recent date

P: 27
i have a table with dates that events occured. it looks something like this:

11/18/2006 1:00PM Open
11/18/2006 1:25PM Close

I created a select query that selects the last (most recent) record in the table. I can't seem to get a delete query to work so taht I can delete that record from the table.

In fact, I don't think I've ever gotten a delete query to work...
Nov 30 '06 #1
Share this Question
Share on Google+
9 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
i have a table with dates that events occured. it looks something like this:

11/18/2006 1:00PM Open
11/18/2006 1:25PM Close

I created a select query that selects the last (most recent) record in the table. I can't seem to get a delete query to work so taht I can delete that record from the table.

In fact, I don't think I've ever gotten a delete query to work...
Standard delete query is:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DELETE * FROM TableName;
  3.  
You will need something like ...

Expand|Select|Wrap|Line Numbers
  1. DELETE * FROM TableName WHERE ID_Field IN (SELECT ID_FIELD FROM yourquerynamehere);
  2.  
Nov 30 '06 #2

100+
P: 143
In its simplest form

DELETE TableName.*
FROM TableName;

or, with criteria
DELETE TableName.FieldName
FROM TableName
WHERE (((TableName.FieldName) Is Null));


or, if you are taking the information from a form

DELETE TableName.*, TableName.[Controlname]
FROM TableName
WHERE (((TableName.[FieldName])=[Forms]![frmFormName]![ControlName]));
Nov 30 '06 #3

P: 27
thank you for the reply.

the problem may also lie in the fact that within the table from which I want to delete records, 4 fields are primary keys.

here is a scenario where A > B and I need to delete name1 and name2 where Time and Date are A's (the greatest value which equates to the most recent date and time)

name1 name2 Date Time
A1 A1 B A
B1 B1 A B
A1 A1 A A
A1 A1 B B

my select query can return the 3rd row with the values of A1, A1, A, A, but I cannot get the delete query to use these values as parameters to delete that specific record from the table. Please note that it is possible and likely that name1 and name2 will have duplicate entries within the table, but with different dates and times.
Nov 30 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
The simple answer is four fields is too big a primary key. Can you add a new primary key with an autonumber?



thank you for the reply.

the problem may also lie in the fact that within the table from which I want to delete records, 4 fields are primary keys.

here is a scenario where A > B and I need to delete name1 and name2 where Time and Date are A's (the greatest value which equates to the most recent date and time)

name1 name2 Date Time
A1 A1 B A
B1 B1 A B
A1 A1 A A
A1 A1 B B

my select query can return the 3rd row with the values of A1, A1, A, A, but I cannot get the delete query to use these values as parameters to delete that specific record from the table. Please note that it is possible and likely that name1 and name2 will have duplicate entries within the table, but with different dates and times.
Nov 30 '06 #5

P: 27
The simple answer is four fields is too big a primary key. Can you add a new primary key with an autonumber?
that probably would simpify things...yes i could
Nov 30 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
that probably would simpify things...yes i could
Once you have done that you could create your query to return this field and then use the delete statement I gave you.
Nov 30 '06 #7

P: 27
Once you have done that you could create your query to return this field and then use the delete statement I gave you.
I will give that a shot. Thank you for the help!
Nov 30 '06 #8

NeoPa
Expert Mod 15k+
P: 31,602
This is made much more complicated by the fact that you are storing dates and times in separate fields.
You will therefore need something as complicated as the code below.
Expand|Select|Wrap|Line Numbers
  1. DELETE
  2. FROM TableName
  3. WHERE (Format([DateField],'yyyymmdd') & _
  4.     Format([TimeField],'hhnnss')= _
  5.     (SELECT Max(Format([DateField],'yyyymmdd') & _
  6.     Format([TimeField],'hhnnss')) FROM TableName))
Nov 30 '06 #9

P: 27
I agree. I now just figured out how to append the date and time into a single field, which will simplify my operations.

Thank you again for your help. I will try out the nested sql code. I am still learning a lot about SQL commands.
Dec 1 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.