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...
9 2836
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:
You will need something like ... -
DELETE * FROM TableName WHERE ID_Field IN (SELECT ID_FIELD FROM yourquerynamehere);
-
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]));
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.
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.
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
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.
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 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. - DELETE
-
FROM TableName
-
WHERE (Format([DateField],'yyyymmdd') & _
-
Format([TimeField],'hhnnss')= _
-
(SELECT Max(Format([DateField],'yyyymmdd') & _
-
Format([TimeField],'hhnnss')) FROM TableName))
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
|
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...
| |