Summing and counting in Reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I run a report of proposals sent out. I would like it to give me a count
of how many records were in the report as well as the sum of the proposal
amounts. In addition, I want it to give me a percentage of awarded contract
dollar value versus total amount bid for the selected timeframe. Thanks in
advance for your help
 
In your report footer add text boxes with control sources of:

=Count(*)
=Sum([Amount])
="How the heck do you calculate pcts"
 
Duane Hookom said:
In your report footer add text boxes with control sources of:

=Count(*)
=Sum([Amount])
="How the heck do you calculate pcts"
In addition, I want it to give me a percentage of
awarded contract dollar value versus total amount
bid for the selected timeframe.
Click to expand...
Click to expand...

Assuming, in the Report Footer, you totalled Sum([Amount]) in a Text Box
named txtTotalBid, and also have a Control called txtAwarded in which you
totalled the sum of awarded contract amounts, then create another Text Box
with a Control Source of

= txtAwarded / txtTotalBid * 100

or just = txtAwarded/txtTotalBid and format it as a percentage.

Larry Linson
Microsoft Access MVP
 
I have the folloing data in a table:
Name effectivedate rate
John smith 11/01/2004 150
John smith 11/01/2005 225
John smith 11/01/2006 245
Sam Johns 11/01/2004 150
Sam Johns 11/01/2005 225
Sam Johns 11/01/2006 245

I need to generate a report as follows:

Name effectivedate rate % Rate Change
John smith 11/01/2004 150 -
11/01/2005 225 =(225-150)/150
11/01/2006 245 =(245-225)/225

Sam Johns 11/01/2004 150 -
11/01/2005 225 =(225-150)/150
11/01/2006 245 =(245-225)/225

any suggestions is greatly appreciated.
Thanks
 
Since you have one record per year per FullName you could create a crosstab
to get your rates in columns. I have given your table the name "InA" and
changed "Name" to "FullName":

==qxtbNameRates=============
TRANSFORM Avg(InA.Rate) AS AvgOfRate
SELECT InA.FullName
FROM InA
GROUP BY InA.FullName
PIVOT "y" & DateDiff("yyyy",[EffectiveDate],Date());

Then create a query to get differences and percent change between years.

SELECT qxtbNameRates.FullName,
qxtbNameRates.y0, qxtbNameRates.y1, qxtbNameRates.y2,
([Y0]-[y1])/[Y1] AS ChangeTo0, ([Y1]-[y2])/[Y2] AS ChangeTo1
FROM qxtbNameRates;
 
Back
Top