Solved

Select column name as prifix to row values

Posted on 2013-01-23
6
464 Views
Last Modified: 2013-01-23
Hi all

I have a customer table below.
I need the result to be like the values in table customers result below.

I.e I need to append all the result set with column Name as prefix..
How can I achive this?

Thanks in Advance

Customers Resultcustomers table
0
Comment
Question by:ZURINET
[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
6 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 38809243
You'll need to run a query here. Something like

SELECT 'CustomerID' & CustomerID, 'StoeID' & StoreID,......
FROM yourtable


Kelvin
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38809246
you can use string concatenation

example
select
'[customerID].&[' + convert(nvarchar,CustomerID) + ']' as customerID,
'[storeID].&[' + convert(nvarchar,storeID) + ']' as storeID,
...etc ...
FROM tablename
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38809253
Make sure ALL of the fields you are updating are defined as text in the underlying table and Try this in VBA:

Sub DoThis()
dim rs as DAO.recordset
SET rs = CurrentDB.OpenRecordset
if rs.recordcount = 0 then Exit Sub
do until rs.eof
    rs.Edit
    rs!CustomerID = "[CustomerID].[" & CustomerID & "]"
    rs!StoreID= "[StoreID].[" & StoreID & "]"
    rs!AccountNumber = "[AccountNumber].[" & AccountNumber & "]"
    rs.Update
    rs.MoveNext
Loop
rs.close
set rs = nothing

Open in new window


EDIT:

Sorry - I though this was posted in the Access zone... This assumes you're working with an Access interface.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 2

Accepted Solution

by:
harshada_sonawane earned 500 total points
ID: 38809255
u can try this

SELECT '[id].&'+ '['+ convert(varchar,id) +']' as id from customer

same way for all columns
0
 

Author Comment

by:ZURINET
ID: 38809276
Hi Hars..
Thanks for the great answer!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38809286
ZURINET,

Did you see the earlier post from 'sognoct' at http:#a38809246 ?

Unless I'm missing something,  the answer you accepted from harshada_sonawane is identical to that earlier response...
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 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