Report Percentages

R

red

Hi,
I was wondering if anyone has an idea on how I can create a percentag
automatically. I have a field with the following as a contro
scource:
=([ReportTotal]/[query!totalpeople!Expr1]) wnen I run the report,
get a txt box
asking me for the [query!totalpeople!Expr1] amount. When I enter
number, there is a percentage which is correct at the end of my report
My question is, how can I get the control scource to pull thi
information automatically as [query!totalpeople!Expr1] is a tota
people query. I have a Report total number from my report and a quer
with total people. I hope this is enough infromation for someone.
Thanks in advance for for your time and effort.

VR,
Re
 
D

Duane Hookom

First, alias Expr1 in your query to a name that makes sense. Then, add the
TotalPeople query to your report's record source. You can then add the
properly named column to your report's record source and use it in your
calcs.

This all assumes the TotalPeople query returns only one record.
 
R

red

Thanks for the suggestion. Yes, my total people query only returns on
record so it should work. I'll let you know.

Many Thanks,
Re
 
R

red

Duane,
My control source now looks like this:
=([ReportTotal]/[totalpeople!Total]) and I am still getting a ente
peramiter box before the form opens to enter a total. Any idea as t
why this information isn't pulling automatically? I did add th
totalpeople query to my form record source? Thanks!

Re
 
R

red

Thanks Duane,

I tried it and still getting the "enter Perameter Value" txt box. I
seems that the form is not picking up the totalpeople query even thoug
it is in the form data source. Thanks tho for your time it i
apprecaited. I'll keep trying to see why??????

Re
 
R

red

Thanks Duane,

I tried it and still getting the "enter Perameter Value" txt box. I
seems that the form is not picking up the totalpeople query even thoug
it is in the form data source. Thanks tho for your time it i
apprecaited. I'll keep trying to see why??????

Re
 
R

red

Thanks Duane,

I tried it and still getting the "enter Perameter Value" txt box. I
seems that the form is not picking up the totalpeople query even thoug
it is in the form data source. Thanks tho for your time it i
apprecaited. I'll keep trying to see why??????

Re
 
D

Duane Hookom

Are you referencing a "form is not picking up the totalpeople query" or is
this a report?
Can you provide the SQL view of your report's record source?
 
R

red

Duane,
My mistake, it is in a report. I can send you the SQL of the repor
record source. it is a query. Do you want that SQL? It is rathe
long. It's a completely different query then my totalpeople query.

Re
 
R

red

Duane,
Here is the query SQL for my report source, hope it make sense.

SELECT tblAdminPersonnel_1.LAST, tblAdminPersonnel_1.FIRST
tblAdminPersonnel_1.CurrentTime, tblRanks.Rank, tblUnits.Squadron
FROM tblRecallTime, tblUnits INNER JOIN (tblRanks INNER JOI
tblAdminPersonnel_1 ON tblRanks.RankID = tblAdminPersonnel_1.RankID) O
tblUnits.UnitID = tblAdminPersonnel_1.SQUAD_ID
WHERE (((tblRanks.Rank)<>"civ") AN
((DateDiff("n",[tblRecallTime.RecallTime],[tblAdminPersonnel_1.CurrentTime]))<60
AND ((tblAdminPersonnel_1.STATUS_ID)=1) AN
((tblAdminPersonnel_1.Archived)=No) AN
((tblAdminPersonnel_1.ReportForDuty)=Yes))
ORDER BY tblUnits.Squadron;

Re
 
R

red

Duane,
I tried to post before but it hasn't came through, now it probably wil
twice :)

Here is the query SQL for my report source, hope it make sense.

SELECT tblAdminPersonnel_1.LAST, tblAdminPersonnel_1.FIRST
tblAdminPersonnel_1.CurrentTime, tblRanks.Rank, tblUnits.Squadron
FROM tblRecallTime, tblUnits INNER JOIN (tblRanks INNER JOI
tblAdminPersonnel_1 ON tblRanks.RankID = tblAdminPersonnel_1.RankID) O
tblUnits.UnitID = tblAdminPersonnel_1.SQUAD_ID
WHERE (((tblRanks.Rank)<>"civ") AN
((DateDiff("n",[tblRecallTime.RecallTime],[tblAdminPersonnel_1.CurrentTime]))<60
AND ((tblAdminPersonnel_1.STATUS_ID)=1) AN
((tblAdminPersonnel_1.Archived)=No) AN
((tblAdminPersonnel_1.ReportForDuty)=Yes))
ORDER BY tblUnits.Squadron;

Re
 
D

Duane Hookom

This is your SQL
SELECT
tblAdminPersonnel_1.LAST,
tblAdminPersonnel_1.FIRST,
tblAdminPersonnel_1.CurrentTime,
tblRanks.Rank,
tblUnits.Squadron

FROM tblRecallTime,
tblUnits INNER JOIN
(tblRanks INNER JOIN
tblAdminPersonnel_1
ON tblRanks.RankID = tblAdminPersonnel_1.RankID)
ON tblUnits.UnitID = tblAdminPersonnel_1.SQUAD_ID

WHERE (((tblRanks.Rank)<>"civ") AND
((DateDiff("n",[tblRecallTime.RecallTime],
[tblAdminPersonnel_1.CurrentTime]))<60)
AND ((tblAdminPersonnel_1.STATUS_ID)=1) AND
((tblAdminPersonnel_1.Archived)=No) AND
((tblAdminPersonnel_1.ReportForDuty)=Yes))

ORDER BY tblUnits.Squadron;

I don't see the query/table totalpeople or the field Total. You can't use
the field name without including it in the report's record source.
 
R

red

Duane,
Manyh Thanks for for your help. I was thinking about what you sai
asking for my SQL. Realizing that the query wasn't part of the SQL,
added it and the percentage works fine. Many Thanks Again.
Re
 
Top