Subtract from Total 1

[jasek78]

This is a Project Development/Time Management database.

I have a report that groups by Development Phase, and just shows the total for each phase, then the 'Grand Total' of All phases. I need to subtract the total of one phase from the grand total.

Any thoughts on how to do this?

thanks!

jason

 

[ssecca]

Here is one solution:

In the OpenREport Event create code to determine the value of the value to be subtracted. I for a test created the following:

Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Sum(Table1.Value) AS OutVal FROM Table1 Where (((Table1.Group)=1));&quot
subValue = rst!outval

End Sub

Make sure the value that is to be subtracted is a public variable.

Public subValue As Integer

On the report do a grand total sum as you would always then add to the ControlSorce code simalar to the following: "-[subValue]"

The end result is a grand total lass the value(s) you want to exclude.

Good Luck
ssecca

 

[jasek78]

OK, I have a bit of a problem...

1. I'm using MS Access 2000 (sorry, forgot to mention that vital vittle of info)

2. The above solution uses DAO to access the database (I'm pretty sure), but I can't find anywhere to load the DAO libraries in the script editor for Access...

I'm gonna try to convert this over to ADO...Tomorrow. Will this work? Is there a better/easier way? I'm looking for a confirmation, at least; a bit of code would be much appreciated...

Before any of you answer, let me clarify the problem...

(I genericized (sic) the fields for ease of explanation)

An Access 2000 database I have a table called 'Management' with the fields 'date', 'name', 'hours', 'project', 'phase'

I am trying to run a report grouped by phase, and that shows the total hours for each phase, and I need to sum a grand total of all phases, except 1. (see example below)

*********Example Report (simplified)************

Phase       Hours
Planning      5
Development  18
Concept      10
-------------------
Total        28 (excluding 'planning' phase)

***************************************************

I hope that this clarifies what I'm looking for...

jason

 

[ssecca]

TO add a reference open any module in design mode. Goto TOOLS...REFERENCE on the menu find and select the reference of choice. In this case look for Microsoft DAO 3.6 (I believe).

Good Luck
ssecca

 

[jasek78]

ack, I thank you as I smack myself in the head... I added the reference and now am trying to get the field names/variables correct....

jason

 

[jasek78]

It gives me a 'type mismatch' (error 13) on the Set rs line. Any ideas? I tried using an integer and a single as the variable, also I tried both "recordset" types (there is 2 of them in the type list)

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Sum(Management.HoursWorked) AS OutVal FROM Management WHERE (((Management.PhaseID)=16));")
sngValueAdded = rs!OutVal