Moving multiple data to new fields

[Dimitris]

Hello,
In a table named PK1 there are two field named AM1 and J1. AM1 may have the
same data entered many times. J1 has unique data, no duplicates at all. Now
in that table there are new empty fields, TL1, TL2, TL3, TL4, TL5, TL6, TL7,
TL8, TL9, TL10
So the table PK1 looks like:

AM1........J1.....TL1......TL2.....TL3.....TL4......TL5.....TL6....TL7...TL8...TL9..TL10

JOHN.....68
MIKE.....94
JOHN.....55
MARY.....30
JOHN.....02
MIKE.....99
JOHN.....00
etc etc

What I need is to have every entry in field AM1 entered once. And the data
in field J1 entered in the fields TL1 TL2 TL3 TL4.......TL10 for every
unique AM1 entry. So the above example must be like:

AM1....TL1......TL2.....TL3.....TL4......TL5.....TL6....TL7...TL8...TL9..TL10

JOHN... 68......55........02.......00
MIKE....94........99
MARY...30
etc etc

So actually I need each name of AM1 entered once with and all the data of
field J1 entered in the fields TL1 to TL10 for the same name. If each name of
AM1 has only one data it will be entered in field TL1. if that name has 2
entries they will be entered in TL1 and TL2 and so on.

I hope you understand what I need. I am from Greece and don't speak well
English.
I also am new in Access and I hope I can get detailed instructions.

I am looking forward for your help.
Thank you very much

Dimitris

 

[Duane Hookom]

This is possible but you would need to have some field value that determines
which J1 value falls under which TL column. Does it make a difference if 68
is TL2 and 55 is TL1?

You would first create a query "qcarPrepForCrosstab" with SQL of:

SELECT PK1.AM1, PK1.J1, Count(PK1.J1) AS TL
FROM PK1 LEFT JOIN PK1 AS PK1_1 ON PK1.AM1 = PK1_1.AM1
WHERE (((PK1_1.J1)>=[PK1]![J1]))
GROUP BY PK1.AM1, PK1.J1;

Then create a crosstab with SQL of:
TRANSFORM Sum(qcarPrepForCrosstab.J1) AS SumOfJ1
SELECT qcarPrepForCrosstab.AM1
FROM qcarPrepForCrosstab
GROUP BY qcarPrepForCrosstab.AM1
PIVOT "TL" & [TL];

The TL values will be descending from left to right.