Discussion Groups Home

How do set up a report in Access to track monthly spending? in Access Database Reports

7/20/2005 9:07 AM PST

Question

	We used the expense report template in Access 2000 to track our expenses. I need a report that summarizes the monthly expenditures by expense report, then expense category, subtotals each expense report's totals, then totals the expenses for that month. Of course, at the end, I would want a summary all the expense reports for the year, then the grand total for the year.

Was this post helpful to you?

Top

Duane Hookom

7/20/2005 11:14 AM PST

Answer

Get all the records into a single query. Use this query as the record source
for your report. Set up the sorting and grouping (with headers and footers)
as required. Place totals in the group/report footer sections.

--
Duane Hookom
MS Access MVP

"IHarris" <IHarris@discussions.microsoft.com> wrote in message
news:CD9046DE-8CD2-4077-88CF-F75FE0925C99@microsoft.com...
> We used the expense report template in Access 2000 to track our expenses.
> I
> need a report that summarizes the monthly expenditures by expense report,
> then expense category, subtotals each expense report's totals, then totals
> the expenses for that month.
>
> Of course, at the end, I would want a summary all the expense reports for
> the year, then the grand total for the year.

Was this post helpful to you?

Top

IHarris

7/22/2005 10:14 AM PST

Thank you. I believe that will help with what I'm doing. Now next question,
how do you add reports to the same report or do you have to just do separate
reports and just print and collate them? What we're trying to do is find out
how much we spend per month, per year, per class, and per expense category. I
would like, at the end of the monthly report, to attach two reports that
total the annual expenses per class and then the annual expenses per
category. Now that I think about it, isn't there a function that subtotals
everything by month and then totals it by year? I am a real newbie to this,
as I've used queries to do most of my reports and I have no idea how to
design reports.

"Duane Hookom" wrote:

> Get all the records into a single query. Use this query as the record source
> for your report. Set up the sorting and grouping (with headers and footers)
> as required. Place totals in the group/report footer sections.
>
> --
> Duane Hookom
> MS Access MVP
>
>
> "IHarris" <IHarris@discussions.microsoft.com> wrote in message
> news:CD9046DE-8CD2-4077-88CF-F75FE0925C99@microsoft.com...
> > We used the expense report template in Access 2000 to track our expenses.
> > I
> > need a report that summarizes the monthly expenditures by expense report,
> > then expense category, subtotals each expense report's totals, then totals
> > the expenses for that month.
> >
> > Of course, at the end, I would want a summary all the expense reports for
> > the year, then the grand total for the year.
>
>
>

Was this post helpful to you?

Top

IHarris

7/22/2005 3:16 PM PST

I took your advice and it worked! However, the months are printing in
alphabetical order rather than chronologically and I want the dates to only
be for one year, not to the earliest date in the database.
"IHarris" wrote:

> We used the expense report template in Access 2000 to track our expenses. I
> need a report that summarizes the monthly expenditures by expense report,
> then expense category, subtotals each expense report's totals, then totals
> the expenses for that month.
>
> Of course, at the end, I would want a summary all the expense reports for
> the year, then the grand total for the year.

Was this post helpful to you?

Top

Duane Hookom

7/22/2005 5:30 PM PST

Set the criteria in the report's record source to only display the current
year.

Don't sort on the text month.

--
Duane Hookom
MS Access MVP

"IHarris" <IHarris@discussions.microsoft.com> wrote in message
news:F9735690-B3BF-41A2-B8D0-A7D211E05760@microsoft.com...
>I took your advice and it worked! However, the months are printing in
> alphabetical order rather than chronologically and I want the dates to
> only
> be for one year, not to the earliest date in the database.
> "IHarris" wrote:
>
>> We used the expense report template in Access 2000 to track our expenses.
>> I
>> need a report that summarizes the monthly expenditures by expense report,
>> then expense category, subtotals each expense report's totals, then
>> totals
>> the expenses for that month.
>>
>> Of course, at the end, I would want a summary all the expense reports for
>> the year, then the grand total for the year.

Was this post helpful to you?

Top

IHarris

8/16/2005 7:15 AM PST

Not clear on how to do that.

"Duane Hookom" wrote:

> Set the criteria in the report's record source to only display the current
> year.
>
> Don't sort on the text month.
>
> --
> Duane Hookom
> MS Access MVP
>
>
> "IHarris" <IHarris@discussions.microsoft.com> wrote in message
> news:F9735690-B3BF-41A2-B8D0-A7D211E05760@microsoft.com...
> >I took your advice and it worked! However, the months are printing in
> > alphabetical order rather than chronologically and I want the dates to
> > only
> > be for one year, not to the earliest date in the database.
> > "IHarris" wrote:
> >
> >> We used the expense report template in Access 2000 to track our expenses.
> >> I
> >> need a report that summarizes the monthly expenditures by expense report,
> >> then expense category, subtotals each expense report's totals, then
> >> totals
> >> the expenses for that month.
> >>
> >> Of course, at the end, I would want a summary all the expense reports for
> >> the year, then the grand total for the year.
>
>
>

Was this post helpful to you?

Top

Duane Hookom

8/16/2005 8:54 AM PST

What don't you understand: the criteria or the sorting?

I expect you have a date field. Sort on
=Month([DateField])

--
Duane Hookom
MS Access MVP
--

"IHarris" <IHarris@discussions.microsoft.com> wrote in message
news:10A24A6E-DE79-466C-8EBE-80CAD51C1629@microsoft.com...
> Not clear on how to do that.
>
> "Duane Hookom" wrote:
>
>> Set the criteria in the report's record source to only display the
>> current
>> year.
>>
>> Don't sort on the text month.
>>
>> --
>> Duane Hookom
>> MS Access MVP
>>
>>
>> "IHarris" <IHarris@discussions.microsoft.com> wrote in message
>> news:F9735690-B3BF-41A2-B8D0-A7D211E05760@microsoft.com...
>> >I took your advice and it worked! However, the months are printing in
>> > alphabetical order rather than chronologically and I want the dates to
>> > only
>> > be for one year, not to the earliest date in the database.
>> > "IHarris" wrote:
>> >
>> >> We used the expense report template in Access 2000 to track our
>> >> expenses.
>> >> I
>> >> need a report that summarizes the monthly expenditures by expense
>> >> report,
>> >> then expense category, subtotals each expense report's totals, then
>> >> totals
>> >> the expenses for that month.
>> >>
>> >> Of course, at the end, I would want a summary all the expense reports
>> >> for
>> >> the year, then the grand total for the year.
>>
>>
>>

Was this post helpful to you?

Top

marcopolo

1/12/2008 4:53 PM PST

Duane, I'm having the same problem, where does the =Month([DateField])
go?

"Duane Hookom" wrote:

> What don't you understand: the criteria or the sorting?
>
> I expect you have a date field. Sort on
> =Month([DateField])
>
> --
> Duane Hookom
> MS Access MVP
> --
>
> "IHarris" <IHarris@discussions.microsoft.com> wrote in message
> news:10A24A6E-DE79-466C-8EBE-80CAD51C1629@microsoft.com...
> > Not clear on how to do that.
> >
> > "Duane Hookom" wrote:
> >
> >> Set the criteria in the report's record source to only display the
> >> current
> >> year.
> >>
> >> Don't sort on the text month.
> >>
> >> --
> >> Duane Hookom
> >> MS Access MVP
> >>
> >>
> >> "IHarris" <IHarris@discussions.microsoft.com> wrote in message
> >> news:F9735690-B3BF-41A2-B8D0-A7D211E05760@microsoft.com...
> >> >I took your advice and it worked! However, the months are printing in
> >> > alphabetical order rather than chronologically and I want the dates to
> >> > only
> >> > be for one year, not to the earliest date in the database.
> >> > "IHarris" wrote:
> >> >
> >> >> We used the expense report template in Access 2000 to track our
> >> >> expenses.
> >> >> I
> >> >> need a report that summarizes the monthly expenditures by expense
> >> >> report,
> >> >> then expense category, subtotals each expense report's totals, then
> >> >> totals
> >> >> the expenses for that month.
> >> >>
> >> >> Of course, at the end, I would want a summary all the expense reports
> >> >> for
> >> >> the year, then the grand total for the year.
> >>
> >>
> >>
>
>
>

Was this post helpful to you?

Top

marcopolo

1/12/2008 5:20 PM PST

I tried the below and got a type mismatch. My field is a combo box with the
months in the Row Source. Below you say dont sort on text. What field
should the month be?
"marcopolo" wrote:

> Duane, I'm having the same problem, where does the =Month([DateField])
> go?
>
> "Duane Hookom" wrote:
>
> > What don't you understand: the criteria or the sorting?
> >
> > I expect you have a date field. Sort on
> > =Month([DateField])
> >
> > --
> > Duane Hookom
> > MS Access MVP
> > --
> >
> > "IHarris" <IHarris@discussions.microsoft.com> wrote in message
> > news:10A24A6E-DE79-466C-8EBE-80CAD51C1629@microsoft.com...
> > > Not clear on how to do that.
> > >
> > > "Duane Hookom" wrote:
> > >
> > >> Set the criteria in the report's record source to only display the
> > >> current
> > >> year.
> > >>
> > >> Don't sort on the text month.
> > >>
> > >> --
> > >> Duane Hookom
> > >> MS Access MVP
> > >>
> > >>
> > >> "IHarris" <IHarris@discussions.microsoft.com> wrote in message
> > >> news:F9735690-B3BF-41A2-B8D0-A7D211E05760@microsoft.com...
> > >> >I took your advice and it worked! However, the months are printing in
> > >> > alphabetical order rather than chronologically and I want the dates to
> > >> > only
> > >> > be for one year, not to the earliest date in the database.
> > >> > "IHarris" wrote:
> > >> >
> > >> >> We used the expense report template in Access 2000 to track our
> > >> >> expenses.
> > >> >> I
> > >> >> need a report that summarizes the monthly expenditures by expense
> > >> >> report,
> > >> >> then expense category, subtotals each expense report's totals, then
> > >> >> totals
> > >> >> the expenses for that month.
> > >> >>
> > >> >> Of course, at the end, I would want a summary all the expense reports
> > >> >> for
> > >> >> the year, then the grand total for the year.
> > >>
> > >>
> > >>
> >
> >
> >

Was this post helpful to you?

Top

	Return to Microsoft Communities		Notify me of replies