Sales $$ Amt. by Quarter on Same Report

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

Dee

Subject: Re: Sum $$ Order Totals Based on Date
From: "Dee" <[email protected]> Sent: 7/16/2003 9:22:35
AM

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...
..
 
One subreport would have four records (not controls), one for each quarter.
If they have to be side by side then make the report four columns with the
Page Setup menu. The totals query I provided should create quarterly totals
by CHID. If you use the Link Master/Child properties with CHID, then only
the values for the same CHID will show in the subreport.

--
Duane Hookom
MS Access MVP


Dee said:
Subject: Re: Sum $$ Order Totals Based on Date
From: "Dee" <[email protected]> Sent: 7/16/2003 9:22:35
AM

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...
 
Back
Top