Solved

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

Posted on 2016-11-24
8
26 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
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Comment Utility
I was looking for creating new dummy rows . Please give a solution ,if you have, for it
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
 

Author Comment

by:Sam OZ
Comment Utility
Hi Pawan,
     Yes , that is correct
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Comment Utility
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 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
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 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now