Report sum from another table....

[soupisgood84]

Here is the situation....
I have a report that gets data from a query from one table. In the report it displays:
Date, Item Description, Department Name and Price.
The report is divided by department and in the footer I have the Price totaled for each.
In another table I have the projected spending limit for each department.

What I would like the report to do is:
Display the projected amount for each department, subtract the actual amount spent, then give me an amount remaining.

Any ideas......thanks in advance

 

[PHV]

Have a look at the DLookUp function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[soupisgood84]

The dlookup function will only return the value that you request, unless I am mistaken.

 

[soupisgood84]

apologies.....I believe that it will work for my purposes.

 

[PHV]

Yes, in the department's footer you request the projected amount for the current department.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[soupisgood84]

Once requested wouldn't in remain the same?
how would I get it to change for each department?

This is in the ON OPEN event:

Me.txtBudgeted = DLookup("Hardware", "tblBudget", [qurBudgetUnbound]![Department])

BTW....this code doesn't work.

 

[PHV]

Once requested wouldn't in remain the same?
Did you read my post stamped 13 Jun 07 16:40 ?
this code doesn't work
Feel free to press the F1 key when the cursor is inside the DLookUp word.

 

[soupisgood84]

Put this in the Control Source and it displayed "error":
=DLookUp("Hardware","tblBudget",[Department])
Put this in the Control Source and it only displayed the first row's value:
=DLookUp("Hardware","tblBudget",[qurBudgetUnbound]![Department] )