Summing Groups in Reports

[WorkRelated]

I have created a data base that holds information about jobs we have fitted.
Job Name, Nett Value, Fitting Team, Start Date and Finish Date.
From this I have a query with the parameters set to ask for a Between And
for the Start Date and the Finish Date. The report is grouped into fitting
team then month with a total Nett per team per month, then a total Nett of
every job in the Report Footer.

When running a report for April figures - I enter the Start Date parameters
as 01-03-09 to 30-04-09 and the Finish Date as 01-04-09 to 31-05-09. The
purpose of the report it to gain a figure of all jobs charged out in April
and to have a list of Job Names that we are expecting to be charged for the
supply of the products. This would be simple if we started all jobs in April
and finished them in April but as we sometimes start a job in April and
finish it in May (making the nett value a May figure) I am finding it
difficult to gain the April fitting total.

I would like the total in the footer to only sum together the jobs that have
finished in April, at the moment I am getting a total of all the jobs, I know
this is the parameters I have set so the total is correct but the purpose of
the report is to combine many reports I am running at the moment into one and
get the figure of jobs finished in April.

I am struggling to do this so it will work for every report run. IE May
report will be for jobs started between 01-04-09 to 31-05-09 and jobs
finished between 01-05-09 to 30-06-09 only adding nett value of jobs finish
in May. Maybe I have set the query up incorrectly please help if this is
possible. Thanks in advance

Example Report Below

Kenny [Fitting Team] Fitting Team Header
April [Finish Date] £100 [Nett Value per Team per Month] Fitting Date Header
[Job Name] [Nett Value] Report Detail
Smith £100

Brian [Fitting Team] Fitting Team Header
April [Finish Date] £250 [Nett Value per Team per Month] Fitting Date Header
[Job Name] [Nett Value] Report Detail
Jones £100
Brown £150

May [Finish Date] £150 [Nett Value per Team per Month] Fitting Date Header
[Job Name] [Nett Value] Report Detail
Grant £150

£500 [SumofNett] *** I want this total to be £350 just the total of the
April jobs***

 

[Wayne-I-M]

Hi

Bit lost with what you're looking for. You say you want to sum the amounts
for April - how do you tell the application which year.

eg
= Sum ( IIf (DateSerial(Year(Date()), Month([DateField]),1), [Amount]))
would give you the start of this year's month in the date field
DateSerial(Year(Date()), Month([DateField)+1, 0)
will give you the end of the month - just do a between these 2. But don't
think this is what you looking for

But how will you sellect the date(s) - popup form, perameter, etc

Can you cive a simple breakdown of how you're doing things, sure someone
here will be able to to give you help



--
Wayne
Manchester, England.

I have created a data base that holds information about jobs we have fitted.
Job Name, Nett Value, Fitting Team, Start Date and Finish Date.
From this I have a query with the parameters set to ask for a Between And
for the Start Date and the Finish Date. The report is grouped into fitting
team then month with a total Nett per team per month, then a total Nett of
every job in the Report Footer.

When running a report for April figures - I enter the Start Date parameters
as 01-03-09 to 30-04-09 and the Finish Date as 01-04-09 to 31-05-09. The
purpose of the report it to gain a figure of all jobs charged out in April
and to have a list of Job Names that we are expecting to be charged for the
supply of the products. This would be simple if we started all jobs in April
and finished them in April but as we sometimes start a job in April and
finish it in May (making the nett value a May figure) I am finding it
difficult to gain the April fitting total.

I would like the total in the footer to only sum together the jobs that have
finished in April, at the moment I am getting a total of all the jobs, I know
this is the parameters I have set so the total is correct but the purpose of
the report is to combine many reports I am running at the moment into one and
get the figure of jobs finished in April.

I am struggling to do this so it will work for every report run. IE May
report will be for jobs started between 01-04-09 to 31-05-09 and jobs
finished between 01-05-09 to 30-06-09 only adding nett value of jobs finish
in May. Maybe I have set the query up incorrectly please help if this is
possible. Thanks in advance

Example Report Below

Kenny [Fitting Team] Fitting Team Header
April [Finish Date] £100 [Nett Value per Team per Month] Fitting Date Header
[Job Name] [Nett Value] Report Detail
Smith £100

Brian [Fitting Team] Fitting Team Header
April [Finish Date] £250 [Nett Value per Team per Month] Fitting Date Header
[Job Name] [Nett Value] Report Detail
Jones £100
Brown £150

May [Finish Date] £150 [Nett Value per Team per Month] Fitting Date Header
[Job Name] [Nett Value] Report Detail
Grant £150

£500 [SumofNett] *** I want this total to be £350 just the total of the
April jobs***

 

[WorkRelated]

At the moment I have a Start Date Field and Finish Date Field which I have
set the Between And Parameter in the query. The report then shows all the
nett values for all jobs that have started in march finished in april,
started in april and finished in may. As i am running an April report I only
want the total of nett contracts finished in April (which I have grouped
together by month of finish date) to appear in the report footer, but i am
not sure how to ask access to only add certain netts values

Hi

Bit lost with what you're looking for. You say you want to sum the amounts
for April - how do you tell the application which year.

eg
= Sum ( IIf (DateSerial(Year(Date()), Month([DateField]),1), [Amount]))
would give you the start of this year's month in the date field
DateSerial(Year(Date()), Month([DateField)+1, 0)
will give you the end of the month - just do a between these 2. But don't
think this is what you looking for

But how will you sellect the date(s) - popup form, perameter, etc

Can you cive a simple breakdown of how you're doing things, sure someone
here will be able to to give you help



--
Wayne
Manchester, England.

I have created a data base that holds information about jobs we have fitted.
Job Name, Nett Value, Fitting Team, Start Date and Finish Date.
From this I have a query with the parameters set to ask for a Between And
for the Start Date and the Finish Date. The report is grouped into fitting
team then month with a total Nett per team per month, then a total Nett of
every job in the Report Footer.

When running a report for April figures - I enter the Start Date parameters
as 01-03-09 to 30-04-09 and the Finish Date as 01-04-09 to 31-05-09. The
purpose of the report it to gain a figure of all jobs charged out in April
and to have a list of Job Names that we are expecting to be charged for the
supply of the products. This would be simple if we started all jobs in April
and finished them in April but as we sometimes start a job in April and
finish it in May (making the nett value a May figure) I am finding it
difficult to gain the April fitting total.

I would like the total in the footer to only sum together the jobs that have
finished in April, at the moment I am getting a total of all the jobs, I know
this is the parameters I have set so the total is correct but the purpose of
the report is to combine many reports I am running at the moment into one and
get the figure of jobs finished in April.

I am struggling to do this so it will work for every report run. IE May
report will be for jobs started between 01-04-09 to 31-05-09 and jobs
finished between 01-05-09 to 30-06-09 only adding nett value of jobs finish
in May. Maybe I have set the query up incorrectly please help if this is
possible. Thanks in advance

Example Report Below

Kenny [Fitting Team] Fitting Team Header
April [Finish Date] £100 [Nett Value per Team per Month] Fitting Date Header
[Job Name] [Nett Value] Report Detail
Smith £100

Brian [Fitting Team] Fitting Team Header
April [Finish Date] £250 [Nett Value per Team per Month] Fitting Date Header
[Job Name] [Nett Value] Report Detail
Jones £100
Brown £150

May [Finish Date] £150 [Nett Value per Team per Month] Fitting Date Header
[Job Name] [Nett Value] Report Detail
Grant £150

£500 [SumofNett] *** I want this total to be £350 just the total of the
April jobs***