|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 8th, 2010, 11:18 AM
posted to microsoft.public.access.reports
|
|||
|
|||
Year to date expression/calculation
I have a report based on a query that has a number of calculated controls.
The query also includes a month as txtmonth which is used in the report. data is collected half yearly in June and December. I am trying to produce a report that looks like this: June 2009 Year to date Field 1 Total 2000 2000 Field 2 Total 1000 1000 Field 3 Total 3000 3000 December 2009 Year to date Field 1 Total 1000 3000 Field 2 Total 2000 3000 Field 3 Total 4000 7000 Can someone help with the formula for the controls that calculate the year to date totals? I've Googled a question on calculating YTD but can't find anything that does what I want to do. Thanks Tony -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201001/1 
|
|
#2
January 8th, 2010, 12:48 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Year to date expression/calculation
By "Year to date" do you mean calendar year, or do you mean the years
starting (June 2009 and December 2009)? What is the SQL of the query you currently have for this reports Record Source? ---- HTH Dale "TonyWilliams via AccessMonster.com" wrote: I have a report based on a query that has a number of calculated controls. The query also includes a month as txtmonth which is used in the report. data is collected half yearly in June and December. I am trying to produce a report that looks like this: June 2009 Year to date Field 1 Total 2000 2000 Field 2 Total 1000 1000 Field 3 Total 3000 3000 December 2009 Year to date Field 1 Total 1000 3000 Field 2 Total 2000 3000 Field 3 Total 4000 7000 Can someone help with the formula for the controls that calculate the year to date totals? I've Googled a question on calculating YTD but can't find anything that does what I want to do. Thanks Tony -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201001/1 .
|
|
#3
January 9th, 2010, 10:46 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Year to date expression/calculation
Hi Dale YTD is a calendar year Jan-Dec.
here is my sql: SELECT [qryasset%2].txtmonth, ([Debt%]/[SumOftxttotalline])*100 AS [Debt%2], ([PM%]/[SumOftxttotalline])*100 AS [PM%2], ([Stock%]/[SumOftxttotalline])*100 AS [Stock%2], ([Prop%]/[SumOftxttotalline])*100 AS [Prop%2], ([Cashflow%]/ [SumOftxttotalline])*100 AS [cashflow%2], ([Other%]/[SumOftxttotalline])*100 AS [other%2], [qryasset%2].[Debt%], [qryasset%2].[PM%], [qryasset%2].[Stock%], [qryasset%2].[Prop%], [qryasset%2].[Cashflow%], [qryasset%2].[Other%], [qryasset%2].SumOftxttotalline FROM [qryasset%2] GROUP BY [qryasset%2].txtmonth, [qryasset%2].[Debt%], [qryasset%2].[PM%], [qryasset%2].[Stock%], [qryasset%2].[Prop%], [qryasset%2].[Cashflow%], [qryasset%2].[Other%], [qryasset%2].SumOftxttotalline; Any help? Thanks Tony Dale Fye wrote: By "Year to date" do you mean calendar year, or do you mean the years starting (June 2009 and December 2009)? What is the SQL of the query you currently have for this reports Record Source? ---- HTH Dale I have a report based on a query that has a number of calculated controls. The query also includes a month as txtmonth which is used in the report. data [quoted text clipped - 17 lines] Thanks Tony -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201001/1
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
