Solved

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

Posted on 2016-11-24
8
49 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 28

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 28

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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

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

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 49

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 28

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

828 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