UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Advance The Date On Set Of Records In A Record Set, Access 2013    
 
   
SomekindaVB
post Jun 25 2019, 01:07 AM
Post#1



Posts: 300
Joined: 15-December 16



Hello All,

I have a table of hundreds of records with the date '21/06/2019'. In the same table I need to duplicate those exact same records but with the date field '22/06/2019' and then '23/06/2019'

I'm assuming I will need to use an insert. I did this, but all I ended up doing was exactly duplicating the date. I need the date advanced.

CODE
INSERT INTO Tbl_MyTable ( ProductID, MyLimit, MyDate, MyDate, MyAmount, MyAlternate, [Currency], MyRate, CalculationMethod )
SELECT ProductID, MyLimit, MyDate, #6/22/2019# AS MD, MyAmount, MyAlternate, Currency, MyRate, CalculationMethod
FROM Tbl_MyTable

WHERE (((Tbl_MyTable.MyDate)=#6/21/2019#));


Go to the top of the page
 
Phil_cattivocara...
post Jun 25 2019, 02:00 AM
Post#2



Posts: 304
Joined: 2-April 18



Does QBE does not help you with this?
I would use an alias when you indicate Tbl_MyTable as the source for the SELECT
SQL
INSERT INTO Tbl_MyTable ( ProductID, MyLimit, MyDate, MyDate, MyAmount, MyAlternate, [Currency], MyRate, CalculationMethod )
SELECT ProductID, MyLimit, MyDate, #6/22/2019# AS MD, MyAmount, MyAlternate, [Currency], MyRate, CalculationMethod
FROM Tbl_MyTable AS T1
WHERE (((T1.MyDate)=#6/21/2019#));

Then you change the date in select, from 6/22/2019 to 6/23/2019. Perhaps you can do both queries in one with a UNION
SQL
INSERT INTO Tbl_MyTable ( ProductID, MyLimit, MyDate, MyDate, MyAmount, MyAlternate, [Currency], MyRate, CalculationMethod )
SELECT ProductID, MyLimit, MyDate, #6/22/2019# AS MD, MyAmount, MyAlternate, [Currency], MyRate, CalculationMethod
FROM ( SELECT ProductID, MyLimit, MyDate, #6/22/2019# AS MD, MyAmount, MyAlternate, [Currency], MyRate, CalculationMethod
FROM Tbl_MyTable AS T1
WHERE (((T1.MyDate)=#6/21/2019#))
UNION
SELECT ProductID, MyLimit, MyDate, #6/23/2019# AS MD, MyAmount, MyAlternate, [Currency], MyRate, CalculationMethod
FROM Tbl_MyTable AS T2
WHERE (((T2.MyDate)=#6/21/2019#))

) AS TUnion
Note squared brackets with Currency (which is a reserved word)
Do you have to repeat this every day?
(not tested)

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
cheekybuddha
post Jun 25 2019, 03:52 AM
Post#3


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


Specifying field MyDate twice in your insert query will lead to unexpected results.

If I understand correctly what you are trying to do (though I do not understand why you are trying to do it!), you should be able (building on Phil's UNION idea) to run:
CODE
INSERT INTO Tbl_MyTable ( ProductID, MyLimit, MyDate, MyAmount, MyAlternate, [Currency], MyRate, CalculationMethod )
SELECT ProductID, MyLimit, MyDate + 1, MyAmount, MyAlternate, [Currency], MyRate, CalculationMethod
FROM Tbl_MyTable AS T1
WHERE T1.MyDate = #6/21/2019#
UNION
SELECT ProductID, MyLimit, MyDate + 2, MyAmount, MyAlternate, [Currency], MyRate, CalculationMethod
FROM Tbl_MyTable AS T2
WHERE T2.MyDate = #6/21/2019#
;


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Phil_cattivocara...
post Jun 25 2019, 04:09 AM
Post#4



Posts: 304
Joined: 2-April 18



(I have read cheekybuddha post)
I made a mistake in my previous post. The second line should be
SQL
SELECT ProductID, MyLimit, MyDate, MD, MyAmount, MyAlternate, [Currency], MyRate, CalculationMethod

(this happens when you copy-past and then decide to modify again). Sorry sorry sorry.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
cheekybuddha
post Jun 25 2019, 04:25 AM
Post#5


UtterAccess VIP
Posts: 11,423
Joined: 6-December 03
From: Telegraph Hill


The problem, Phil, is that MyDate and MD are the same field.

--------------------


Regards,

David Marten
Go to the top of the page
 
SomekindaVB
post Jun 27 2019, 06:25 PM
Post#6



Posts: 300
Joined: 15-December 16



Thanks all for help. I've applied this info given and It seems to have worked as intended.

Also, as to why, I am aware that duplication is not best practice, and in my database a good deal of effort goes into removing duplicate records, however, in some cases, my organisation need for records to be duplicated from one day to the next. It's a business requirement in this case.

Cheers
This post has been edited by SomekindaVB: Jun 27 2019, 06:28 PM
Go to the top of the page
 
MadPiet
post Jun 27 2019, 06:29 PM
Post#7



Posts: 3,171
Joined: 27-February 09



this is one of those times where I would use a Tally table (a table of numbers that goes from 1 to the highest number you need). Then you just cross join.

SELECT a.Myfield1, a.MyField2, t.N
FROM TableA a CROSS JOIN Tally t ON a.MyField1 <= t.N;

that way you'll get a partial cross join that creates one record for every value between 1 and a.Myfield1 's value.
Go to the top of the page
 
MadPiet
post Jun 27 2019, 06:41 PM
Post#8



Posts: 3,171
Joined: 27-February 09



INSERT INTO Tbl_MyTable ( ProductID, MyLimit, MyDate, MyAmount, MyAlternate, [Currency], MyRate, CalculationMethod )
SELECT ProductID, MyLimit, MyDate + Tally.Num, MyAmount, MyAlternate, [Currency], MyRate, CalculationMethod
FROM Tbl_MyTable AS T1
CROSS JOIN Tally
WHERE Tally.Num<=5; <-- this is how many duplicates you want. If it's in a column in MyTable, then you can specify that column here.

Tally is a table of Numbers...
CREATE TABLE Tally (Num INT NOT NULL);
INSERT INTO Tally (Num) VALUES (1), (2), (3);

Go to the top of the page
 
MadPiet
post Jun 27 2019, 09:17 PM
Post#9



Posts: 3,171
Joined: 27-February 09



Here's a tested example. =)
CODE
USE tempdb;
GO
/* This is a simple table of numbers from 1 to whatever you want. You could use VBA to populate it if you want.*/
CREATE TABLE Tally (Num TINYINT PRIMARY KEY);
GO
INSERT INTO Tally (Num) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

/* this is my data table */
CREATE TABLE MyTable (ProductID INT, MyLimit TINYINT, MyDate DATE);
GO

-- GETDATE() is the same as DATE() in Access. It's just easier to write this in T-SQL
INSERT INTO MyTable (ProductID, MyLimit, MyDate) VALUES (1,100,GETDATE()),(2,150,GETDATE());

/* this is the cross-join... I'm effectively duplicating each record Num times */
SELECT mt.ProductID
    , mt.MyLimit
    , Tally.Num
    , DATEADD(day,Tally.num, mt.MyDate) AS NextDate
FROM MyTable mt CROSS JOIN Tally
WHERE Tally.Num<=2;
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th July 2019 - 05:00 PM