Add amounts from two different rows.

Barbara69
Barbara69 used Ask the Experts™
on
I have a query that combines the beginning date from one row with the ending date from another row (see below). I need to pick up the two expended amounts associated with each of the GrantID numbers. So, I need to add the amount that goes with 11476 to the amount that goes with 11476b to get a total amount expended. Also, to account for any possible null values associated with either of the GrantIDs.

Just so you understand what I'm talking about:

Grant ID     Budget Period     Pymt1  Pymt2  Pymt3    Expended
11476        6/1/11-2/31/12     100    50     60         210
11476b       3/1/12-5/31/12      80    90    100         270

I need total expended for 11476 expended amount + 11476b expended amount = 480 that goes with the budget period 6/1/11-5/31/12.


SELECT Val([tblAward Data].[txtGrantNumber]) AS [Grantee #], (select min(t.[dteBudgetBeginDate]) from [tblAward Data] as t where  val([t].[txtGrantNumber])= val([tblAward Data].[txtGrantNumber])) & " - " & (select max(t.[dteBudgetEndDate]) from [tblAward Data] as t where  val([t].[txtGrantNumber])= val([tblAward Data].[txtGrantNumber])) AS [Budget Period], [tblAward Data].numAwardedAmt, Sum(qryRARExpenseTotals.SumOfnumFedExpense) AS FE, [tblAward Data].numApprovedAmt, tblGrantees.txtGranteeName
FROM tblGrantees LEFT JOIN ([tblProject Data] LEFT JOIN ([tblAward Data] LEFT JOIN qryRARExpenseTotals ON [tblAward Data].txtGrantNumber = qryRARExpenseTotals.txtGrantNumber) ON [tblProject Data].numProjectDataID = [tblAward Data].numProjectDataID) ON tblGrantees.numGranteeID = [tblProject Data].numGranteeID
GROUP BY Val([tblAward Data].[txtGrantNumber]), [tblAward Data].numAwardedAmt, [tblAward Data].numApprovedAmt, tblGrantees.txtGranteeName, [tblAward Data].numFYFundedBy
HAVING (((Val([tblAward Data].[txtGrantNumber])) Not Like "*S*" And (Val([tblAward Data].[txtGrantNumber]))>="10*") AND (([tblAward Data].numFYFundedBy) Like "2011"))
ORDER BY tblGrantees.txtGranteeName;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Please post a sample database.  In preparing the sample, you may want to peruse http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_9130-Getting-database-issues-resolved-while-managing-sensitive-information-sensibly.html for best practices involved in preparing sample data

Author

Commented:
Please see the attahced.
Vendor.accdb
You have two linked tables in your DB and they are used in your query. Can we get expected result without these tables?
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Author

Commented:
I doubt it, but if you run the qryVendor you'll see an Awarded and Approved Amount. Maybe you could use the Approved amounts for 10384 and 10384b as a test.
Top Expert 2016

Commented:
try this query based on  "qryVendor"


SELECT Val([txtGrantNumber]) AS [Grantee #], (select min(t.[dteBudgetBeginDate]) from [qryVendor] as t where  val([t].[txtGrantNumber])= val([qryVendor].[txtGrantNumber])) & " - " & (select max(t.[dteBudgetEndDate]) from [qryVendor] as t where  val([t].[txtGrantNumber])= val([qryVendor].[txtGrantNumber])) AS [Budget Period], Sum(qryVendor.numAwardedAmt) AS SumOfnumAwardedAmt, Sum(qryVendor.numApprovedAmt) AS SumOfnumApprovedAmt, Sum(nz([numAwardedAmt])+nz([numApprovedAmt])) AS Total
FROM qryVendor
GROUP BY Val([txtGrantNumber])
HAVING (((Val([txtGrantNumber]))="10384"));
Top Expert 2016

Commented:
here is the sample db, run "query1"
Vendor.accdb

Author

Commented:
I ran query1 and it works, but the problem for my data is the expended amount in the below is based on
Sum(qryRARExpenseTotals.SumOfnumFedExpense) AS Expended. So, what I need is a way to sum the sum to get the total expended amount of 480.


Grant ID     Budget Period     Pymt1  Pymt2  Pymt3    Expended
11476        6/1/11-2/31/12     100    50     60         210
11476b       3/1/12-5/31/12      80    90    100         270

Author

Commented:
I've attached a spreadsheet of a query of the original data before any the dates are combined along with the expended amounts. Please remember the expended amount comes from Sum(qryRARExpenseTotals.SumOfnumFedExpense).
qryNewRpt.xlsx
Top Expert 2016

Commented:
Barbara69,

you posted

Grant ID     Budget Period     Pymt1  Pymt2  Pymt3    Expended
11476        6/1/11-2/31/12     100    50     60         210
11476b       3/1/12-5/31/12      80    90    100         270


and yet on your excel file that you posted, does not reflect the names of fields that mentioned above..

can't you simply make them the same, you are making us guess which ones are the oranges and apples here.

which are Pymt1  Pymt2  Pymt3    Expended ?

to get the sum of expended, just do a sum on field Expended.

Author

Commented:
The expended column on the excel spreadsheet is the total of payments made that are gotten from the qryRARExpenseTotals and the fields for that query come from the tblGrantExpenses Detail and tblExpenseCategories tables. The expended field is already a sum of the payments, which can be anywhere between 1 and 14 made pertaining to each GrantID. The following is how the Expended amount is gotten:
Sum(tblExpenseCategories.numFedExpense) AS SumOfnumFedExpense (Expended Amount).

I need something to sum the sum of the expended field,
Sum(Sum(tblExpenseCategories.numFedExpense)) AS SumOfnumFedExpense (Expended Amount), but access won't allow it, since it's an aggregate already.

Author

Commented:
Please see the qrtvendor without the dates combined in the attached, the qryvendor1 has the dates combined. I renamed the tables and removed the links.
Vendor.accdb
May be you can show expected result for one grant from your uploaded DB?
You can test this query:
SELECT Left([tblVendor]![txtGrantNumber],5) AS [Grant], Min(tblVendor.dteBudgetBeginDate) AS [Min-dteBudgetBeginDate], Max(tblVendor.dteBudgetEndDate) AS [Max-dteBudgetEndDate], Sum([tblGrant Data].numAwardedAmt) AS [Sum-numAwardedAmt], Sum([tblGrant Data].numApprovedAmt) AS [Sum-numApprovedAmt], Sum(Nz([SumOfnumFedExpense],0)) AS SumOfSumOfnumFedExpense
FROM (tblVendor LEFT JOIN [tblGrant Data] ON tblVendor.txtGrantNumber = [tblGrant Data].txtGrantNumber) LEFT JOIN qryRARExpenses ON tblVendor.txtGrantNumber = qryRARExpenses.txtGrantNumber
WHERE (((tblVendor.txtGrantNumber) Not Like "*S*" And (tblVendor.txtGrantNumber)>"10*" And (tblVendor.txtGrantNumber) Not Like "PH*" And (tblVendor.txtGrantNumber) Not Like "P*") AND (([tblGrant Data].numFYFundedBy) Like "2011" Or ([tblGrant Data].numFYFundedBy) Like "2010"))
GROUP BY Left([tblVendor]![txtGrantNumber],5);

Open in new window

May be it is near expected

Author

Commented:
The total expended is working fine as long as I leave in ([tblGrant Data].numFYFundedBy) Like "2010")). The approved and awarded amounts are incorrect. If you look at the qryvendor and then at the query you posted, the amounts are way off.
Also, if I remove ([tblGrant Data].numFYFundedBy) Like "2010"))from the query than the dates are getting picked up incorrectly. If you look at the budget period dates when you run qryvendor for 10384 (6/1/10-8/31/11) & 10384b (9/1/11-10/31/11), the budget period should be 6/1/10-10/31/11, but without ([tblGrant Data].numFYFundedBy) Like "2010"))it's displaying as 9/1/11-10/31/11 and the total expended amount will only display $7,249, the date and amount from 10384b.

Is there a way to pick up the correct budget period, approved and awarded amounts, total expended without ([tblGrant Data].numFYFundedBy) Like "2010")) in the query?

Your original query was pickung up the dates fine and it had ([tblGrant Data].numFYFundedBy) Like "2011")).

Author

Commented:
The below is from qryvendor:

Grant Number        BPB           BPE             Awarded Amt      Approved Amt        Expended
11477             6/1/2011      3/15/2012      $115,000.00      $115,000.00        $75,000.00
11477b           3/16/2012      5/31/2012                                                      $8,747.00

The result I'm looking for is:

Grant Number        BPB           BPE             Awarded Amt      Approved Amt        Expended
11477             6/1/2011      5/31/2012      $115,000.00      $115,000.00        $83,747.00
Check this sample (query qryVendor1). You should group data in additional queries (they could be included in resulting query, but in this case it is very difficult to debug them, it can be done later)
Vendor.accdb

Author

Commented:
QryVendor0 has highlighted rows of the GrantIDs that aren't displaying in qryVendor1. QryVendor1 has highlighted rows of the GrantIDs that are displaying the awarded and approved amounts doubled.
qryVendor0.xlsx
qryVendor1.xlsx

Author

Commented:
The highlighted rows in qryVendor0 are not displaying because there are no expended amounts for those GrantIDs.
For these values I've made separate query: qryGrantData. May be you need add additional criteria there.
You have (for 10384) two records:
10384There is sum without criteria in my example.
Try to get correct data in separate queries and then combine them.
We know nothing about your data and it is not simple to get expected result.
Seems you need left join from query with all grants and other queries

Author

Commented:
I changed the join properties between the qryVendor0 and qryRARExpenses for the qryVendor1 to include all records from qryVendor0 and only those records from qryRARExpenses where the joined fields are equal. I also added an IIF statement to get it to display 0 in the total expended column if there were no expenses. Thanks for all of your assistance with my problem.

See below final query:


SELECT qryVendor0.GrantID, [qryVendor0]![Min-dteBudgetBeginDate] & " - " & [qryVendor0]![Max-dteBudgetEndDate] AS FromToDate, qryGrantData.[Sum-numAwardedAmt], qryGrantData.[Sum-numApprovedAmt], IIf(Sum([qryRARExpenses.SumOfnumFedExpense]) is null,0,Sum([qryRARExpenses.SumOfnumFedExpense])) AS Expended
FROM (qryVendor0 LEFT JOIN qryRARExpenses ON qryVendor0.GrantID = qryRARExpenses.txtGrantNumber) INNER JOIN qryGrantData ON qryVendor0.GrantID = qryGrantData.txtGrantNumber
GROUP BY qryVendor0.GrantID, [qryVendor0]![Min-dteBudgetBeginDate] & " - " & [qryVendor0]![Max-dteBudgetEndDate], qryGrantData.[Sum-numAwardedAmt], qryGrantData.[Sum-numApprovedAmt]
HAVING (((qryVendor0.GrantID) Between "10125" And "12577"));
Is it working as expected?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial