• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • Last Modified:

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.pdf    Doc1/V1
         Doc1     \\Server\MyPath\Doc1_V2.pdf    Doc1/V2

 Two  typical dummy rows copied would look like  
       Doc2        \\Server\MyPath\Doc2_V1.pdf     Doc2/V1
       Doc2        \\Server\MyPath\Doc2_V2.pdf     Doc2/V2
       Doc3        \\Server\MyPath\Doc3_V1.pdf     Doc3/V1
       Doc3        \\Server\MyPath\Doc3_V2.pdf     Doc3/V2
0
Sam OZ
Asked:
Sam OZ
  • 4
  • 3
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Try...

SELECT DocName, Path , DocAndVersion
FROM YourTableName
UNION ALL
SELECT DocName, Path , DocAndVersion
FROM YourTableName

Open in new window

0
 
Sam OZAuthor Commented:
I was looking for creating new dummy rows . Please give a solution ,if you have, for it
0
 
Pawan KumarDatabase ExpertCommented:
Pls confirm.

Actual
Doc2        \\Server\MyPath\Doc2_V1.pdf     Doc2/V1


New you wanted to create
Doc2        \\Server\MyPath\Doc2_V2.pdf     Doc2/V2
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Sam OZAuthor Commented:
Hi Pawan,
     Yes , that is correct
0
 
Pawan KumarDatabase ExpertCommented:
Hi,
Pls try,..

SELECT DocName, Path , DocAndVersion
FROM YourTableName
UNION ALL
SELECT DocName, Path , REPLACE(DocAndVersion,'V1','V2') AS DocAndVersion
FROM YourTableName

Open in new window

0
 
Sam OZAuthor Commented:
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
0
 
Gustav BrockCIOCommented:
Run a double loop to create from Doc1/V1 to Doc1000/V9:
Dim rs As DAO.Recordset

Dim DocId As Long
Dim VerId As Long
Dim DocName As String
Dim VerName As String

Set rs = CurrentDb.OpenRecordset("YourTable")

For DocId = 1 to 1000
    For VerId = 1 to 9
        DocName = "Doc" & CStr(DocId)
        VerName = "V" & CStr(VerId)
        rs.AddNew
            rs!DocName.Value = DocName
            rs!Path.Value = "\\Server\MyPath\" & DocName & "_" & VerName & ".pdf"
            rs!DocAndVersion.Value = DocName & "/" & VerName  
        rs.Update
    Next
Next
rs.Close

Set rs = Nothing

Open in new window

/gustav
0
 
Pawan KumarDatabase ExpertCommented:
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

0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now