hey there guys!

Really would appreciate your help making a cross-tab report out of the attached acess table. The needed cross-tab report is quite complicated and currently we do it manually by counting items, subtracting and entering the info into excel. A sample excel output is also attached.

The way it works is: we are given a request year (i.e. "submit report for 2012"). We need to calculate total items arrived per item per year starting with previous year (ie.. 2011). For previous year, we need "Ending Balance" of items and price only. That is total items (which arrived in 2011) remaing in store. That total is total no of items which arrived in 2011 minus all items disposed (which came in 2011). Disposed items have status=6. The total price is not affected by number of disposed items at all which means it is always total price for all items which arrived in 2011. Total number of disposed items for that year is not displayed.

For the requested year (.e.g 2012), we need total number of items which arrived in 2012 and their total price, total number of disposed items and their price. Ending balance for that year (2012), calculated by subtracting total number of disposed items from total number of items and the price as well. Grand total for that row would be then the ending balance of 2012 (total number of items remaining in 2012) plus the ending balance of the previous year (2011's total number of items remaining).

I really have no idea where to go about it in the cross tab query design, nor i am expert on it. I wanna give it a shot since we been working manually on that report.

The access is a query that fetches data from several tables and i wanna use it to generate the report.

Any input would be really appreciated.