Solved

Copy a row in MS Access but with some field values changed

Posted on 2016-11-24
8
54 Views
Last Modified: 2016-11-28
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
Comment
Question by:Sam OZ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41901114
Try...

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

Open in new window

0
 

Author Comment

by:Sam OZ
ID: 41901122
I was looking for creating new dummy rows . Please give a solution ,if you have, for it
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41901123
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Sam OZ
ID: 41901124
Hi Pawan,
     Yes , that is correct
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41901129
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
 

Author Comment

by:Sam OZ
ID: 41901135
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
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41901235
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
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41901298
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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question