Summing and counting in Reports

[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

 

[Duane Hookom]

In your report footer add text boxes with control sources of:

=Count(*)
=Sum([Amount])
="How the heck do you calculate pcts"

 

[Larry Linson]

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.

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

 

[Guest]

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

 

[Duane Hookom]

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;