Is this impossible?

[Guest]

Hello I have a table with 3 fields. Field1 has an ID number. Field2 has a
code number and field3 has a value which is a number.

In this table the ID numbers of Field1 is entered more than once with
different values. for example:

Field1........Field2.......Field3
123A..........542...........3
168F..........634............21
123A..........634...........55
843W.........224...........54
123A..........552...........31
etc.

What I need to do and have no idea how to do it, is to create new fields
from the code numbers in Field2 for every ID in field1 with the data of
field3 under that new field.
In the above example Field1 has ID "123A" entered three times with different
Field2 codes. I want those codes to become new fields in the ID "123A" row.
So the table will look like:

Field1........(new field)542......(new field)634.......(new field)552
123A..............3...........................55..........................31
168F..........................................21..............................
etc.

So actually for every Field1 ID number, I want the data in Field2 to become
new fields, with the data of field3 under that new Field.

I'm afraid I haven't explained it well to you since I am from Greece and my
English isn't that good. And I am also afraid that what I am asking is not
possible.

Can anyone help me?

Thank you
Dimitris

 

[Michel Walsh]

a crosstab?

TRANSFORM LAST(field3)
SELECT field1
FROM yourTableNameHere
GROUP BY field1
PIVOT "Code" & field2



Note that you are limited to 255 fields, max, so, you may get error if there
is more than 250 different values for field2.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you for your help Michel.
It worked, and I finally see as a querie what I was looking for.
Just one more question. How can I turn this querie into a table in a
different database? I need this crosstab as a table in that database.

Thank you
Dimitris

 

[Michel Walsh]

You can always make another query (that makes a table) using this query as
'source' ?

Vanderghast, Access MVP

 

[Guest]

Thank you very much.
You helped me a lot.

You can always make another query (that makes a table) using this query as
'source' ?

Vanderghast, Access MVP