?
Solved

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

Posted on 2016-11-24
8
Medium Priority
?
56 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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 51

Accepted Solution

by:
Gustav Brock earned 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

752 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