Compiled Access Report

[Adam Horsman]

Greetings, I have created a supplier review database, with an elaborate final
report that sums 6 different categories that have been calculated with a
variety of formulas. (all done within the report) Currently I have a query
setup, asking me for a unique Supplier ID before I open the report, so each
report is unique for that supplier. (and calculated on the spot)

What I would like to do is create a compiled one-page report for internal
use I would like to create a compiled report with each supplier's score. My
problem is that all of the calculations are performed in another report, and
it has a query upon entry. Is there anyway to perform this task?

I hope this makes sense.

Thank you. Adam

 

[Larry Linson]

Actually, I am not certain I follow you, but I would think that you could
use a copy of the existing report (with the calculations) as the basis and
modify it to summarize.

Perhaps you could simply not use the Supplier ID as a criteria and produce
something useful.

But, my guess is that you can, at best, save re-entering the calculations,
and that to summarize on a single page, you will really be creating a new
report. I prefer to perform my calculations in the query that can be done
there, then use the query as Record Source -- that tends to make things such
as you want to do at least a little easier.

Larry Linson
Microsoft Access MVP

 

[Adam Horsman]

Larry, Thank you for the response. It is necessary for me to keep the
Supplier ID in, because I have a 1-1 relationship with my "Vendor Table."
(where my supplier names and contact information is kept)

Currently in my individual report it looks something like this:

Supplier Company XYZ

Quarter One Scores:
Defective Parts Score = 10
Later Deliveries Score = 5
Bonus Points Score = 5
TOTAL POINTS SCORE = 25

What I want my summarized report to look like is this:

Supplier Score
Supplier Company XYZ = 25
Supplier Company ABC = 30
Supplier Company TEG = 26

And so on.....

So ideally I just want to bring in the calculated quarterly scores for all
30 of my suppliers into one report.

Thanks again.

Adam Horsman

 

[Larry Linson]

Looks like it might be a rather simple Report with a Totals Query as its
Recordsource, unless there are very complicated calculations to arrive at
the various scores.

I note that in the example, the individual scores do not add up to the Total
Score -- I'm hoping that was just a transcription error in copying.

Larry Linson
Microsoft Access MVP

 

[Adam Horsman]

Larry,

Thanks again, yes that was my faux pas; I had made a hypothetical example
and apparently I'm too used to the SUM function.

The totals query recordsource makes a lot of sense. Currently most of my
calculations are determined with multiple imbedded IIF statements.
"IIF(defective parts per million is > 400, then "20",IIF DPPM > 1000,
"15"...."

You noted that the calculations cannot be comlicated with this setup, why is
this?

Thanks again.

Adam Horsman
10+5+5 = 20

 

[Larry Linson]

Totals queries provide simple totalling and other functions.

If your calculations are very complicated, you'll likely have to do them in
your own public module. That doesn't mean it can't be done, but it won't be
as easy.

Larry Linson
Microsoft Access MVP