is this possible in access

[mikarsen]

for example i have a query with...
field1 field2 field3
a------b-----c


my goal is to create a new table with that gets the values of field1,2,&3 and display as a row in a newly created column

newcolumn
a
b
c

thanks in advance.

 

[nikkypickles]

probably not a lot of help...

I'm not sure about a new table (and maybe some of the more experienced databasers will correct me here) but it looks like you're describing a cross-tab query?
However, for a cross tab query you also need at least one row and one column of data as well as the value.

 

[integer]

it is posible but in VBA.

 

[mikarsen]

hi integer... can u teach me how to accomplish this task using VBA. Thanks in advance

 

[gemma-the-husky]

is there just one row in your query, with columns a b c

in that case you can do the following. i haven't got the sql syntax exactly right below though


sub loadtable

dim rst as recordset
dim sqlstrg as string

set rst=currentdb.openrecordset(myquery)
rst.movefirst

sqlstrg = insert into newtable rst!fielda - you need to get the right syntax for this
docmd.runsql sqlstrg

sqlstrg = insert into newtable rst!fieldb - you need to get the right syntax for this
docmd.runsql sqlstrg

sqlstrg = insert into newtable rst!fieldc - you need to get the right syntax for this
docmd.runsql sqlstrg

rst.close
set rst=nothing
end sub

 

[mikarsen]

yup there is only one row in my first query. Ok il try the code that you suggested.many thanks.