SOLVED

DSum conditional expression in Query

New Contributor

Hi Everyone,

I am fairly new to MS Access, stuck with below challenge with framing an expression. Please help.

 

Table: SiteLogT

Contains income/expense of a project datewise

SiteLogTable.png

 

Query: I am trying to query the Projects (unique lines), along with a custom expression to sum the income and expense in separate columns

PortfolioQ.png

Currently the query results the sum of total amount, with respect to Income/Expense, but not corresponding to the Project.

 

I know my expressions are only considering the condition whether it is income/expense but not corresponding to the project ID associated with it. Please help me reframe my expression to result only the income/expense subjective to the unique project IDs.

 

my current Expression:

Income: format(DSum("Amount","SiteLogT","LogType='Income'"),"₹00,000.00")

Expense: format(DSum("Amount","SiteLogT","LogType='Expense'"),"₹00,000.00")

 

Many thanks,

Frank

2 Replies

@FranklinVaz 

If SiteLogT is the name of the table in question (as it appears in the screenshot),

then you can try this:

 

SELECT ProjID, Sum(Iif([LogType] = "Income", [Amount],0) AS IncomePerProject, Sum(Iif([LogType] = "Expense", [Amount],0) as ExpensePerProject

FROM SiteLogT

Group By ProjID

best response confirmed by FranklinVaz (New Contributor)
Solution

Hi @George Hepworth,

 

That worked, many thanks. You gave me the spark to ignite the fire in me :)

Until next time.

 

Regards,

Frank