Query calculations problem

[ajkosby]

I'm setting up a database that needs to track spending. I have when funds we have allocated to certain areas in one table. then I have funds that are obligated to certain vendors in another. all of the funds allocated do not get obligated, sum stay un-obligated. also, more than one vendor will be obligated funds from the same allocation code. so far when link the tables, everything works fine.

here is the issue. I'm having trouble running a query to calculate what funds are left. Here is the structure of my tables

tblALLOCATIONS:

AllocationID
Account
AccountCode
Program
GLAC
Description
AllocationAmount

tblOBLIGATIONS:
ObligationID
Account
Vendor
DateInitiated
Purpose
ObligationAmount

I want to subtract "ObligationAmount" from "AllocationAmount". It sort of works if there is only one instance of a vendor being obligated from a certain allocation. when I have more than one vendor that has funds obligated from the same allocation, then the query doesn't work. here is the query structure I was trying.

Field: Account | Expr1: Sum([AllocationAmount]+[ObligationAmount])
Table: tblALLOCATIONS
Total: Group by | Expression


I have the Obligated numbers entered in a negative format. such as if $25,000 have been obligated to PENSKE, I entered it as ($25,000). I thought this would help, but it just does the same thing as if they were positive numbers and I subtract them instead. Instead of subtracting the total of the ObligationAmount from the AllocationAmount, it adds all of it together and I get one huge number.

I'm not sure what I'm doing wrong. Please help.



thank you.

[Dal Jeanis]

Suppose for account X you have one allocation for $1000 and two obligations for $100 and $200. When you join the tables, you get two records like this

X  $1000 $100 [net $900]
X  $1000 $200 [net $800]

X  $2000 $300 [net $1700]  GROUP LEVEL

Suppose for account Y you have two allocations for $1000 and $1500 and three obligations for $100, $150 and $200. When you join the tables, you get six records like this

X  $1000 $100 [net  $900]
X  $1000 $150 [net  $850]
X  $1000 $200 [ne t $800]
X  $1500 $100 [net $1400]
X  $1500 $150 [net $1350]
X  $1500 $200 [net $1300]

X  $7500 $900 [net $6600]  GROUP LEVEL

What you have is a partial CROSS JOIN. You can't join them that way and get sensible results. You'll need a UNION. Something like the following:

SELECT Account, Sum(NetAllocation) AS NetAllocation
FROM
   (
   SELECT TA.Account AS Account, TA.AllocationAmount AS NetAllocation
   FROM tblALLOCATIONS As TA 
 UNION
   SELECT TO.Account AS Account, TO.ObligationAmount AS NetAllocation
   FROM tblOBLIGATIONS As TO 
   )
GROUP BY ACCOUNT

Or if UNIONs make you queasy, you can do the SUMs of each table, then LEFT JOIN them after the GROUP BY has combined the allocations and the obligations to a single record each:

SELECT Account, (SumAllocation - NZ(SumObligation,0)) AS NetAllocation
FROM
   (
   SELECT TA.Account, Sum(TA.AllocationAmount) AS SumAllocation
   FROM tblALLOCATIONS As TA 
   GROUP BY TA.Account
   )
 LEFT JOIN
   (
   SELECT TO.Account, Sum(TO.ObligationAmount) AS SumObligation
   FROM tblOBLIGATIONS As TO 
   GROUP BY TO.Account
   )
 ON TA.Account = TO.Account

The above assumes there will always be an allocation before there is an obligation. I did a LEFT JOIN to avoid killing allocations that hadn't been obligated yet. If there may be obligations beofrer the allocation gets entered, then you might want to do a full outer join and add a the NZ function around the SumAllocation term.

[ajkosby]

Thanks! It's been a few years since I created a database and this is what I needed to refresh my memory on how things work. I will give it a try. And you are correct, there will always be an allocation before an obligation can be made.

[Dal Jeanis]

Good. Okay, if it works, please mark the thread "solved". Top of page, under "thread tools".