Show Previous Year's Totals as Balance Brought Forward on Current Year's Report (1 Viewer)

[Tophan]

Hello,

I have a report that is based on a crosstab query which shows the deliveries of water tanks by year and month. The row headings are year and month, column headings are the different size water tanks and the value is the qty received.

For this new year, I would like the 2022 deliveries as a balance brought forward and not see the monthly breakdown for 2022. I tried starting a new report for 2023 with the following formula but I am getting an #error or #Name? error message.

=DSum("QtyRcvd", "qryRotoplasticsDelNotes_Crosstab", "System = 'Potable Water Tank - 450-gal'" AND "Year = #2022")

 

[June7]

Year is a reserved word. Advise not to use reserved words as names. Enclose in []: [Year]. Why is there a # in front of 2022? Remove the quotes before and after AND.

 

[Tophan]

Thank you! I made the changes but was still getting the #Name? error message. However, I ran a basic query (not a cross tab query) and used that as the domain and the forumla with your recommended changes worked.

thanks again!

 

[Tophan]

Year is a reserved word. Advise not to use reserved words as names. Enclose in []: [Year]. Why is there a # in front of 2022? Remove the quotes before and after AND.

A different issue has arisen.

There are 4 sizes of water tanks but only 2 sizes were delivered in January. When I try running the report I am getting an error message stating that the access database does not recognize the [tank size] as a valid field name or expression. Is there a way for a crosstab query to show zeros?

 

[June7]

One way is to join to a dataset that is a list of all possible tank sizes/periods in order to show them even if there is no data for particular period.
Another is to specify column header names in CROSSTAB PIVOT. Review http://allenbrowne.com/ser-67.html#ColHead

Could provide database for analysis.

 

[Tophan]

One way is to join to a dataset that is a list of all possible tank sizes/periods in order to show them even if there is no data for particular period.
Another is to specify column header names in CROSSTAB PIVOT. Review http://allenbrowne.com/ser-67.html#ColHead

Could provide database for analysis.

Thank you

 

[June7]

This shows how to do in query designer https://www.quackit.com/microsoft_a...fields_in_a_crosstab_query_in_access_2016.cfm

 

[Tophan]

This shows how to do in query designer https://www.quackit.com/microsoft_a...fields_in_a_crosstab_query_in_access_2016.cfm

This method worked perfectly...thank you so much for your assistance. I really appreciate it! Definitely saving this article.