Queries for determining unused amounts of assets/cash

[Long Tom Coffin]

A week or two ago I posted some questions I had about some design issues I had in the DB design section. The problem was resolved and the DB design came along beautifully.

Unfortunatley, this grasshopper's query-fu is apparently significantly weaker than he had anticipated, because I can't get them to work right.

Here's the background: My employer receives allotments of funds from the Feds for distribution to rural businesses. Each allotment has several contracts that are issued to various businesses. Each contract will have several fund obligations for different amounts to said businesses for various purposes. Each of these obligations will have different disbursements/invoices over periods of time as each business receives sets amounts of funds for various purposes.

Example. We receive an allotment of $100k. We sign contracts with businesses X, Y, & Z. Each contract is for $25k, meaning we now have $25K in allotments left. Each company (X, Y, & Z) wants $10k from each of their contracts obligated to them for whatever purpose they are pursuing. Then, those $10k obligations will be disbursed in $5k chunks each month for the next two months. So now you see how the line process works: allotment -> contract -> obligation -> disbursement/invoice.

Now, I am trying to design queries which I can use for reports. Here is what I am trying to do, with the End result desired placed first, followed by the calculations to be used.

Total Unobligated Allotments = Allotment Amount - Total Obligations


Unliquidated Contracts = Obligation (per contract) - Total disbursements

I've tried everything I can think of, but it usually ends up with an error message along the lines of "You have tried to Execute a query that does not include XXXX as part of an aggregate function) or something like that.


What should I do?


ETA: Also, this is for ACCESS 2007

[jzwp11]

You will probably have to use a series of queries to get the various totals. You will also have to be careful on what fields you group by in your totals queries. Can you post the structure you have?

[RayMilhon]

Don't forget to post the SQL.

[Long Tom Coffin]

You will probably have to use a series of queries to get the various totals. You will also have to be careful on what fields you group by in your totals queries. Can you post the structure you have?

Do you mean just the structure for some of the queries? Or for the whole DB?

Here's the SQL for one of the Unobligated Allotment queries I was trying to run:

SELECT tblAllotments.AllotNo, tblObligations.ObligAmount, tblObligations.[ObligDoc#], tblObligations.ObligDate, tblAllotments.AllotAmt, tblAllotments.AllotDate, [AllotAmt]-[ObligAmount] AS TotalOblig
FROM (tblAllotments INNER JOIN tblContracts ON tblAllotments.[pkAllotID] = tblContracts.[fkAllotID]) INNER JOIN tblObligations ON tblContracts.[pkContractID] = tblObligations.[fkContractID];


I'm doing something wrong with that, because the numbers don't add up when running the query

[jzwp11]

What I meant by structure is the list of tables with the key fields and the relationships between the tables, but if you want to post a copy of the database with any sensitive data removed that would be OK too.

[Long Tom Coffin]

Okay. Here's a list of the relationships, with each pk and fk in each table Report1.pdf



ETA. Tried adding the DB too, but I'm exceeding the file MB limit.

[jzwp11]

If you only want to do this:

Total Unobligated Allotments = Allotment Amount - Total Obligations

You would first create a query that sums the obligations by allotment #


query name: qryObligationsByAllotment
SELECT tblAllotments.pkAllotID, Sum(tblObligations.ObligAmount) AS SumOfObligAmount
FROM (tblAllotments INNER JOIN tblContracts ON tblAllotments.pkAllotID = tblContracts.fkAllotID) INNER JOIN tblObligations ON tblContracts.pkContractID = tblObligations.fkContractID
GROUP BY tblAllotments.pkAllotID;

Now you can join the above query back to tblAllotments in another query and do your calculation:

SELECT tblAllotments.pkAllotID, tblAllotments.AllotNo, tblAllotments.AllotDate, tblAllotments.AllotAmt, qryObligationsByAllotment.SumOfObligAmount, [allotamt]-[sumofobligamount] AS [Unobligated Allotments]
FROM tblAllotments INNER JOIN qryObligationsByAllotment ON tblAllotments.pkAllotID = qryObligationsByAllotment.pkAllotID;

You would use a similar approach for the other calculation you wanted to do.

With respect to posting the database, did you use the compact and repair utility in Access & then zip the file (and post the zipped file)?

[Long Tom Coffin]

That just dawned on me. I had to get clearance to actually d/l winzip (sigh stupid IT regulations), so here it is:


Test Proto.zip

It still looks God awful, but I'm sort of feeling my way around in the dark on this, and learning as I go.

[jzwp11]

OK, I saw a couple issues with your database. First, the relationships, you should use the type 1 in the relationship window (only include rows where the joined fields from both tables are equal). You can adjust the join type in your queries depending on how you want to present the data. Second, in tblObligations you have a field called ObligDoc#. The # sign is a reserved symbol in Access so, I changed the field name to ObligDocNo in the attached.

I added some data & copied the queries that I showed in my earlier post into the database so you can see what the output for the query. Please run the query qryAllotmentLessObligated to see the output. Hopefully it is what you are after.