Sum $$ Order Totals Based on Date

  • Thread starter Thread starter Dee
  • Start date Start date
D

Dee

Create Control on Report that Sum Order Total Based on
Date. I have created a control in the report footer that
needs to sum the Order Total based on a date range. I
have created this formula that counts the number of orders
based on a date range (and it works): =Count(IIf
([ProjectDate] Between #1/1/2003# And #3/31/2003#,0)), but
I can't find a formula to sum the order total based on
those same dates.

This is not going to be a report based on a specific
quarter or date, yet all four quarters and their totals;
therefore the date range in the query is >#1/1/03#, so
that I can include all projects within that date range.
Then, on the report, I create 10 controls which extract
the information I need for each quarter on the same
report. I have also created a parameter {Enter Channel
ID], since the report will be based per REP or
Distributor. I did not create a report for each rep or
distributor because these reports are changed frequently
which would mean changing 30 reports every time management
wants a new field or a format change. This way I only
change 1 report and enter the ID number of the REP or
Distributor I want. Here are the parameter for each of the
controls on the report:

Count of Number of Projects for Each Period (first 5
controls):

1st Quarter =Count(IIf([ProjectDate] Between
#1/1/03# And #3/31/03#,0))
2nd Quarter =Count(IIf([ProjectDate] Between
#4/1/03# And #6/30/03#,0))
3rd Quarter =Count(IIf([ProjectDate] Between
#7/1/03# And #9/30/03#,0))
4th Quarter =Count(IIf([ProjectDate] Between
#10/1/03# And #12/31/03#,0))
Year to Date =Count(IIf([ProjectDate] Between
#1/1/03# And #12/31/03#,0))

Total $$ Amount Quoted Projects for Each Period (second 5
controls):

1st Quarter sum based on (IIf([ProjectDate] Between
#1/1/03# And #3/31/03#,0)) - haven't figured out formula
yet.
2nd Quarter sum based on (IIf([ProjectDate] Between
#4/1/03# And #6/30/03#,0)) - haven't figured out formula
yet.
3rd Quarter sum based on (IIf([ProjectDate] Between
#7/1/03# And #9/30/03#,0)) - haven't figured out formula
yet.
4th Quarter sum based on (IIf([ProjectDate] Between
#10/1/03# And #12/31/03#,0)) - haven't figured out formula
yet.
Year to Date

Here is a copy in SQL of the query:

SELECT Projects.QID, Projects.CHID, Channel.Channel,
Projects.ProjectDate, Projects.DeleteDuplicate,
Projects.Revised, Projects.RSMID
FROM Channel INNER JOIN Projects ON Channel.CHID =
Projects.CHID
WHERE (((Projects.CHID)=[Enter Channel ID Number]) AND
((Projects.ProjectDate)>#1/1/2003#) AND
((Projects.DeleteDuplicate)=False) AND ((Projects.Revised)
=False))
WITH OWNERACCESS OPTION;

I appreciate any help you can offer.

Dee
 
You have never provided a field or expression that you want to total...
I would create a totals query:
SELECT Projects.CHID, Sum([Field2Total]) as TotalMaybe,
Format(Projects.ProjectDate, "Q") as Qrtr
FROM Channel INNER JOIN
Projects ON Channel.CHID = Projects.CHID
WHERE ((Projects.ProjectDate)>#1/1/2003#) AND
((Projects.DeleteDuplicate)=False) AND
((Projects.Revised)=False))
GROUP BY Projects.CHID, Format(Projects.ProjectDate, "Q")

Then create a subreport based on this query and place it in a footer in your
main report. Use the Link Master/Child properties of you subreport control
to link CHID fields.
--
Duane Hookom
Microsoft Access MVP


Dee said:
Create Control on Report that Sum Order Total Based on
Date. I have created a control in the report footer that
needs to sum the Order Total based on a date range. I
have created this formula that counts the number of orders
based on a date range (and it works): =Count(IIf
([ProjectDate] Between #1/1/2003# And #3/31/2003#,0)), but
I can't find a formula to sum the order total based on
those same dates.

This is not going to be a report based on a specific
quarter or date, yet all four quarters and their totals;
therefore the date range in the query is >#1/1/03#, so
that I can include all projects within that date range.
Then, on the report, I create 10 controls which extract
the information I need for each quarter on the same
report. I have also created a parameter {Enter Channel
ID], since the report will be based per REP or
Distributor. I did not create a report for each rep or
distributor because these reports are changed frequently
which would mean changing 30 reports every time management
wants a new field or a format change. This way I only
change 1 report and enter the ID number of the REP or
Distributor I want. Here are the parameter for each of the
controls on the report:

Count of Number of Projects for Each Period (first 5
controls):

1st Quarter =Count(IIf([ProjectDate] Between
#1/1/03# And #3/31/03#,0))
2nd Quarter =Count(IIf([ProjectDate] Between
#4/1/03# And #6/30/03#,0))
3rd Quarter =Count(IIf([ProjectDate] Between
#7/1/03# And #9/30/03#,0))
4th Quarter =Count(IIf([ProjectDate] Between
#10/1/03# And #12/31/03#,0))
Year to Date =Count(IIf([ProjectDate] Between
#1/1/03# And #12/31/03#,0))

Total $$ Amount Quoted Projects for Each Period (second 5
controls):

1st Quarter sum based on (IIf([ProjectDate] Between
#1/1/03# And #3/31/03#,0)) - haven't figured out formula
yet.
2nd Quarter sum based on (IIf([ProjectDate] Between
#4/1/03# And #6/30/03#,0)) - haven't figured out formula
yet.
3rd Quarter sum based on (IIf([ProjectDate] Between
#7/1/03# And #9/30/03#,0)) - haven't figured out formula
yet.
4th Quarter sum based on (IIf([ProjectDate] Between
#10/1/03# And #12/31/03#,0)) - haven't figured out formula
yet.
Year to Date

Here is a copy in SQL of the query:

SELECT Projects.QID, Projects.CHID, Channel.Channel,
Projects.ProjectDate, Projects.DeleteDuplicate,
Projects.Revised, Projects.RSMID
FROM Channel INNER JOIN Projects ON Channel.CHID =
Projects.CHID
WHERE (((Projects.CHID)=[Enter Channel ID Number]) AND
((Projects.ProjectDate)>#1/1/2003#) AND
((Projects.DeleteDuplicate)=False) AND ((Projects.Revised)
=False))
WITH OWNERACCESS OPTION;

I appreciate any help you can offer.

Dee
Click to expand...
 
Replied to New Thread. Please don't get new threads going with each posting.
Keep this all in one posting.

--
Duane Hookom
MS Access MVP


Dee said:
Duane,

I like you idea (for another report perhaps). By the way
the field I am trying to Total is called "[Total]". The
problem remains the same. I get an over all total for all
four quarters. I need to get a formula to use in 4
different controls in the footer of the report because
each control needs to total each quarter (i.e. 1st Quarter
= $10,000, 2nd Quarter = $2,000, 3rd Quarter = $0.00, 4th
Quarter = $0.00, Total = $12,000.00. And they need to be
side by side horizontally, not vertically.

Getting the over all total on the report is easy. Finding
a formula, for say the first quarter for example, that
sums up all orders for only that quarter has been my
problem all along. I have tried making a totals query for
each quarter and then making a query to include all of the
totals queries. But I would need the CHID in those reports
to be able bring up the information by Channel (because
this report is just one of a compilations of reports that
will evaluate a rep's activities individually). However,
when I put the CHID in each of the totals queries and add
the totals queries to one query, it will not work.

-----Original Message-----
You have never provided a field or expression that you want to total...
I would create a totals query:
SELECT Projects.CHID, Sum([Field2Total]) as TotalMaybe,
Format(Projects.ProjectDate, "Q") as Qrtr
FROM Channel INNER JOIN
Projects ON Channel.CHID = Projects.CHID
WHERE ((Projects.ProjectDate)>#1/1/2003#) AND
((Projects.DeleteDuplicate)=False) AND
((Projects.Revised)=False))
GROUP BY Projects.CHID, Format(Projects.ProjectDate, "Q")

Then create a subreport based on this query and place it in a footer in your
main report. Use the Link Master/Child properties of you subreport control
to link CHID fields.
--
Duane Hookom
Microsoft Access MVP


Dee said:
Create Control on Report that Sum Order Total Based on
Date. I have created a control in the report footer that
needs to sum the Order Total based on a date range. I
have created this formula that counts the number of orders
based on a date range (and it works): =Count(IIf
([ProjectDate] Between #1/1/2003# And #3/31/2003#,0)), but
I can't find a formula to sum the order total based on
those same dates.

This is not going to be a report based on a specific
quarter or date, yet all four quarters and their totals;
therefore the date range in the query is >#1/1/03#, so
that I can include all projects within that date range.
Then, on the report, I create 10 controls which extract
the information I need for each quarter on the same
report. I have also created a parameter {Enter Channel
ID], since the report will be based per REP or
Distributor. I did not create a report for each rep or
distributor because these reports are changed frequently
which would mean changing 30 reports every time management
wants a new field or a format change. This way I only
change 1 report and enter the ID number of the REP or
Distributor I want. Here are the parameter for each of the
controls on the report:

Count of Number of Projects for Each Period (first 5
controls):

1st Quarter =Count(IIf([ProjectDate] Between
#1/1/03# And #3/31/03#,0))
2nd Quarter =Count(IIf([ProjectDate] Between
#4/1/03# And #6/30/03#,0))
3rd Quarter =Count(IIf([ProjectDate] Between
#7/1/03# And #9/30/03#,0))
4th Quarter =Count(IIf([ProjectDate] Between
#10/1/03# And #12/31/03#,0))
Year to Date =Count(IIf([ProjectDate] Between
#1/1/03# And #12/31/03#,0))

Total $$ Amount Quoted Projects for Each Period (second 5
controls):

1st Quarter sum based on (IIf([ProjectDate] Between
#1/1/03# And #3/31/03#,0)) - haven't figured out formula
yet.
2nd Quarter sum based on (IIf([ProjectDate] Between
#4/1/03# And #6/30/03#,0)) - haven't figured out formula
yet.
3rd Quarter sum based on (IIf([ProjectDate] Between
#7/1/03# And #9/30/03#,0)) - haven't figured out formula
yet.
4th Quarter sum based on (IIf([ProjectDate] Between
#10/1/03# And #12/31/03#,0)) - haven't figured out formula
yet.
Year to Date

Here is a copy in SQL of the query:

SELECT Projects.QID, Projects.CHID, Channel.Channel,
Projects.ProjectDate, Projects.DeleteDuplicate,
Projects.Revised, Projects.RSMID
FROM Channel INNER JOIN Projects ON Channel.CHID =
Projects.CHID
WHERE (((Projects.CHID)=[Enter Channel ID Number]) AND
((Projects.ProjectDate)>#1/1/2003#) AND
((Projects.DeleteDuplicate)=False) AND ((Projects.Revised)
=False))
WITH OWNERACCESS OPTION;

I appreciate any help you can offer.

Dee
Click to expand...


.
Click to expand...
Click to expand...
 
Sorry... I did it again with a new subject, but for the
future, I will keep it all in one posting. I just wanted
to make sure my questions don't get lost in the hundreds
of postings that there are. I wanted to make sure someone
could see the problem. Once I get the last question that
I posted answered, I think I'll finally be done for a
while and I need to get these reports out ASAP. Like I
said, I was afraid my question/response would get buried
and never answered by anyone.

Sorry about that.

Dee
-----Original Message-----
Replied to New Thread. Please don't get new threads going with each posting.
Keep this all in one posting.

--
Duane Hookom
MS Access MVP


Dee said:
Duane,

I like you idea (for another report perhaps). By the way
the field I am trying to Total is called "[Total]". The
problem remains the same. I get an over all total for all
four quarters. I need to get a formula to use in 4
different controls in the footer of the report because
each control needs to total each quarter (i.e. 1st Quarter
= $10,000, 2nd Quarter = $2,000, 3rd Quarter = $0.00, 4th
Quarter = $0.00, Total = $12,000.00. And they need to be
side by side horizontally, not vertically.

Getting the over all total on the report is easy. Finding
a formula, for say the first quarter for example, that
sums up all orders for only that quarter has been my
problem all along. I have tried making a totals query for
each quarter and then making a query to include all of the
totals queries. But I would need the CHID in those reports
to be able bring up the information by Channel (because
this report is just one of a compilations of reports that
will evaluate a rep's activities individually). However,
when I put the CHID in each of the totals queries and add
the totals queries to one query, it will not work.

-----Original Message-----
You have never provided a field or expression that you want to total...
I would create a totals query:
SELECT Projects.CHID, Sum([Field2Total]) as TotalMaybe,
Format(Projects.ProjectDate, "Q") as Qrtr
FROM Channel INNER JOIN
Projects ON Channel.CHID = Projects.CHID
WHERE ((Projects.ProjectDate)>#1/1/2003#) AND
((Projects.DeleteDuplicate)=False) AND
((Projects.Revised)=False))
GROUP BY Projects.CHID, Format (Projects.ProjectDate, "Q")

Then create a subreport based on this query and place
Click to expand...
it
in a footer in your
main report. Use the Link Master/Child properties of
Click to expand...
you
subreport control
to link CHID fields.
--
Duane Hookom
Microsoft Access MVP


Create Control on Report that Sum Order Total Based on
Date. I have created a control in the report footer that
needs to sum the Order Total based on a date range. I
have created this formula that counts the number of orders
based on a date range (and it works): =Count(IIf
([ProjectDate] Between #1/1/2003# And
Click to expand...
#3/31/2003#,0)),
but
I can't find a formula to sum the order total based on
those same dates.

This is not going to be a report based on a specific
quarter or date, yet all four quarters and their totals;
therefore the date range in the query is >#1/1/03#, so
that I can include all projects within that date range.
Then, on the report, I create 10 controls which extract
the information I need for each quarter on the same
report. I have also created a parameter {Enter Channel
ID], since the report will be based per REP or
Distributor. I did not create a report for each rep or
distributor because these reports are changed frequently
which would mean changing 30 reports every time management
wants a new field or a format change. This way I only
change 1 report and enter the ID number of the REP or
Distributor I want. Here are the parameter for each
Click to expand...
of
the
controls on the report:

Count of Number of Projects for Each Period (first 5
controls):

1st Quarter =Count(IIf([ProjectDate] Between
#1/1/03# And #3/31/03#,0))
2nd Quarter =Count(IIf([ProjectDate] Between
#4/1/03# And #6/30/03#,0))
3rd Quarter =Count(IIf([ProjectDate] Between
#7/1/03# And #9/30/03#,0))
4th Quarter =Count(IIf([ProjectDate] Between
#10/1/03# And #12/31/03#,0))
Year to Date =Count(IIf([ProjectDate] Between
#1/1/03# And #12/31/03#,0))

Total $$ Amount Quoted Projects for Each Period
Click to expand...
(second
5
controls):

1st Quarter sum based on (IIf([ProjectDate] Between
#1/1/03# And #3/31/03#,0)) - haven't figured out formula
yet.
2nd Quarter sum based on (IIf([ProjectDate] Between
#4/1/03# And #6/30/03#,0)) - haven't figured out formula
yet.
3rd Quarter sum based on (IIf([ProjectDate] Between
#7/1/03# And #9/30/03#,0)) - haven't figured out formula
yet.
4th Quarter sum based on (IIf([ProjectDate] Between
#10/1/03# And #12/31/03#,0)) - haven't figured out formula
yet.
Year to Date

Here is a copy in SQL of the query:

SELECT Projects.QID, Projects.CHID, Channel.Channel,
Projects.ProjectDate, Projects.DeleteDuplicate,
Projects.Revised, Projects.RSMID
FROM Channel INNER JOIN Projects ON Channel.CHID =
Projects.CHID
WHERE (((Projects.CHID)=[Enter Channel ID Number]) AND
((Projects.ProjectDate)>#1/1/2003#) AND
((Projects.DeleteDuplicate)=False) AND ((Projects.Revised)
=False))
WITH OWNERACCESS OPTION;

I appreciate any help you can offer.

Dee



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top