Query join issue?

[ajkosby]

Ok, so I'm not sure if this is the correct section of the forum to post in. Here is my issue. I have 3 tables, one is called tblALLOCATIONS and lists all money in our organization that has been allocated to certain GLACs. the second table is called tblOBLIGATIONS and lists which vendors have been obligated money from certain GLACs in the tblALLOCATIONS table, not all GLACs have money obligated to a certain vendor, in fact, there are only 40 vendors out of hundreds that have money obligated to them, all the other vendors just take from the ALLOCATIONS that are not obligated. the 3rd table is called tblPURCHASES and lists all the purchases for the Organization. Right now I have the tblALLOCATIONS and tblOBLIGATIONS linked by the field ACCOUNT number and the same field is in the tblPURCHASES table and linked to tblALLOCATIONS. what I'm running into is I cannot create a query to calculate how much an OBLIGATED vendor has spent in each of their ALLOCATIONS. for example, VENDOR A could have 6 different OBLIGATIONS and 5 purchases for each OBLIGATION. I cannot find out how much is left in those OBLIGATIONS because there is nothing linking the tables together. I cannot link them by vendor because then I lose all the other vendors that we use. Here are the structures of the tables. I can run a query to tell me how much has been spent in each allocation, but I cannot run one for the obligation.

[June7]

If you would like to provide db for analysis and testing with data, follow instructions at bottom of my post.

[ajkosby]

Thanks. I know I'm missing something and I just can't figure it out. I need to be able to run reports showing how much we've spent at a certain Vendor and how much we have remaining obligated to that vendor. As well, I need to run reports showing what we've spent in each Allocation and what is remaining in each. I hope I've attached it properly.

[June7]

Are your obligations limited to no more than 3 mods? Query will have to add the 4 amount fields. Access didn't like the field names beginining with numbers, had to manually type the [] to enclose fields.

Does this get you somewhere:

query1: OblVendorAccount
SELECT tblOBLIGATIONS.VendorName, tblOBLIGATIONS.Account, Sum([ObligationAmount]+[1stModAmount]+[2ndModAmount]+[3rdModAmount]) AS TotalObl
FROM tblOBLIGATIONS
GROUP BY tblOBLIGATIONS.VendorName, tblOBLIGATIONS.Account;

query2: PurVendorAccount
SELECT tblPURCHASES.Vendor, tblPURCHASES.Account, Sum(tblPURCHASES.PurchaseAmount) AS TotPur FROM tblPURCHASES
GROUP BY tblPURCHASES.Vendor, tblPURCHASES.Account;

query3:
SELECT OblVendorAccount.VendorName, OblVendorAccount.Account, OblVendorAccount.TotalObl, PurVendorAccount.SumOfPurchaseAmount, [TotalObl]-[TotPur] AS Bal
FROM PurVendorAccount INNER JOIN OblVendorAccount ON (PurVendorAccount.Account = OblVendorAccount.Account) AND (PurVendorAccount.Vendor = OblVendorAccount.VendorName);

tblAllocations does not have date. If this will be a multi-year db, I expect will need that field and then year could be a parameter in the queries. Most of the obligations are 2012 but I see several dated 2013. Majority of purchases are in 2013. I don't know your business process so can't say how date will influence this output.

[ajkosby]

June7, so far this seems to work, except in the case when a vendor that has obligated funds was paid with non-obligated funds, which I will have to find a way around. I know, our organization doesn't do things exactly correctly.

In terms of the multi-year db, not really. I will duplicate the database every year and erase all the purchases and update all the allocations/obligations. We work off of a fiscal year that runs from October 1st - September 30, not a regular calendar year. all of the allocations would be dated 10/1/12 as that's when the budget had taken effect. Obligations can happen anytime during that year to include 6 months prior to the Fiscal Year starting, although no funds can be spent until 10/1. Right now everything is in an excel spreadsheet with 12 different tabs all referencing data in different tabs. I have to get everything into a database and create forms/reports so that someone that has no experience with excel or access can enter in our purchases and run reports for the boss. I'm just using this year's data as a test case in order to build the db.

this is the second time you've helped me. thanks! I will enter this as solved. Sorry for the all the info that was probably unnecessary. You are much appreciated though.