Solved

Access copy row querry

Posted on 2003-12-02
7
478 Views
Last Modified: 2008-02-01
I have a table called "controls" and "roles". They are the exact same structure.
Now i need to copy the values in the row of table "roles" (with username "default") to the "controls" table with a new username im getting from the parameter. Any ideas how to do this in 1 access querry ?!


PARAMETERS [@username] Text ( 50 );
DECLARE  [@top] text (255), [@left] Text  (255),[@middle] Text (255), [@right] Text (255),[@bottom] Text (255);

SELECT  [@top] = Roles.[top], [@left]=Roles.[left], [@middle]=Roles.[middle], [@right]=Roles.[right], [@bottom]=Roles.[Bottom]
FROM Roles
WHERE Roles.Username = "default"

INSERT INTO controls Values  SET controls.username = [@username], controls.[top] = [@top], controls.[left] = [@left], controls.middle = [@middle], controls.[right] = [@right], controls.bottom = [@bottom]
0
Comment
Question by:zinno
  • 4
  • 2
7 Comments
 
LVL 5

Expert Comment

by:morpheus30
ID: 9862665
INSERT INTO Controls (Top, Middle, Right, Bottom, UserName) SELECT Top, Middle, Right, Bottom, UserName, [Enter UserName] FROM Roles
0
 
LVL 5

Assisted Solution

by:morpheus30
morpheus30 earned 250 total points
ID: 9862673
Sorry, I forgot the where statement

INSERT INTO Controls (Top, Middle, Right, Bottom, UserName)
SELECT Top, Middle, Right, Bottom, UserName, [Enter UserName]
FROM Roles
WHERE UserName = "default"

0
 
LVL 23

Accepted Solution

by:
heer2351 earned 250 total points
ID: 9862701
Looks like you have a .adp, correct?

This should do the trick:

PARAMETERS [@username] Text ( 50 );
INSERT INTO controls (username, top, left, middle, right, bottom)
SELECT [@username], [top], [left], [middle], [right], [Bottom]
FROM Roles
WHERE Username = "default"
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.  

 
LVL 23

Expert Comment

by:heer2351
ID: 9862708
Sorry morpheus30 did not see your code until I posted mine, only difference that I declare the parameter first.
0
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9862752
And on first given opportunity, you should change the name of your table "Controls", - it is a critical word to use for naming objects in Access, as it has explicit meaning in certain relations.

Sven
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 9862823
Oh dude...

I misunderstood the question: Any ideas how to do this in 1 access querry ?!

I was thinking you were trying to convert T-SQL into native Access SQL used by .mbd files.  My solution won't work if you're using an .adp file.  heer2351's solution should work for you there...

0
 
LVL 5

Expert Comment

by:morpheus30
ID: 9862945
If you want to save it as a query (Stored Procedure) in your Access .adp file, you could copy and paste the following SQL statement

INSERT INTO Controls (Top, Middle, Right, Bottom, UserName)
SELECT Top, Middle, Right, Bottom, UserName, @UserName
FROM Roles
WHERE UserName = 'default'
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

830 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