Sam OZ
asked on
Copy a row in MS Access but with some field values changed
Hi Experts,
I have a table in MS Access with fields like DocName, Path , DocAndVersion
I need to create 100s of rows of dummy data from a given row and I am looking a way to do that
(If you think doing it in Excel is easier, That solution is also fine)
The sample row is
Doc1 \\Server\MyPath\Doc1_V1.pd f Doc1/V1
Doc1 \\Server\MyPath\Doc1_V2.pd f Doc1/V2
Two typical dummy rows copied would look like
Doc2 \\Server\MyPath\Doc2_V1.pd f Doc2/V1
Doc2 \\Server\MyPath\Doc2_V2.pd f Doc2/V2
Doc3 \\Server\MyPath\Doc3_V1.pd f Doc3/V1
Doc3 \\Server\MyPath\Doc3_V2.pd f Doc3/V2
I have a table in MS Access with fields like DocName, Path , DocAndVersion
I need to create 100s of rows of dummy data from a given row and I am looking a way to do that
(If you think doing it in Excel is easier, That solution is also fine)
The sample row is
Doc1 \\Server\MyPath\Doc1_V1.pd
Doc1 \\Server\MyPath\Doc1_V2.pd
Two typical dummy rows copied would look like
Doc2 \\Server\MyPath\Doc2_V1.pd
Doc2 \\Server\MyPath\Doc2_V2.pd
Doc3 \\Server\MyPath\Doc3_V1.pd
Doc3 \\Server\MyPath\Doc3_V2.pd
ASKER
I was looking for creating new dummy rows . Please give a solution ,if you have, for it
Pls confirm.
Actual
Doc2 \\Server\MyPath\Doc2_V1.pd f Doc2/V1
New you wanted to create
Doc2 \\Server\MyPath\Doc2_V2.pd f Doc2/V2
Actual
Doc2 \\Server\MyPath\Doc2_V1.pd
New you wanted to create
Doc2 \\Server\MyPath\Doc2_V2.pd
ASKER
Hi Pawan,
Yes , that is correct
Yes , that is correct
Hi,
Pls try,..
Pls try,..
SELECT DocName, Path , DocAndVersion
FROM YourTableName
UNION ALL
SELECT DocName, Path , REPLACE(DocAndVersion,'V1','V2') AS DocAndVersion
FROM YourTableName
ASKER
My Apologies!! I probably overlooked your previous question . The creation I am looking is below ( Also I need 1000s of rows created Doc2, Doc3, Doc4..... Doc1000
Actual
Doc1 \\Server\MyPath\Doc1_V1.pdf Doc1/V1
New you wanted to create a new row
Doc2 \\Server\MyPath\Doc2_V1.pdf Doc2/V1
Actual
Doc1 \\Server\MyPath\Doc1_V1.pdf Doc1/V1
New you wanted to create a new row
Doc2 \\Server\MyPath\Doc2_V1.pdf Doc2/V1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try..
DECLARE @Looper INTEGER
SET @Looper = 1
CREATE TABLE Pawan (DocName VARCHAR(100) , Path VARCHAR(100) , DocAndVersion VARCHAR(100) )
WHILE @Looper < 1001
BEGIN
INSERT INTO Pawan(DocName,Path,DocAndVersion)
SELECT DocName, Path , REPLACE(DocAndVersion,'V1', 'V' + str(@Looper) ) AS DocAndVersion
SELECT @Looper = @Looper + 1
END
---Operations ....
DROP TABLE Pawan
Open in new window