Running totals

[Guest]

I have a report that generates totals for a given month for a given team. The
problem is that the only way I am getting accurate totals is if I start the
parameter at 1/1/2005. What if I want to run a report for just the month of
March? I would need the total spent for the month of march only but the
remaining total budget field should still take the total for all purchases
and give me an accurate number. The Grand Total field says this =Sum([Sum Of
Amount of Purchase]) and the remaining team budget field says this
=[BudgetTotal]-[Sum Of Amount of Purchase Grand Total Sum]. Any help with
this would be totally appreciated. I am a rookie !!

 

[Duane Hookom]

You can retrieve the Year To Date value using:
-a subreport
-DSum()
-a totals query join to your report's record source
-code
-combo box

 

[Guest]

I am very new to Access like only 2 months lol. Can you let me know the
easiest way If you could give me a start that would be great. I am also
trying to save these pages as data access pages so users will be able to go
to our intranet site and run their own monthly reports. I have been sucessful
with this but still have the running totals problem. I hope you understood
the initial question. The basic parameter for my reports says enter a start
date, enter an enddate, enter a team name. The report comes up with a totals
field based on the dates and also a year to dae field. The only way the year
to date field comes up correct is if the user starts the parameter from
1/1/2005. I hope that helps you more.

Ryan

You can retrieve the Year To Date value using:
-a subreport
-DSum()
-a totals query join to your report's record source
-code
-combo box

--
Duane Hookom
MS Access MVP
--

I have a report that generates totals for a given month for a given team.
The
problem is that the only way I am getting accurate totals is if I start
the
parameter at 1/1/2005. What if I want to run a report for just the month
of
March? I would need the total spent for the month of march only but the
remaining total budget field should still take the total for all purchases
and give me an accurate number. The Grand Total field says this =Sum([Sum
Of
Amount of Purchase]) and the remaining team budget field says this
=[BudgetTotal]-[Sum Of Amount of Purchase Grand Total Sum]. Any help with
this would be totally appreciated. I am a rookie !!

 

[Guest]

Another option:
Add a field to your query as follows --
iif(format([DateFld],"mmmm")=[Please enter desired month]),[Amount of
Purchase],0) as PurchasesForMonth
(where DateFld is the name of your purchase date field)

Then in your report footer, you'll have fields with:
=Sum([PurchasesForMonth]) and
=Sum([Budget Total]) - Sum([PurchasesForMonth])

You can retrieve the Year To Date value using:
-a subreport
-DSum()
-a totals query join to your report's record source
-code
-combo box

--
Duane Hookom
MS Access MVP
--

I have a report that generates totals for a given month for a given team.
The
problem is that the only way I am getting accurate totals is if I start
the
parameter at 1/1/2005. What if I want to run a report for just the month
of
March? I would need the total spent for the month of march only but the
remaining total budget field should still take the total for all purchases
and give me an accurate number. The Grand Total field says this =Sum([Sum
Of
Amount of Purchase]) and the remaining team budget field says this
=[BudgetTotal]-[Sum Of Amount of Purchase Grand Total Sum]. Any help with
this would be totally appreciated. I am a rookie !!

 

[Duane Hookom]

Consider using controls on forms for your users to enter criteria. This
doesn't take too much effort and improves the user experience tremendously.
Martin Green has some good tutorials on this at
http://www.fontstuff.com/access/index.htm.

Now, since your criteria is coming from a control on a form, create a query
similar (converted to a totals query) to your report's record source only
set the criteria to something like:

Field: [YourDateField]
Total: Where
Criteria: Between
DateAdd("d",-DatePart("y",Forms!frmYourForm!txtEndDate)+1,Forms!frmYourForm!txtEndDate)
And Forms!frmYourForm!txtEndDate

You can add this totals query to your report's record source and include
your ytd column.

--
Duane Hookom
MS Access MVP
--

I am very new to Access like only 2 months lol. Can you let me know the
easiest way If you could give me a start that would be great. I am also
trying to save these pages as data access pages so users will be able to
go
to our intranet site and run their own monthly reports. I have been
sucessful
with this but still have the running totals problem. I hope you understood
the initial question. The basic parameter for my reports says enter a
start
date, enter an enddate, enter a team name. The report comes up with a
totals
field based on the dates and also a year to dae field. The only way the
year
to date field comes up correct is if the user starts the parameter from
1/1/2005. I hope that helps you more.

Ryan

You can retrieve the Year To Date value using:
-a subreport
-DSum()
-a totals query join to your report's record source
-code
-combo box

--
Duane Hookom
MS Access MVP
--

I have a report that generates totals for a given month for a given
team.
The
problem is that the only way I am getting accurate totals is if I start
the
parameter at 1/1/2005. What if I want to run a report for just the
month
of
March? I would need the total spent for the month of march only but the
remaining total budget field should still take the total for all
purchases
and give me an accurate number. The Grand Total field says this
=Sum([Sum
Of
Amount of Purchase]) and the remaining team budget field says this
=[BudgetTotal]-[Sum Of Amount of Purchase Grand Total Sum]. Any help
with
this would be totally appreciated. I am a rookie !!

 

[Guest]

I know I am a pain in the butt... I am still having no luck with this, I was
wondering if their was any way I could send my table structure to someone, or
maybe a few of the queries I am having trouble with?? I am down to crunch
time with this database and need assistance... Any help would be greatly
appreciated!!!

Another option:
Add a field to your query as follows --
iif(format([DateFld],"mmmm")=[Please enter desired month]),[Amount of
Purchase],0) as PurchasesForMonth
(where DateFld is the name of your purchase date field)

Then in your report footer, you'll have fields with:
=Sum([PurchasesForMonth]) and
=Sum([Budget Total]) - Sum([PurchasesForMonth])

You can retrieve the Year To Date value using:
-a subreport
-DSum()
-a totals query join to your report's record source
-code
-combo box

--
Duane Hookom
MS Access MVP
--

I have a report that generates totals for a given month for a given team.
The
problem is that the only way I am getting accurate totals is if I start
the
parameter at 1/1/2005. What if I want to run a report for just the month
of
March? I would need the total spent for the month of march only but the
remaining total budget field should still take the total for all purchases
and give me an accurate number. The Grand Total field says this =Sum([Sum
Of
Amount of Purchase]) and the remaining team budget field says this
=[BudgetTotal]-[Sum Of Amount of Purchase Grand Total Sum]. Any help with
this would be totally appreciated. I am a rookie !!

 

[Duane Hookom]

Did you try my recent suggestion?
Can you provide the SQL view of your report's Record Source?

--
Duane Hookom
MS Access MVP
--

I know I am a pain in the butt... I am still having no luck with this, I
was
wondering if their was any way I could send my table structure to someone,
or
maybe a few of the queries I am having trouble with?? I am down to crunch
time with this database and need assistance... Any help would be greatly
appreciated!!!

Another option:
Add a field to your query as follows --
iif(format([DateFld],"mmmm")=[Please enter desired month]),[Amount of
Purchase],0) as PurchasesForMonth
(where DateFld is the name of your purchase date field)

Then in your report footer, you'll have fields with:
=Sum([PurchasesForMonth]) and
=Sum([Budget Total]) - Sum([PurchasesForMonth])

You can retrieve the Year To Date value using:
-a subreport
-DSum()
-a totals query join to your report's record source
-code
-combo box

--
Duane Hookom
MS Access MVP
--

I have a report that generates totals for a given month for a given
team.
The
problem is that the only way I am getting accurate totals is if I
start
the
parameter at 1/1/2005. What if I want to run a report for just the
month
of
March? I would need the total spent for the month of march only but
the
remaining total budget field should still take the total for all
purchases
and give me an accurate number. The Grand Total field says this
=Sum([Sum
Of
Amount of Purchase]) and the remaining team budget field says this
=[BudgetTotal]-[Sum Of Amount of Purchase Grand Total Sum]. Any help
with
this would be totally appreciated. I am a rookie !!

 

[Guest]

I was able to create a query for budget totals that took BT(Budgettotals)-AP
(amountofPurchase)=Remaining team budget. I also added a query using the iff
statement that when you run the query is gives you the certain month which
allowsfor ytd totals to come up correct. I added the sub-report to the bottom
of the ytd report. Thank you guys for all your help in this, like I said I
apologize for not knowing much

Ryan

P.S. Hope what I wrote makes sence. I need to learn a little about VB and
SQL statements. I think it will make me a much more valuable asset

Did you try my recent suggestion?
Can you provide the SQL view of your report's Record Source?

--
Duane Hookom
MS Access MVP
--

I know I am a pain in the butt... I am still having no luck with this, I
was
wondering if their was any way I could send my table structure to someone,
or
maybe a few of the queries I am having trouble with?? I am down to crunch
time with this database and need assistance... Any help would be greatly
appreciated!!!

Another option:
Add a field to your query as follows --
iif(format([DateFld],"mmmm")=[Please enter desired month]),[Amount of
Purchase],0) as PurchasesForMonth
(where DateFld is the name of your purchase date field)

Then in your report footer, you'll have fields with:
=Sum([PurchasesForMonth]) and
=Sum([Budget Total]) - Sum([PurchasesForMonth])

:

You can retrieve the Year To Date value using:
-a subreport
-DSum()
-a totals query join to your report's record source
-code
-combo box

--
Duane Hookom
MS Access MVP
--

I have a report that generates totals for a given month for a given
team.
The
problem is that the only way I am getting accurate totals is if I
start
the
parameter at 1/1/2005. What if I want to run a report for just the
month
of
March? I would need the total spent for the month of march only but
the
remaining total budget field should still take the total for all
purchases
and give me an accurate number. The Grand Total field says this
=Sum([Sum
Of
Amount of Purchase]) and the remaining team budget field says this
=[BudgetTotal]-[Sum Of Amount of Purchase Grand Total Sum]. Any help
with
this would be totally appreciated. I am a rookie !!