Data Filtering on a Report

[newguy86]

Hi All,
I have a table that stores three different types of data (PID Hours, Revised Hours, & Actual Hours) for each package that we have so that I can report on them. The problem is that I also need to calculate the variance between some of those types.

Here is what the Table/Data looks like:
StID Pnum Source Type Hours
101 1 Db PID 20
101 1 Db Revised 15
101 1 Db Actual 25
101 2 Db PID 10
101 2 Db Revised 12
101 2 Db Actual 15
108 6 Db PID 5
108 6 Db Revised 8
108 6 Db Actual 7


What I tried to setup is a report that had three rows in the detail section and I tried to get each Type field to filter on a specific type so that I could add in a couple of fields to calculate the variance. Unfortunately everytime I try to put in a reference it either throws an error or a popup box appears wanting me to input data that it should get automatically.

Any suggestions on either a better way to do this or what I'm doing wrong?


Travis

http://www.apexsystemsinc.com

 

[newguy86]

One more thing...I'm running on Access 2007 if that makes any difference!

Travis

http://www.apexsystemsinc.com

 

[SkipVought]

You might what to pivot your report
[tt]
Sum of Hours Type
StID Pnum Source Actual PID Revised Total
101 1 Db 25 20 15 60
2 Db 15 10 12 37
108 6 Db 7 5 8 20
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[newguy86]

How would I calculate the variance between types?

For Example:

-PID minus Actuals
-Revised minus Actuals

Every time I try to setup the variance it calculates all three together by adding instead of subtracting.

Travis

http://www.apexsystemsinc.com

 

[dhookom]

Next time, please provide a table name.
Create a crosstab query like:

TRANSFORM Sum(tblNewGuy86.Hours) AS SumOfHours
SELECT tblNewGuy86.StID, tblNewGuy86.Pnum, tblNewGuy86.Source
FROM tblNewGuy86
GROUP BY tblNewGuy86.StID, tblNewGuy86.Pnum, tblNewGuy86.Source
PIVOT tblNewGuy86.Type In ("PID","Revised","Actual");

[tt]
StID Pnum Source PID Revised Actual
101 1 Db 20 15 25
101 2 Db 10 12 15
101 6 Db 5 8 7
[/tt]

Duane
Hook'D on Access
MS Access MVP