Transform Data

[arvive]

Dear all kindly assist me in doing this. I have data in a table which look like below
DATE DESIGNCODE TRAINEE
01/08/2019 D0D23 1001
01/08/2019 D0D24 1001
02/08/2019 D0123 1002
02/08/2019 100RT 1002


I need a query to present it like below in a report. I have tried crosstab queries. (Please note any process to do this is welcome)


DATE TRAINEE DESIGNCODE1 DESIGNCODE2 DESIGNCODE3


01/08/2019 1001 D0D23 D0D24
02/08/2019 1002 D0123 100RT

[ranman256]

make a query to get the data you want, save it, say Q1
start the query wizard
create CROSSTAB query
tell it to use Q1
set the row data as DATE
set the column data as CODE
set the VALUE data as TRAINEE, and use FIRST VALUE

save this

[CJ_London]

crossposted here with answers https://www.access-programmers.co.uk...d.php?t=306325

[arvive]

Thanks Sir but I still have not been able to get the result yet.

(Below is the SQL) result data in attached pics.

TRANSFORM First(monthlydesignallow.[EMPNO]) AS FirstOfEMPNO
SELECT monthlydesignallow.[DATETIM]
FROM monthlydesignallow
GROUP BY monthlydesignallow.[DATETIM]
PIVOT monthlydesignallow.[DESCODE];

Result

[arvive]

Need to get the required result like this. Thanks