Calculation in Access Query to Change Based on Dates

Question

I am trying to run a query that will calculate "TOTAL COST". The current code I have is:

SELECT [Main Query].SaltInfo_ID, Sum([Main Query].Tons) AS [Total Of Tons], Sum([Main Query].BlastTons) AS [Total Of BlastTons], Sum([Main Query].LiqGal) AS [Total Of LiqGal], Sum([Main Query].LiqBlastGal) AS [Total Of LiqBlastGal], ((Nz([Total Of Tons])+Nz([Total of BlastTons]))*66.63)+((Nz([Total Of LiqGal])+Nz([Total of LiqBlastGal]))*0.182) AS [TOTAL COST]
FROM [Main Query]
WHERE ((([Main Query].EventDate) Is Not Null))
GROUP BY [Main Query].SaltInfo_ID;

This works just fine; however I need to change the calculated [TOTAL COST] to calculate values based on the [EventDate]. If the event date was before 7/31/2016, the Tons and BlastTons cost is $66.63 and the LiqGal and LiqBlastGal is $0.182. After 7/31/2016, the cost changed to $50.40 and $0.1594 respectively. Can you please help with the proper coding so that this will calculate properly? I have tried this, and it didn't work:

SELECT [Main Query].SaltInfo_ID, Sum([Main Query].Tons) AS [Total Of Tons], Sum([Main Query].BlastTons) AS [Total Of BlastTons], Sum([Main Query].LiqGal) AS [Total Of LiqGal], Sum([Main Query].LiqBlastGal) AS [Total Of LiqBlastGal], IIf([Main Query].[EventDate]<#7/31/2016#,((Nz([Total Of Tons],0)+Nz([Total of BlastTons],0))*66.63)+((Nz([Total Of LiqGal],0)+Nz([Total of LiqBlastGal],0))*0.182),((Nz([Total Of Tons],0)+Nz([Total of BlastTons],0))*50.4)+((Nz([Total Of LiqGal],0)+Nz([Total of LiqBlastGal],0))*0.1594)) AS [TOTAL COST]
FROM [Main Query]
WHERE ((([Main Query].EventDate) Is Not Null))
GROUP BY [Main Query].SaltInfo_ID;

When I try to run the query, I get the error: "You tried to execute a query that does not include the specified expression 'IIf([Main Query].[EventDate]<#7/31/2016#,((Nz([Total Of Tons],0)+Nz([Total of BlastTons],0))*66.63)+((Nz([Total Of LiqGal],0)+Nz([Total of LiqBlastGal],0))*0.182),((Nz([Total Of Tons],0)+Nz([Total of BlastTons],0))*50.4)+((Nz([Total Of LiqGal],0)+Nz([Total of Li' as part of an aggregate function."

What I am doing wrong??? Any help would be greatly appreciated!

Can you try this query: "SELECT SaltInfo_ID, Sum(Tons) AS [Total Of Tons], Sum(BlastTons) AS [Total Of BlastTons], Sum(LiqGal) AS [Total Of LiqGal], Sum(LiqBlastGal) AS [Total Of LiqBlastGal], Sum(IIf(EventDate<#7/31/2016#, ((Nz(Tons,0)+Nz(BlastTons,0))*66.63)+((Nz(LiqGal,0)+Nz(LiqBlastGal,0))*0.182), ((Nz(Tons,0)+Nz(BlastTons,0))*50.4)+((Nz(LiqGal,0)+Nz(LiqBlastGal,0))*0.1594))) AS [TOTAL COST] From [Main Query] WHERE EventDate Is Not Null GROUP BY SaltInfo_ID;" — Velid
The error message is pretty clear: You also need to group by on that expression. — Gustav
Velid - I tried your query and it worked perfectly. Exactly what I needed it to do - thank you! — user7380439

Brad Brad · Accepted Answer · 2017-01-05T19:01:56

A query is not the place to put this sort of logic. You need another table to hold your pricing:

create table ProductCosts (Product varchar(255), StartDate datetime, EndDate DateTime, Cost Currency)

insert into ProductCosts(Product, Cost, StartDate,EndDate)
Select 'BlastTons', 66.63, '1/1/1990','7/31/2016'
union select 'LiqGal', 0.182, '1/1/1990','7/31/2016'
union select 'LiqBlastGal', 0.182,'1/1/1990','7/31/2016'
union Select 'BlastTons', 50.40, '8/1/2016','1/1/2050'
union select 'LiqGal', 0.1594, '8/1/2016','1/1/2050'
union select 'LiqBlastGal', 0.1594, '8/1/2016','1/1/2050'

Then your final query can be like this:

SELECT [Main Query].SaltInfo_ID, Sum([Main Query].Tons) AS [Total Of Tons]
    , Sum([Main Query].BlastTons) AS [Total Of BlastTons]
    , Sum([Main Query].LiqGal) AS [Total Of LiqGal]
    , Sum([Main Query].LiqBlastGal) AS [Total Of LiqBlastGal]
    , ((Nz([Total Of Tons])+Nz([Total of BlastTons]))*BlastTons.Cost)+((Nz([Total Of LiqGal])*LiqGal.Cost + Nz([Total of LiqBlastGal]))*LiqBlastGal.Cost) AS [TOTAL COST]
FROM [Main Query] mq
inner join ProductCosts BlastTons on BlastTons.StartDate <= mq.[EventDate] and BlastTons.EndDate > mq.[EventDate] and BlastTons.Product = 'BlastTons'
inner join ProductCosts LiqGal on BlastTons.StartDate <= mq.[EventDate] and BlastTons.EndDate > mq.[EventDate] and BlastTons.Product = 'LiqGal'
inner join ProductCosts LiqBlastGal on BlastTons.StartDate <= mq.[EventDate] and BlastTons.EndDate > mq.[EventDate] and BlastTons.Product = 'LiqBlastGal'
WHERE ((([Main Query].EventDate) Is Not Null))
GROUP BY [Main Query].SaltInfo_ID;

Ideally there is not a join for each type of product but that would involve you changing your Main Query first.

Calculation in Access Query to Change Based on Dates

1 Answers