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

[psuaudi]

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...

[MMcCarthy]

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:

   
DELETE * FROM TableName;

You will need something like ...

DELETE * FROM TableName WHERE ID_Field IN (SELECT ID_FIELD FROM yourquerynamehere);

[Tanis]

In its simplest form

DELETE TableName.*
FROM TableName;

or, with criteria
DELETE TableName.Field Name
FROM TableName
WHERE (((TableName.Fi eldName) Is Null));


or, if you are taking the information from a form

DELETE TableName.*, TableName.[Controlname]
FROM TableName
WHERE (((TableName.[FieldName])=[Forms]![frmFormName]![ControlName]));

[psuaudi]

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.

[MMcCarthy]

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.

[psuaudi]

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

[MMcCarthy]

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.

[psuaudi]

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!

[NeoPa]

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.

DELETE
FROM TableName
WHERE (Format([DateField],'yyyymmdd') & _
    Format([TimeField],'hhnnss')= _
    (SELECT Max(Format([DateField],'yyyymmdd') & _
    Format([TimeField],'hhnnss')) FROM TableName))

[psuaudi]

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.