Access - put data from a table horizontally

Asked

Viewed 113 times

1

Hello!

I have already searched in several forums, but I could not find a satisfactory solution. I have the following table:

ID  COD VLR_REEMB   N_FICHA DT_RECEBIMENTO  
1   1   4.022,06    965     24/09/2010  
2   3   6.600,00    746     17/09/2013  
3   4   6.600,00    746     17/09/2013  
4   5   6.600,00    746     17/09/2013  
5   6   6.600,00    746     17/09/2013  
6   6   6.600,00    749     20/09/2013  
7   6   6.600,00    750     17/10/2013  
8   9   6.600,00    746     17/09/2013  
9   1   6.600,00    746     17/09/2013  

The ID is unique, as Cod can be repeated. So I would like to generate a query that shows me the information as follows:

COD    VLR1    FICHA1    DT1      VLR2    FICHA2    DT2    VLR3    FICHA3    DT3
1    4.022,06   965    24/09/2010
6    6.600,00   746    17/09/2016 6.600,00 749  20/09/2013 6.600,00 750 17/10/2013

Someone with some suggestion about me how can I do this in Access?


Hello!

I appreciate the feedback, but I don’t think the post is good enough for me. I came to see a similar solution, but from what I understood, when using the pivot table I can not indicate more than one column, which is exactly what I need.

Follow the example of what I was able to do:

TRANSFORM FIRST([N_FICHA]) AS FirstOfName SELECT COD FROM ( SELECT t1.COD, t1.N_FICHA,'FICHA' & Format(COUNT(*),"00") AS NewName FROM TABELA1 AS t1 INNER JOIN TABELA1 AS t2 ON t1.COD = t2.COD AND t1.N_FICHA >= t2.N_FICHA GROUP BY t1.COD, t1.N_FICHA ) GROUP BY COD PIVOT NewName

That gave me the following result:

COD FICHA01 FICHA02 FICHA03 1 746 965 3 746
4 746
5 746
6 746 749 750 9 746

That’s exactly what I need, only with the additional columns next to each chip. Any suggestions?

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.