Deleting records from a table in Access

[Guest]

I am trying to delete the min and max dates out of a set of data before
archiving the set of data. I have written a set of queries for both the min
and the max dates. For both the min and the max, the first query identifies
the date I want to target and the second query finds all the matching records
in the target table. If I leave the second query as a select query, it
works. The minute I set it up as a delete query, it gives me the following:

"Could not delete from specified tables. (Error 3086)
You tried to delete data from one or more tables, but the deletion could not
be completed.

Possible causes:

You do not have permission to modify the table. To change your permissions
assignments, see your system administrator or the table's creator.
The database was opened for read-only access. The database is read-only for
one of these reasons:
You used the OpenDatabase method and opened the database for read-only
access.
The database file is defined as read-only in the database server operating
system or by your network.
In a network environment, you do not have write privileges for the database
file.
In Microsoft® Visual Basic®, you used the Data control and set the ReadOnly
property to True.
To delete the data, close the database, resolve the read-only condition, and
then reopen the file for read/write access."

I have checked....all my permissions are read/write....what am I missing?
Joe

 

[Guest]

Try using correlated subqueries in the WHERE clause, e.g:

DELETE *
FROM YourTable AS T1
WHERE YourDate=
(SELECT MAX(YourDate)
FROM YourTable AS T2
WHERE T2.SomeField= T1.SomeField)
OR YourDate=
(SELECT MIN(YourDate)
FROM YourTable AS T3
WHERE T3.SomeField= T1.SomeField);

Ken Sheridan
Stafford, England

 

[Guest]

Thank you, Ken
I am still having trouble....you will have to excuse me....I don't have any
formal training....mostly, I have taught myself. Let's talk just about the
MIN queries....
Table T1 has my data for the week. Query 1 identifies the MIN date in the
T1 table. Query 2 then identifies all instances of the MIN date in T1. I am
trying to get Query 2 to be a delete query to delete this MIN data out of the
T1 table, but, it still gives me the same error. I did not completely
understand your answer below, so, I tried different combinations and still
could not get it to work. Do I need to put your input into a third query or
can I do this in the second query? What is the exact entry I need to make in
the design view of the query and where do I enter it?

Thank you for being patient.

Joe

 

[Guest]

Joe:

In effect the query for which I sent you the SQL does the same as your 2
queries, but as well as deleting the MIN dates also deletes the MAX dates,
which in your first post you said you wanted to do also. The query I sent
you is completely self contained. Your Query 1 is represented by the second
subquery, which is enclosed in parentheses in the outer (main) query. It’s
the one beginning SELECT MIN(YourDate). This query identifies the earliest
date. The subquery is in the outer query's WHERE clause, so any row(s) which
match this MIN date will be deleted. The first subquery does the same for
the MAX date. Note how a Boolean OR is used so that rows from the outer
query which match either the MIN or the MAX dates will be deleted.

I said in my earlier reply that the subqueries should be correlated with the
outer query, and the SQL I sent you does this. From what you say in your
last post, however, it might not be necessary to correlate them.

So you understand this let me first deal with a situation where correlation
would be necessary. This would be the case if your table included data for a
number of weeks and you wanted to delete the MIN and MAX dated rows for each
week. It is then necessary to correlate the subqueries and outer queries on
the weeks. These can be identified from the dates by the Year of the date
and the Week Number of the date (the table might contain data for the same
weeks in separate years, so you need to identify them separately. The Year
can be obtained with the YEAR function and week number with DATEPART
function. As the outer query and the subqueries use the same table you need
to be able to differentiate between them to achieve the correlation. This is
done by giving the table aliases T1, T2 and T3. So putting all that together
the query would go like this (you'd substitute your own table and field names
of course)

DELETE *
FROM YourTable AS T1
WHERE YourDate=
(SELECT MAX(YourDate)
FROM YourTable AS T2
WHERE YEAR(T2.YourDate) = YEAR(T1.YourDate)
AND DATEPART("ww",T2.YourDate) = DATEPART("ww",T1.YourDate))
OR YourDate=
(SELECT MIN(YourDate)
FROM YourTable AS T3
WHERE YEAR(T2.YourDate) = YEAR(T1.YourDate)
AND DATEPART("ww",T3.YourDate) = DATEPART("ww",T1.YourDate));

By correlating the subqueries on the year and week number each will return
the MIN and MAX date for each week of each row of the outer query, so when
the date in the row of the outer query is one of those two MIN or MAX values
the row will be deleted.

However, you say your table has ' data for the week', which suggests it has
only one week's data; right? In which case there is obviously no need to
correlate the subqueries on the week as only one week is represented, so the
query can use much simpler uncorrelated subqueries:

DELETE *
FROM YourTable
WHERE YourDate=
(SELECT MAX(YourDate)
FROM YourTable)
OR YourDate=
(SELECT MIN(YourDate)
FROM YourTable);

You cannot do this type of thing entirely in query design view. Its quite
simple to enter all the SQL directly. In fact you can just copy and paste
the above into a query in SQL view and then change the table and field names
to your own. In design view the most you could do would be to set up the
outer query, and then enter the SQL for each subquery in separate criteria
rows of the YourDate column.

This does make me wonder whether you are using separate tables for each
week's data? If so that is not considered to be good database design. So
long as you have dates in a column in the table you can store as many weeks
data as you wish in a single table. Individual weeks (or days, quarters etc)
data can easily be extracted with queries. Using separate tables amounts to
encoding data as table names, whereas the database relational model
stipulates that data should be stored as values at column positions in rows
in tables and in no other way. In the relational model tables represent
entity type and columns (fields) represent attribute types. The week is
really an attribute type, but you don't need a column specifically for it as
it can be derived from the YourDate value. In fact to have a separate column
for the week would constitute redundancy, which leaves the door open to
'update anomalies' in hat a date and week value in the same row could be
inconsistent with each other.

As well as contravening the principles of the theoretical model this does
have practical implications as when querying over a period of more than one
week involves a UNION of what could be many tables, if a year's data is being
queried say, whereas this can be done very simply if all the data is in one
table.

If you are simply storing the data for one week and then emptying and
refilling the same table each week that is fine, of course, and you mentioned
archiving, which suggests that is the case. But I'd still raise an eyebrow
over the need for archiving on a weekly basis, or at all, unless you are
inserting an exceptionally large number of rows into the table each week.

Ken Sheridan
Stafford, England