Hello,
Please help me to query the records from a table and display the records in horizontally for a report.
I have a table with records in 4 fields:
Region Store Month_Year Number
East WalMart January2017 100
East WalMart February2017 100
East WalMart March2017 200
East WalMart April2017 300
East WalMart May2017 100
East Kmart February2017 200
East Kmart March2017 100
East Kmart May2017 200
East Target January2017 100
East Target February2017 200
East Target April2017 100
East Target May2017 300
West WalMart January2017 100
West WalMart February2017 200
West WalMart April2017 200
West WalMart May2017 400
West KMart January2017 300
West KMart March2017 200
West KMart April2017 100
West KMart May2017 200
West Target January2017 300
West Target February2017 200
West Target March2017 500
West Target May2017 200
North …
South …
… …
What I’m trying to display in report is group by region, Store and list Number by each Month_Year. Also, list the YTD Amount of the Store at the right column and Month total in the bottom of each Month like this:
East summary
Store-Name January2017 February2017 March2017April2017 May2017 ... YTD Amount
Wal-Mart 100 100 200 300 100 ... 800
Kmart 200 100 200 ... 500
Target 100 200 100 300 ... 700
Month Total 200 500 300 400 600 ... 2000
West Summary
Store-NameJanuary2017 February2017March2017April2017May2017...YTD Amount
~~
Wal-Mart 100 200 200 400 ... 900
Kmart 300 200 100 200 ... 800
Target 300 200 500 200 ... 1200
Month Total 700 400 700 300 800 ... 2900
North summary (Same way... )
South summary (Same way... )
...
Sorry, I can't display the format of database table in the poster here. But,
Thank you in advance,
lw