asked on

Select column name as prifix to row values

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

Kelvin Sparks

You'll need to run a query here. Something like

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

Kelvin

sognoct

you can use string concatenation

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

mbizup

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.

ASKER CERTIFIED SOLUTION

harshada_sonawane

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ZURINET

ASKER

Hi Hars..
Thanks for the great answer!

mbizup

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...