I have a "delete query" query!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a one to many relationship and I want to delete all records in the
"many" table EXCEPT those records who have the most recent date. Having
written this it has occurred to me to have a "NOT" criteria.

How would I write it? I know it is a simple thing but need to be sure!
 
If your table and field names don't have spaces in them or otherwise require
square brackets [] then you should be able to write a query that looks like
the following.

DELETE DistinctRow M.*
FROM ManyTable as M
WHERE M.PrimaryKey NOT IN
(
SELECT Primary Key
FROM ManyTable Inner JOIN
(SELECT ForeignKey, Max(DateField) as LatestDate
FROM ManyTable as Temp
GROUP BY ForeignKey) AS q1
ON ManyTable.ForeignKey = q1.ForeignKey
AND ManyTable.DateField = q1.LatestDate )
 
Thanks for the reply but I wasn't expecting anything that complicated. I have
a switchboard form for which I have unbound text boxes for date criteria.
What I had envisioned is being able to put in a date and click a button so it
deletes all the records from the "many" table apart from that date. That is
why I thought a "NOT" criteria would suffice.



John Spencer said:
If your table and field names don't have spaces in them or otherwise require
square brackets [] then you should be able to write a query that looks like
the following.

DELETE DistinctRow M.*
FROM ManyTable as M
WHERE M.PrimaryKey NOT IN
(
SELECT Primary Key
FROM ManyTable Inner JOIN
(SELECT ForeignKey, Max(DateField) as LatestDate
FROM ManyTable as Temp
GROUP BY ForeignKey) AS q1
ON ManyTable.ForeignKey = q1.ForeignKey
AND ManyTable.DateField = q1.LatestDate )



scubadiver said:
I have a one to many relationship and I want to delete all records in the
"many" table EXCEPT those records who have the most recent date. Having
written this it has occurred to me to have a "NOT" criteria.

How would I write it? I know it is a simple thing but need to be sure!
Click to expand...
Click to expand...
 
First, let me add a warning. BACKUP your data before doing the delete. You
cannot UNDO the delete action.

You could use simple criteria but this would delete all the records in the
many table that had dates that were not equal to what you input. My
understanding was that the latest date (Maximum Date) would vary depending
on the One table records.

Example many table (Dates displayed in m/d/y format):
PK FK DateValue
1 --- 2 --- 1/1/06
2 --- 2 --- 1/3/06
3 --- 5 --- 1/1/06
4 --- 5 --- 2/1/06

So you would want to delete rows with PK of 1 and 3. If you simply deleted
all rows with a date of <> #1/3/06, then you would only keep row 2.


scubadiver said:
Thanks for the reply but I wasn't expecting anything that complicated. I
have
a switchboard form for which I have unbound text boxes for date criteria.
What I had envisioned is being able to put in a date and click a button so
it
deletes all the records from the "many" table apart from that date. That
is
why I thought a "NOT" criteria would suffice.



John Spencer said:
If your table and field names don't have spaces in them or otherwise
require
square brackets [] then you should be able to write a query that looks
like
the following.

DELETE DistinctRow M.*
FROM ManyTable as M
WHERE M.PrimaryKey NOT IN
(
SELECT Primary Key
FROM ManyTable Inner JOIN
(SELECT ForeignKey, Max(DateField) as LatestDate
FROM ManyTable as Temp
GROUP BY ForeignKey) AS q1
ON ManyTable.ForeignKey = q1.ForeignKey
AND ManyTable.DateField = q1.LatestDate )



scubadiver said:
I have a one to many relationship and I want to delete all records in
the
"many" table EXCEPT those records who have the most recent date. Having
written this it has occurred to me to have a "NOT" criteria.

How would I write it? I know it is a simple thing but need to be sure!
Click to expand...
Click to expand...
Click to expand...
 
What would the criteria be if I wanted to delete all records except for those
with the two most recent weeks. I don't think I would have to use an unbound
text box to specifiy a date. There must be a way using the max function.

thanks

John Spencer said:
First, let me add a warning. BACKUP your data before doing the delete. You
cannot UNDO the delete action.

You could use simple criteria but this would delete all the records in the
many table that had dates that were not equal to what you input. My
understanding was that the latest date (Maximum Date) would vary depending
on the One table records.

Example many table (Dates displayed in m/d/y format):
PK FK DateValue
1 --- 2 --- 1/1/06
2 --- 2 --- 1/3/06
3 --- 5 --- 1/1/06
4 --- 5 --- 2/1/06

So you would want to delete rows with PK of 1 and 3. If you simply deleted
all rows with a date of <> #1/3/06, then you would only keep row 2.


scubadiver said:
Thanks for the reply but I wasn't expecting anything that complicated. I
have
a switchboard form for which I have unbound text boxes for date criteria.
What I had envisioned is being able to put in a date and click a button so
it
deletes all the records from the "many" table apart from that date. That
is
why I thought a "NOT" criteria would suffice.



John Spencer said:
If your table and field names don't have spaces in them or otherwise
require
square brackets [] then you should be able to write a query that looks
like
the following.

DELETE DistinctRow M.*
FROM ManyTable as M
WHERE M.PrimaryKey NOT IN
(
SELECT Primary Key
FROM ManyTable Inner JOIN
(SELECT ForeignKey, Max(DateField) as LatestDate
FROM ManyTable as Temp
GROUP BY ForeignKey) AS q1
ON ManyTable.ForeignKey = q1.ForeignKey
AND ManyTable.DateField = q1.LatestDate )



I have a one to many relationship and I want to delete all records in
the
"many" table EXCEPT those records who have the most recent date. Having
written this it has occurred to me to have a "NOT" criteria.

How would I write it? I know it is a simple thing but need to be sure!
Click to expand...
Click to expand...
Click to expand...
 
Field: DateField
Criteria: < DateAdd("d",-14,Date())

That should select all records where the date is more than 14 days ago. Is
that what you want?

scubadiver said:
What would the criteria be if I wanted to delete all records except for
those
with the two most recent weeks. I don't think I would have to use an
unbound
text box to specifiy a date. There must be a way using the max function.

thanks

John Spencer said:
First, let me add a warning. BACKUP your data before doing the delete.
You
cannot UNDO the delete action.

You could use simple criteria but this would delete all the records in
the
many table that had dates that were not equal to what you input. My
understanding was that the latest date (Maximum Date) would vary
depending
on the One table records.

Example many table (Dates displayed in m/d/y format):
PK FK DateValue
1 --- 2 --- 1/1/06
2 --- 2 --- 1/3/06
3 --- 5 --- 1/1/06
4 --- 5 --- 2/1/06

So you would want to delete rows with PK of 1 and 3. If you simply
deleted
all rows with a date of <> #1/3/06, then you would only keep row 2.


scubadiver said:
Thanks for the reply but I wasn't expecting anything that complicated.
I
have
a switchboard form for which I have unbound text boxes for date
criteria.
What I had envisioned is being able to put in a date and click a button
so
it
deletes all the records from the "many" table apart from that date.
That
is
why I thought a "NOT" criteria would suffice.



:

If your table and field names don't have spaces in them or otherwise
require
square brackets [] then you should be able to write a query that looks
like
the following.

DELETE DistinctRow M.*
FROM ManyTable as M
WHERE M.PrimaryKey NOT IN
(
SELECT Primary Key
FROM ManyTable Inner JOIN
(SELECT ForeignKey, Max(DateField) as LatestDate
FROM ManyTable as Temp
GROUP BY ForeignKey) AS q1
ON ManyTable.ForeignKey = q1.ForeignKey
AND ManyTable.DateField = q1.LatestDate )



I have a one to many relationship and I want to delete all records in
the
"many" table EXCEPT those records who have the most recent date.
Having
written this it has occurred to me to have a "NOT" criteria.

How would I write it? I know it is a simple thing but need to be
sure!
Click to expand...
Click to expand...
Click to expand...
 
Ive sorted it out.

Thanks


John Spencer said:
Field: DateField
Criteria: < DateAdd("d",-14,Date())

That should select all records where the date is more than 14 days ago. Is
that what you want?

scubadiver said:
What would the criteria be if I wanted to delete all records except for
those
with the two most recent weeks. I don't think I would have to use an
unbound
text box to specifiy a date. There must be a way using the max function.

thanks

John Spencer said:
First, let me add a warning. BACKUP your data before doing the delete.
You
cannot UNDO the delete action.

You could use simple criteria but this would delete all the records in
the
many table that had dates that were not equal to what you input. My
understanding was that the latest date (Maximum Date) would vary
depending
on the One table records.

Example many table (Dates displayed in m/d/y format):
PK FK DateValue
1 --- 2 --- 1/1/06
2 --- 2 --- 1/3/06
3 --- 5 --- 1/1/06
4 --- 5 --- 2/1/06

So you would want to delete rows with PK of 1 and 3. If you simply
deleted
all rows with a date of <> #1/3/06, then you would only keep row 2.


Thanks for the reply but I wasn't expecting anything that complicated.
I
have
a switchboard form for which I have unbound text boxes for date
criteria.
What I had envisioned is being able to put in a date and click a button
so
it
deletes all the records from the "many" table apart from that date.
That
is
why I thought a "NOT" criteria would suffice.



:

If your table and field names don't have spaces in them or otherwise
require
square brackets [] then you should be able to write a query that looks
like
the following.

DELETE DistinctRow M.*
FROM ManyTable as M
WHERE M.PrimaryKey NOT IN
(
SELECT Primary Key
FROM ManyTable Inner JOIN
(SELECT ForeignKey, Max(DateField) as LatestDate
FROM ManyTable as Temp
GROUP BY ForeignKey) AS q1
ON ManyTable.ForeignKey = q1.ForeignKey
AND ManyTable.DateField = q1.LatestDate )



I have a one to many relationship and I want to delete all records in
the
"many" table EXCEPT those records who have the most recent date.
Having
written this it has occurred to me to have a "NOT" criteria.

How would I write it? I know it is a simple thing but need to be
sure!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top