Hi, all, I've been trying to figure this out on my own for two days now with no luck. I've had bits and pieces of success but I just can't seem to put the pieces together and so sorry if this has been answered before, but if it was I wasn't able to find it.
I have, what I think should be a pretty simple report. We have classes starting here, we're calling them sessions. Each session has materials and these cost money. There's a sessions table, and a purchases table, the purchases table links purchases to the sessions table's ID field.
I setup a report to create an invoice for all classes between a date range. I set it up like this
Main Report
|> Purchases Report
|> Trainees Report
Inside the main report's details section is the Sessions Report and inside the Sessions Report's details section is the Purchases and Trainees reports. Then finally inside the Purchases report's details section is the price of all purchases made for that particular session.
What I want to do seems simple enough to me, I just want to total up all of the prices for all purchases made for each session. This is proving to be impossible, frustratingly so.
I've placed a textbox on the main report, I've tried to access it through the expressions editor, =[Sessions].[Report]![purchased].[Report]![PRICE] When I do this it will show 1 price from whatever the last purchases report's price field was. I can't get this to sum. If I try to do =Sum([Sessions].[Report]![purchased].[Report]![PRICE]) I just get #Error.
So I've tried also doing a group by in the footer of the Purchases report, making a textbox called subtotal and making it be a running sum of the price and setting it to "over group". This does what it's suppose to but I end up with multiple subtotal fields and each one only subtotals that particular Session's purchases, it doesn't add up across all sessions.
How can I get the total of all purchases made across all sessions? Is it possible through the expressions editor or do I have to do vba scripting or did I setup my report wrong?
I have, what I think should be a pretty simple report. We have classes starting here, we're calling them sessions. Each session has materials and these cost money. There's a sessions table, and a purchases table, the purchases table links purchases to the sessions table's ID field.
I setup a report to create an invoice for all classes between a date range. I set it up like this
Main Report
-> Sessions Report
Inside the main report's details section is the Sessions Report and inside the Sessions Report's details section is the Purchases and Trainees reports. Then finally inside the Purchases report's details section is the price of all purchases made for that particular session.
What I want to do seems simple enough to me, I just want to total up all of the prices for all purchases made for each session. This is proving to be impossible, frustratingly so.
I've placed a textbox on the main report, I've tried to access it through the expressions editor, =[Sessions].[Report]![purchased].[Report]![PRICE] When I do this it will show 1 price from whatever the last purchases report's price field was. I can't get this to sum. If I try to do =Sum([Sessions].[Report]![purchased].[Report]![PRICE]) I just get #Error.
So I've tried also doing a group by in the footer of the Purchases report, making a textbox called subtotal and making it be a running sum of the price and setting it to "over group". This does what it's suppose to but I end up with multiple subtotal fields and each one only subtotals that particular Session's purchases, it doesn't add up across all sessions.
How can I get the total of all purchases made across all sessions? Is it possible through the expressions editor or do I have to do vba scripting or did I setup my report wrong?
