473,326 Members | 2,148 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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
9 2836
MMcCarthy
14,534 Expert Mod 8TB
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
Tanis
143 100+
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
psuaudi
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
14,534 Expert Mod 8TB
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
psuaudi
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
14,534 Expert Mod 8TB
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
psuaudi
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
32,556 Expert Mod 16PB
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
psuaudi
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

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

Similar topics

1
by: Phantom_guitarist | last post by:
I am trying to write a query (in PHP) which selects from a database all of the items which are in the future. My query is as follows SELECT * FROM news WHERE ((news.date)>$today ORDER BY date ...
9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
2
by: John | last post by:
Hi - I am trying to perform a simple append query, with no luck. I have a table (MktPrices) that has the following fields: BondID, PriceDate, Price. The objective is to allow the user to input a...
2
by: Willem | last post by:
Hi there, I'm sort of new with doing much record manipulation with queries. Up till now I've been programming VBA and doing record looping to get my results. This works fine but tends to get...
6
by: sheree | last post by:
I would like to create a query where one of the columns of the queries comes from a combo list box on a form. For example, if my table has the following fields: id name interest1 interest2...
3
by: Serious_Practitioner | last post by:
Good day, and thank you in advance for any assistance you can provide. I have a table in an Access 2000 .mdb file, and I've run into something odd and insolvable, at least for me. The database is...
24
by: clare at snyder.on.ca | last post by:
I have a SQL query I need to design to select name and email addresses for policies that are due and not renewed in a given time period. The problem is, the database keeps the information for every...
0
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted...
1
by: csolomon | last post by:
Hello: I am using two queries to get one result set. The issue is, I return no data when I combine them into one query. I have listed both queries, as well as the 3rd query that shows them...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.