move records based date in field
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
move records based date in field
I have two table:
tabl1 and table_history
I need a sql to move in "one shot only" all records from table1 to append into table _history but only the oldest records from table1 based the 3 oldest date.
Example:
field date of table1 contain
11/10/2010
04/10/2010
15/12/2008
14/11/2007
09/01/1989
07/11/2010
11/11/2001
....
in this case move into table_history all records from table1
09/01/1989
15/12/2008
14/11/2007
11/11/2001
i effect i want to maintain into table1 always the records with the 3 newest date.
Note:
- i use vb and ject conn
- all two tabel have the same strucutre fileds name and formatt field
tabl1 and table_history
I need a sql to move in "one shot only" all records from table1 to append into table _history but only the oldest records from table1 based the 3 oldest date.
Example:
field date of table1 contain
11/10/2010
04/10/2010
15/12/2008
14/11/2007
09/01/1989
07/11/2010
11/11/2001
....
in this case move into table_history all records from table1
09/01/1989
15/12/2008
14/11/2007
11/11/2001
i effect i want to maintain into table1 always the records with the 3 newest date.
Note:
- i use vb and ject conn
- all two tabel have the same strucutre fileds name and formatt field
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: move records based date in filed
You cannot do this in "one shot". It will take two steps:
1) Append the records that satisfy the condition to table_history. The SQL is:
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField] Not In (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)
or alternatively
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
2) Delete the records that satisfy the condition from table1. The SQL is:
DELETE [DateField] FROM table1 WHERE [DateField] Not In (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)
or
DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
1) Append the records that satisfy the condition to table_history. The SQL is:
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField] Not In (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)
or alternatively
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
2) Delete the records that satisfy the condition from table1. The SQL is:
DELETE [DateField] FROM table1 WHERE [DateField] Not In (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)
or
DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
Re: move records based date in filed
No words!HansV wrote:You cannot do this in "one shot". It will take two steps:
1) Append the records that satisfy the condition to table_history. The SQL is:
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField] Not In (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)
or alternatively
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
2) Delete the records that satisfy the condition from table1. The SQL is:
DELETE [DateField] FROM table1 WHERE [DateField] Not In (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)
or
DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
Re: move records based date in filed
OPS!HansV wrote:You cannot do this in "one shot". It will take two steps:
1) Append the records that satisfy the condition to table_history. The SQL is:
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField] Not In (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)
or alternatively
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
2) Delete the records that satisfy the condition from table1. The SQL is:
DELETE [DateField] FROM table1 WHERE [DateField] Not In (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)
or
DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
But lost important info...
I have table1 in c:\mydir\mdb1.mdb
and
table_history in c:\mydr1\mdb2.mdb
sorry for that:-)
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: move records based date in field
Create a link to table_history in mdb1.mdb. You can then run the code in mdb1.mdb.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
Re: move records based date in field
Clear me...HansV wrote:Create a link to table_history in mdb1.mdb. You can then run the code in mdb1.mdb.
But if have only 2 or 1 or 3 date in DateField the query not return action? or not?
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: move records based date in field
If you have three or fewer dates, the append query and delete query won't do anything.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
Re: move records based date in field
tks!HansV wrote:If you have three or fewer dates, the append query and delete query won't do anything.
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
Re: move records based date in filed
TIPS...HansV wrote:You cannot do this in "one shot". It will take two steps:
1) Append the records that satisfy the condition to table_history. The SQL is:
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField] Not In (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)
or alternatively
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
2) Delete the records that satisfy the condition from table1. The SQL is:
DELETE [DateField] FROM table1 WHERE [DateField] Not In (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)
or
DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
i need to exclude from this query always all records with date 30/04/2009, possible?
In ewffet maintain always all records with date 30/04/2009 into table1
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: move records based date in field
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)) AND [DateField]<>#04/30/2009#
and
DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)) AND [DateField]<>#04/30/2009#
(Dates in SQL strings must always be specified in US date format mm/dd/yyyy)
and
DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)) AND [DateField]<>#04/30/2009#
(Dates in SQL strings must always be specified in US date format mm/dd/yyyy)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
Re: move records based date in field
.. but based the suggestion about the format US date independent for all version of Access, Italian or English?HansV wrote:INSERT INTO table_history SELECT * FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)) AND [DateField]<>#04/30/2009#
and
DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)) AND [DateField]<>#04/30/2009#
(Dates in SQL strings must always be specified in US date format mm/dd/yyyy)
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
Re: move records based date in filed
but the first or second option to insert and delete use the same time for execution?HansV wrote:You cannot do this in "one shot". It will take two steps:
1) Append the records that satisfy the condition to table_history. The SQL is:
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField] Not In (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)
or alternatively
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
2) Delete the records that satisfy the condition from table1. The SQL is:
DELETE [DateField] FROM table1 WHERE [DateField] Not In (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)
or
DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: move records based date in field
I think the second option will be faster than the first one for both the append and delete queries, but I haven't tested it on a large number of records.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
Re: move records based date in field
... but for the insert and for the deleting rcords i can use the second?HansV wrote:I think the second option will be faster than the first one for both the append and delete queries, but I haven't tested it on a large number of records.
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
and
DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
????
Last edited by sal21 on 25 Nov 2010, 17:10, edited 2 times in total.
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
Re: move records based date in field
ok. tsk fo rpatience:-)HansV wrote:Yes.
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
Re: move records based date in field
HansV wrote:Yes.
OK. Can you adjust based my last request (about 30/04/2009) the two query,...
Code: Select all
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
and
DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC))
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: move records based date in field
Easy! See Post=33962 higher up in this thread...
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
Re: move records based date in field
Wath you think about tath?HansV wrote:Easy! See Post=33962 higher up in this thread...
Code: Select all
INSERT INTO table_history SELECT * FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC) AND [DateField]<>#04/30/2009#)
and
DELETE [DateField] FROM table1 WHERE [DateField]<(SELECT Min([DateField]) FROM (SELECT Top 3 [DateField] FROM table1 ORDER BY [DateField] DESC)AND [DateField]<>#04/30/2009#)
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands