I thought this would be easy but I am having great difficulty! Could someone kindly please explain where I am going wrong?
I have a single table of data, tblA
each line shows a start/stop, country, date and price
eg. lines:
Action-------Country----------Date-------------------Value
STOP------Argentina---------01/05/2005-------------($50)
STOP------Fiji------------------01/08/2007--------------($100)
START-----Fiji-------------------01/06/2006-------------$150
START-----Cuba----------------01/04/2008-------------$50
Pretty simple.
I need to create a query with the following:
Country names (vertical), split by start or stop
eg.
Argentina - Start
Argentina - Stop
Cuba - Start
Cuba - Stop
Fiji - Start
Fiji - Stop
I then need to populate the Prices of the starts/stops that took place in each month
i.e.
Country/Price per Month---------01/05/2005--------01/06/20005---------01/07/2005
Argentina---------Start--------------$150----------------- $50
Argentina---------Stop--------------($50)-----------------($200)
essentially this mimics an excel pivit table function but i don't know how to create it in access?
I have tried using Group By Country, Sum Price, with a criteria of Date works for one col only
I have tried using an expr:
Expand|Select|Wrap|Line Numbers
- Month1: iif(Date=#01/05/2005#,(sum([Value]),0)
I have tried multiple other ideas which I won't list as I'm sure I'm missing something obvious (fingers crossed)
Please help! :)