Calculating Sum Amount in a query help 2

[iuianj07]

Hello guys,

First of all, I apologize for the lengthy post, and thanks for the patience in reading this.

I am about to create a Financial Spread Database, wherein we gather loan information from our clients (which are banks) including properties' Financial Statements and Rent Roll.

What we'll do is we spread (enter financial data)the financial statement and Rent Roll into the database, make some adjustment into amounts then send it back to the client...

My table structure is below:

tblJobTracking
JobTrackingID -- PK
ReportingPeriod
CompanyID -- FK to tblCompany
InvestorNumber
LoanNumber
PropertyName
PropertyTypeID -- FK to tblPropertyType
etc..(other loan info)

tblCompany -- list of different clients/banks that we'll do work for

CompanyID -- PK
CompanyName

tblPropertyType -- list of different Property Types (ex. Office, Multi-family, Retail)

PropertyTypeID
PropertyType

tblIncome -- this is where we input Income line items from the financial statements we receive from the client

IncomeID -- PK
JobTrackingID -- FK to tblJobTracking
RevLineItemsID -- FK to tblRevLineItems
RevMappingID -- FK to tblMappingLegendRev
Amount -- dollar amount of how much the line item is
Adjustment -- dollar amount that sometimes need to adjust from Amount

tblExpenses -- this is where we input Expense line items from the financial statements we receive from the client

ExpenseID -- PK
JobTrackingID -- FK to tblJobTracking
ExpLineItemsID -- FK to tblExpLineItems
ExpMappingID -- FK to tblMappingLegendExp
Amount -- dollar amount of how much the line item is
Adjustment -- dollar amount that sometimes need to adjust from Amount

tblRevLineItems -- different line items that could be found in the Financial Statement (ex. Base Rent, Laundry Income, Pet Fees)

RevLineItemsID -- PK
RevLineItems

tblExpLineItems -- same desc as above (ex. Roof Repairs, Manager's Salary, Advertising and Marketing,)

ExpLineItemsID
ExpLineItems


tblMappingLegendRev -- list of categories each line items is associated to (ex. Gross Potential Rent, Other Income)

RevMappingID -- PK
MappingLineItem

tblMappingLegendExp -- list of categories each line items is associated to (ex. Real Estate Taxes, Repairs and Maintenance)

ExpMappingID -- PK
MappingLineItem



So with the table strucutre above, I have a form frmFinancialSpread, which is bound to this query:

SELECT tblJobTracking.JobTrackingID, tblJobTracking.ReportingPeriod, tblCompany.CompanyName, tblJobTracking.InvestorNumber, tblJobTracking.LoanNumber, tblJobTracking.PropertyNumber, tblJobTracking.ConsolidatedStatement, tblJobTracking.LoanBalance, tblJobTracking.FinancialStmt, tblJobTracking.FS_VendorReceived, tblJobTracking.RentRoll, tblJobTracking.RR_VendorReceived, tblJobTracking.SecuritizationDate, tblJobTracking.FirstPeriodDue, tblJobTracking.Subserviced, tblJobTracking.SubservicerName, tblJobTracking.BorrowerName, tblJobTracking.PropertyName, tblJobTracking.PropertyStreet, tblJobTracking.PropertyCity, tblJobTracking.PropertyState, tblJobTracking.PropertyZipCode, tblPropertyType.PropertyType, tblJobTracking.NetSquareFeetatContribution, tblJobTracking.NumberUnitsBedsRoomsatContribution, tblJobTracking.UW, tblJobTracking.AssumptionDate, tblJobTracking.RushRequest, tblJobTracking.RushReqDate, tblJobTracking.Withdraw, tblJobTracking.InternalComments
FROM tblPropertyType INNER JOIN (tblCompany INNER JOIN tblJobTracking ON tblCompany.CompanyID = tblJobTracking.CompanyID) ON tblPropertyType.PropertyTypeID = tblJobTracking.PropertyTypeCMSA;

then I have subforms (set as continous forms and are linked to the main form via JobTrackingID) named subfrmIncome and subfrmExpense which are bound to these queries:

subfrmIncome:

SELECT tblIncome.IncomeID, tblIncome.JobTrackingID, tblIncome.RevLineItemsID, tblIncome.RevMappingID, tblIncome.Amount, tblIncome.Adjustment, [Amount]+[Adjustment] AS [Adjusted Amount]
FROM tblIncome;

subfrmExpense:

SELECT tblExpenses.ExpenseID, tblExpenses.JobTrackingID, tblExpenses.ExpLineItemsID, tblExpenses.ExpMappingID, tblExpenses.Amount, tblExpenses.Adjustment, [Amount]+[Adjustment] AS [Adjusted Amount]
FROM tblExpenses;

Now I try to enter dummy data and entered some line items and with multiple dollar amounts...

my question though is how am I going to calculate the NOI (Net Operating Income) wherein the Sum of all Income line items minus the Sum of all Expense line items per loan?

I tried to change frmFinancialSpread's query to:

SELECT tblJobTracking.JobTrackingID, tblJobTracking.ReportingPeriod, tblCompany.CompanyName, tblJobTracking.InvestorNumber, tblJobTracking.LoanNumber, tblJobTracking.PropertyNumber, tblJobTracking.ConsolidatedStatement, tblJobTracking.LoanBalance, tblJobTracking.FinancialStmt, tblJobTracking.FS_VendorReceived, tblJobTracking.RentRoll, tblJobTracking.RR_VendorReceived, tblJobTracking.SecuritizationDate, tblJobTracking.FirstPeriodDue, tblJobTracking.Subserviced, tblJobTracking.SubservicerName, tblJobTracking.BorrowerName, tblJobTracking.PropertyName, tblJobTracking.PropertyStreet, tblJobTracking.PropertyCity, tblJobTracking.PropertyState, tblJobTracking.PropertyZipCode, tblPropertyType.PropertyType, tblJobTracking.NetSquareFeetatContribution, tblJobTracking.NumberUnitsBedsRoomsatContribution, tblJobTracking.UW, tblJobTracking.AssumptionDate, tblJobTracking.RushRequest, tblJobTracking.RushReqDate, tblJobTracking.Withdraw, tblJobTracking.InternalComments, Sum([tblIncome].[Amount])-Sum([tblExpenses].[Amount]) AS NOI
FROM tblPropertyType INNER JOIN (((tblCompany INNER JOIN tblJobTracking ON tblCompany.CompanyID = tblJobTracking.CompanyID) INNER JOIN tblExpenses ON tblJobTracking.JobTrackingID = tblExpenses.JobTrackingID) INNER JOIN tblIncome ON tblJobTracking.JobTrackingID = tblIncome.JobTrackingID) ON tblPropertyType.PropertyTypeID = tblJobTracking.PropertyTypeCMSA
GROUP BY tblJobTracking.JobTrackingID, tblJobTracking.ReportingPeriod, tblCompany.CompanyName, tblJobTracking.InvestorNumber, tblJobTracking.LoanNumber, tblJobTracking.PropertyNumber, tblJobTracking.ConsolidatedStatement, tblJobTracking.LoanBalance, tblJobTracking.FinancialStmt, tblJobTracking.FS_VendorReceived, tblJobTracking.RentRoll, tblJobTracking.RR_VendorReceived, tblJobTracking.SecuritizationDate, tblJobTracking.FirstPeriodDue, tblJobTracking.Subserviced, tblJobTracking.SubservicerName, tblJobTracking.BorrowerName, tblJobTracking.PropertyName, tblJobTracking.PropertyStreet, tblJobTracking.PropertyCity, tblJobTracking.PropertyState, tblJobTracking.PropertyZipCode, tblPropertyType.PropertyType, tblJobTracking.NetSquareFeetatContribution, tblJobTracking.NumberUnitsBedsRoomsatContribution, tblJobTracking.UW, tblJobTracking.AssumptionDate, tblJobTracking.RushRequest, tblJobTracking.RushReqDate, tblJobTracking.Withdraw, tblJobTracking.InternalComments;

but it gives the wrong NOI, which I kinda understand why..

if my Financial Spread is:

subfrmIncome:

Line Item Amount
Rent $10,000


subfrmExpense:

Line Item Amount
Roof Repair $1,000
Property Tax $2,000

Then when I run the query, the result will be around $23,000

because when I take out Group By, the query will return ( I bet you already all know this)

tblIncome.Amount tblExpense.Amount
10,000 1,000
10,000 2,000


so my question though is, how will I be able to calculate NOI that would give the result of $7,000?


Any help is greatly appreciated and thanks for the time in reading this lengthy post.

 

[dhookom]

I'm not sure why you have separate Income and Expense tables. These could all be in a Transaction table with a field that identifies Income or Expense.

If you don't want to or can't change, you would need to create a union query. I would first create an Income query with the revenue tables and then an Expense query with the expense tables. Finally build your union query with the two queries.



Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

hello dhookom...

I thought that separating Income and Expenses into different tables is the normalized way? In case you remember me and my previous posts (although I know you might not remember me out of the thousands of tek tips user you've been helping ) that I have always failed to normalize my tables, so this is a new database project we are working on, and I am trying to properly normalize it

If I should just create a transactions table which have there would be an identifier if the line items are Income or Expense, can you please share what you think that table structure should be? and how it could relate to tblRevLineItems, tblExpLineItems, tblMappingLegendRev and tblMappingLegendExp...

If I go to only having it into one table, I would assume that there will only be one subform needed then... will that be possible given that we could format it that it would look like a financial statement wherein all line items created for Income are grouped together on top and all line items created for Expense are grouped together at the bottom?

Thank you very much for reading my lengthy post...

 

[dhookom]

I recall helping you in the past.

You would combine tblRevLineItems and tblExpLineItems into tblLineItems. tblMappingLegendRev and tblMappingLegendExp would become tblMappningLegend.

Assuming a field for Rev or Exp, you could use two subforms if you want with link master text boxes for Rev and Exp.

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

Hello dhookom,

We had a meeting earlier, and our Senior Director insisted that we should have tblIncome and tblExpense separately. =(

So I am trying out your suggestion to create a query from tblIncome and tblExpenses, then create a Union Query...

I created these queries:

qryTotalIncome:

SELECT 
  tblIncome.JobTrackingID, Sum(tblIncome.Amount) AS SumOfAmount
FROM 
  tblIncome
GROUP BY 
  tblIncome.JobTrackingID;

qryTotalExpense

SELECT 
  tblExpenses.JobTrackingID, Sum(tblExpenses.Amount) AS SumOfAmount
FROM 
  tblExpenses
GROUP BY 
  tblExpenses.JobTrackingID;

when I run these queries, it gives the correct Total Income and Expenses... I tried to create a Union Query though, but I'm sure that this is not correct, and not sure how to create it that would give the desired result:

SELECT
   qryTotalIncome.Amount
FROM
   qryTotalIncome
UNION SELECT
   qryTotalExpense.Amount
FROM
   qryTotalExpense;

Any help is greatly appreciated.

Thanks

 

[dhookom]

I was thinking you would create an income and expense detail union query
The income/revenue base query [qselIncome] would look like (caution air sql):

SELECT "I" as IncExp, IncomeID as IncExpID, JobTrackingID,
  I.RevLineItemsID ItemsID, RevLineItems Item, I.RevMappingID MapID, 
  MappingLineItem MapLineItem, Amount, Adjustment
FROM tblIncome I JOIN II tblRevLineItems on I.RevLineItemsID = II.RevLineItemsID JOIN tblMappingLegendRev IL on I.RevMappingID = IL.RevMappingID

Do the same for the Expense and then create your detailed union query:

SELECT IncExp, IncExpID, JobTrackingID, ItemsID, Item, MapID, 
  MapLineItem, Amount, Adjustment
FROM qselIncome
UNION ALL
SELECT IncExp, IncExpID, JobTrackingID, ItemsID, Item, MapID, 
  MapLineItem, Amount, Adjustment
FROM qselExpense

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

hello,

I created these query, the air sql wasn't working, so I tried to pattern it with these queries:

qryIncome:

SELECT tblIncome.IncomeID, tblIncome.JobTrackingID, tblIncome.RevLineItemsID, tblIncome.RevMappingID, tblIncome.Amount, tblIncome.Adjustment
FROM tblRevLineItems INNER JOIN (tblMappingLegendRev INNER JOIN tblIncome ON tblMappingLegendRev.RevMappingID = tblIncome.RevMappingID) ON tblRevLineItems.RevLineItemsID = tblIncome.RevLineItemsID;

qryExpense:

SELECT tblExpenses.ExpenseID, tblExpenses.JobTrackingID, tblExpenses.ExpLineItemsID, tblExpenses.ExpMappingID, tblExpenses.Amount, tblExpenses.Adjustment
FROM tblMappingLegendExp INNER JOIN (tblExpLineItems INNER JOIN tblExpenses ON tblExpLineItems.ExpLineItemsID = tblExpenses.ExpLineItemsID) ON tblMappingLegendExp.ExpMappingID = tblExpenses.ExpMappingID;

then created the Union Query:

SELECT 
   IncomeID, JobTrackingID, RevLineItemsID, RevMappingID, Amount, Adjustment
FROM
   qryIncome
UNION ALL
SELECT
   ExpenseID, JobTrackingID, ExpLineItemsID, ExpMappingID, Amount, Adjustment
FROM
   qryExpense;

Not sure if this is correct though?

Thank you...

 

[dhookom]

You didn't include any fields from the related lineitem or mapping tables. I would have:
- included these extra fields
- aliased the columns to make them generic
- added the column "I" or "E" for either IncExp
as I suggested 28 Jan 11 10:08.

You might also need to multiply the Amount column by -1 for expenses depending on how you store the values.

This provides the greatest level of details with the extra columns.

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

hello dhookom,

I keep on staring at the query you suggested, the air code?

but I really am horrible at making aliases and adding the new fields... I keep on trying to replicate the air code to make it work, but my queries are not making sense?

I know it might be too much, but can you please write it for me so I could see how it should be properly written?

I really apologize for my ignorance.. =(

 

[dhookom]

Try

SELECT "I" as IncExp, IncomeID as IncExpID, I.JobTrackingID, 
 I.RevLineItemsID ItemsID, RevLineItems Item, I.RevMappingID MapID, 
  MappingLineItem MapLineItem, Amount, Adjustment
FROM tblRevLineItems II INNER JOIN (tblMappingLegendRev IL INNER JOIN tblIncome I ON IL.RevMappingID = I.RevMappingID) ON II.RevLineItemsID = I.RevLineItemsID;

qryExpense

SELECT "E" as IncExp, ExpenseID as IncExpID, E.JobTrackingID, 
 E.ExpLineItemsID ItemsID, ExpLineItems Item, E.ExpMappingID MapID, 
 MappingLineItem MapLineItem, Amount, Adjustment
FROM tblMappingLegendExp EL INNER JOIN (tblExpLineItems INNER JOIN tblExpenses ON tblExpLineItems.ExpLineItemsID = E.ExpLineItemsID) ON EL.ExpMappingID = E.ExpMappingID;

quniIncomeExpenseDetails:

SELECT IncExp, IncExpID, JobTrackingID, ItemsID, Item, MapID,
MapLineItem, AMount, Adjustment
FROM qryIncome
UNION ALL
SELECT IncExp, IncExpID, JobTrackingID, ItemsID, Item, MapID,
MapLineItem, AMount, Adjustment
FROM qryExpense;

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

hello,

I copied and pasted the first two queries and it gives syntax error(missing operator) in query expression 'E.ExpLineItemsID ItemsID'. I tried to put "as" in between E.ExpLineItemsID and ItemsID, and it gives other syntax error for ExpLineItems Item, E.ExpMappingID MapID, and when I put the "as" in between them, it gives the Syntax error in JOIN operation error.

thank you again,, I appreciate it

 

[dhookom]

qselExpenses

SELECT "E" as IncExp, ExpenseID as IncExpID, E.JobTrackingID, 
 E.ExpLineItemsID as ItemsID, ExpLineItems as Item,
 E.ExpMappingID as MapID, MappingLineItem as MapLineItem, 
 Amount, Adjustment
FROM tblMappingLegendExp EL INNER JOIN 
(tblExpLineItems EI INNER JOIN
 tblExpenses E ON EI.ExpLineItemsID = E.ExpLineItemsID)
 ON EL.ExpMappingID = E.ExpMappingID;

qselIncome

SELECT "I" as IncExp, IncomeID as IncExpID, I.JobTrackingID, 
 I.RevLineItemsID as ItemsID, RevLineItems as Item,
 I.RevMappingID MapID, MappingLineItem as MapLineItem,
 Amount, Adjustment
FROM tblRevLineItems II INNER JOIN 
(tblMappingLegendRev IL INNER JOIN
 tblIncome I ON IL.RevMappingID = I.RevMappingID)
 ON II.RevLineItemsID = I.RevLineItemsID;

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

SELECT "I" AS IncExp, IncomeID AS IncExpID, I.JobTrackingID
, I.RevLineItemsID AS ItemsID, RevLineItems AS Item, I.RevMappingID AS MapID
, MappingLineItem AS MapLineItem, Amount, Adjustment
FROM (tblIncome I
INNER JOIN tblRevLineItems II ON II.RevLineItemsID = I.RevLineItemsID)
INNER JOIN tblMappingLegendRev IL ON IL.RevMappingID = I.RevMappingID

SELECT "E" AS IncExp, ExpenseID AS IncExpID, E.JobTrackingID
, E.ExpLineItemsID AS ItemsID, ExpLineItems AS Item, E.ExpMappingID AS MapID
, MappingLineItem AS MapLineItem, Amount, Adjustment
FROM tblExpenses E
INNER JOIN (tblExpLineItems EI ON EI.ExpLineItemsID = E.ExpLineItemsID)
INNER JOIN tblMappingLegendExp EL ON EL.ExpMappingID = E.ExpMappingID

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

 

[iuianj07]

thanks both, I used dhookom's queries =) I added a field in both queries to get the Net Value:

Net Value: Amount + Adjustment

Do I now add the union query into frmFinancialSpread?

Now the union queries returns all line items and identifies with "I" or "E" for income and expenses...

I have been trying to figure out how to get the NOI (All Net Value of Income line items minus All Net Value of Expense Line items)

so what I did was I created these queries ( I really am not an expert to create simplified queries, so I always seem to take the longer path)

qryTotalIncome

SELECT qryuniIncomeExpenseDetails.IncExp, qryuniIncomeExpenseDetails.JobTrackingID, Sum(qryuniIncomeExpenseDetails.[Net Value]) AS [SumOfNet Value]
FROM qryuniIncomeExpenseDetails
GROUP BY qryuniIncomeExpenseDetails.IncExp, qryuniIncomeExpenseDetails.JobTrackingID
HAVING (((qryuniIncomeExpenseDetails.IncExp)="I"));

qryTotalExpenses

SELECT qryuniIncomeExpenseDetails.IncExp, qryuniIncomeExpenseDetails.JobTrackingID, Sum(qryuniIncomeExpenseDetails.[Net Value]) AS [SumOfNet Value]
FROM qryuniIncomeExpenseDetails
GROUP BY qryuniIncomeExpenseDetails.IncExp, qryuniIncomeExpenseDetails.JobTrackingID
HAVING (((qryuniIncomeExpenseDetails.IncExp)="E"));

qryNOI

SELECT qryTotalIncome.[SumOfNet Value] AS A, qryTotalExpenses.[SumOfNet Value] AS B, [A]-[B] AS NOI
FROM qryTotalExpenses, qryTotalIncome;

is this the right approach to calculate NOI? and if yes, how could I incorporate this to frmFinancialSpread??

if this steps I made was incorrect, please guide me to the right direction (as you have done hundreds of times already)

Thank you again...

 

[iuianj07]

I changed my qryNOI to:

SELECT qryTotalIncome.[SumOfNet Value] AS Income, qryTotalExpenses.[SumOfNet Value] AS Expenses, [Income]-[Expenses] AS NOI
FROM qryTotalExpenses INNER JOIN qryTotalIncome ON qryTotalExpenses.JobTrackingID = qryTotalIncome.JobTrackingID;

I forgot to put an Inner Join...

Thanks again

 

[iuianj07]

Hello again,

Okay, I created another subform subfrmNOI on frmFinancialSpread (via JobTrackingID) which is bound to qryNOI,

and it breaks down Total Income, Total Expenses and its NOI...

I think that is correct?

Do you see any issues that may arise or steps that I have missed?

Thanks

 

[iuianj07]

Hello all,

well I think no one would want to reply to me anymore, I hope it's because I had done it correctly and not to simply ignore my posts.. lol

anyway thanks to both of you for your help...

 

[dhookom]

Your qryNOI does not have a column for JobTrackingID so this can't happen "subform subfrmNOI on frmFinancialSpread (via JobTrackingID) which is bound to qryNOI"

I would probably not have created the qryTotalIncome and qryTotalExpenses queries that feed itno qryNOI. You should be able to create a totals or crosstab query directly from qryuniIncomeExpenseDetails that has the totals by JobTrackingID.

I'm not sure how you ended up with a field/column name with a space in it ([Net Value]). Why not NetValue so you don't have to worry about typing []s?

I also avoid using derived columns in other expressions in a query:

I would expect qryNOI to be joined on JobTrackingID like:

SELECT qryTotalIncome.[SumOfNet Value] AS Inc, 
qryTotalExpenses.[SumOfNet Value] AS Exp, 
qryTotalIncome.[SumOfNet Value]-qryTotalExpenses.[SumOfNet Value] AS NOI
FROM qryTotalExpenses INNER JOIN qryTotalIncome qryTotalExpenses.JobTrackingID = qryTotalIncome.JobTrackingID;

Duane
Hook'D on Access
MS Access MVP