Get multiple data against records in horizontally

[lalit81inin]

I am having the table where data with the same invoice number stored as shown in table 2 and I would like to display it as shown in table2
Table1 Table1

invoiceno	amnt	month
123​	77​	jan
453​	89​	feb
123​	65​	april
321​	34​	jan
321​	44​	feb
453​	87​	mar
321​	45​	may

Table2 Table2

invoice	amnt1	amnt2	amnt3
123​	77​	65​	​
321​	34​	44​	45​
453​	89​	87​	​

 

[arnelgp]

well, you need to have a vba to put your months as Number (see UDF, fnInvoicePos in Module1).
then create a Crosstab (FinalQuery) query out of Query1.
FinalQuery is the one you need.

 

[lalit81inin]

well, you need to have a vba to put your months as Number (see UDF, fnInvoicePos in Module1).
then create a Crosstab (FinalQuery) query out of Query1.
FinalQuery is the one you need.

Hi Thanks arnelgp this worked for me but i was looking for output as shown in below it missed to mention month of billing.

invoiceno	billing month1	Amount1	billing month2	Amount2	billing month3	Amount3
123	jan	77	april	65
321	jan	34	feb	44	may	45
453	feb	89	mar	87

 

[Gasman]

Crossposted https://www.accessforums.net/showthread.php?t=89286

 

[arnelgp]

I added dummy table, zzT1 and used it to create a Cross join (crosstab, see Allen Browne: Microsoft Access tips: Crosstab query tips (allenbrowne.com)

your query will be FinalQuery2.