Forum Discussion
DSum conditional expression in Query
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
Query: I am trying to query the Projects (unique lines), along with a custom expression to sum the income and expense in separate columns
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
Hi George_Hepworth,
That worked, many thanks. You gave me the spark to ignite the fire in me 🙂
Until next time.
Regards,
Frank
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
Hi George_Hepworth,
That worked, many thanks. You gave me the spark to ignite the fire in me 🙂
Until next time.
Regards,
Frank
