Get multiple data against records

[lalit81]

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

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

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

I used the pivot table query but didnt worked can someone please help me

[June7]

Review https://stackoverflow.com/questions/...y-in-ms-access

[lalit81]

Hi There
This quite doesnt work in my case i checked tried and tested .I am not able to get desired output

[Welshgasman]

Crossposted, see link below

https://www.access-programmers.co.uk...ntally.330087/

[Micron]

WGM, I think you posted a link to this very thread? This seems to be thread 89286.

[Welshgasman]

WGM, I think you posted a link to this very thread? This seems to be thread 89286.

Oops, apologies. Now corrected.

Thank you @Micron.

[Micron]

Hyperlink text is different, url is the same. Keep trying!

[June7]

Hi There
This quite doesnt work in my case i checked tried and tested .I am not able to get desired output

Why doesn't it work? What happens? Show your attempted SQL. Works for me.

TRANSFORM Max(Table1.amnt) AS MaxOfamnt
SELECT Table1.invoiceno
FROM Table1
GROUP BY Table1.invoiceno
PIVOT "amnt" & DCount("*","Table1","invoiceno=" & [invoiceno] & " AND ID <=" & [ID]);

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

You could even provide your db for analysis. Follow instructions at bottom of my post.

Month is a reserved word and really should not use reserved words as names.


WGM, link still opens this thread. Here is other https://www.access-programmers.co.uk...ntally.330087/
And now I see requirement is different and you have solution.

[Welshgasman]

Hyperlink text is different, url is the same. Keep trying!

Not sure how that happened?

Anyway, correct link this time, and I even tested this one.

[Welshgasman]

Why doesn't it work? What happens? Show your attempted SQL. Works for me.

TRANSFORM Max(Table1.amnt) AS MaxOfamnt
SELECT Table1.invoiceno
FROM Table1
GROUP BY Table1.invoiceno
PIVOT "amnt" & DCount("*","Table1","invoiceno=" & [invoiceno] & " AND ID <=" & [ID]);

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

You could even provide your db for analysis. Follow instructions at bottom of my post.

Month is a reserved word and really should not use reserved words as names.


WGM, link still opens this thread. Here is other https://www.access-programmers.co.uk...ntally.330087/

Thanks @June7, not sure how you can paste a url but for a different address.?
This site only allows a url, not any descriptive text, and I would have just copied and pasted anyway?

[Micron]

Sometimes I forget to copy a url that I've loaded into a tab, and what gets pasted is whatever is on the clipboard. Obvious if not a url but not so obvious if it's the wrong one. Sometimes I open notepad and copy from there, especially if I'm pasting url's that point to each cross post. It's so much easier to put cross posters on IL at AWF.