Show Financial Quarter no calender

[ali32j]

Hi All

I have the below sql, but at present it has calender year as quarter, ie, Jan, Feb, Mar as Q1, what i need is year to start in april, so Q1 would be Apr, May, Jun, etc

SELECT DISTINCTROW StrategyMonthly.Name, Format$(StrategyMonthly.Date,'\Qq yyyy') AS [Date By Quarter], Sum(StrategyMonthly.CostLDE) AS CostLDE, Sum(StrategyMonthly.ValueLDE) AS ValueLDE, Sum(StrategyMonthly.CostAll) AS CostAll, Sum(StrategyMonthly.ValueAll) AS ValueAll, (([valueLDE]-[CostLde])/[Valuelde]) AS GMLDE, (([valueall]-[costall])/[valueall]) AS GMAll, [ValueLDE]/[valueall] AS LDESales, StrategyMonthly.WebSales
FROM StrategyMonthly
GROUP BY StrategyMonthly.Name, Format$(StrategyMonthly.Date,'\Qq yyyy'), (([valueall]-[costall])/[valueall]), [ValueLDE]/[valueall], StrategyMonthly.WebSales, Year(StrategyMonthly.Date)*4+DatePart('q',StrategyMonthly.Date)-1
ORDER BY Format$(StrategyMonthly.Date,'\Qq yyyy');



Is it possible to do this

 

[dhookom]

Add 3 months to the date to get the quarter.

 Format(DateAdd("m",3,StrategyMonthly.Date),'\Qq yyyy') AS [Date By Quarter],

You may need to split the quarter and year depending on how you want this to appear:

 Format(DateAdd("m",3,StrategyMonthly.Date),'\Qq') & Format(StrategyMonthly.Date,'yyyy') AS [Date By Quarter],

BTW: Date should never be used as a field name since it is the name of a function.


Duane
Hook'D on Access
MS Access MVP

 

[ali32j]

Hi Dhookom

Sorry but need a little more info, where do place this code? sql is nt my strong point, and what i ve tried does nt seem to work (ie replacing un select distinct)

Would appreciate a little more if you could help

Ali

 

[dhookom]

You had this in your sql:

 Format$(StrategyMonthly.Date,'\Qq yyyy') AS [Date By Quarter]

Replace your expression with my suggestion to see if it works.

Duane
Hook'D on Access
MS Access MVP

 

[ali32j]

Hi

I get the following:

You tried to execute a query that does not include the specified expression'Format(DateAdd("m",3,StrategyMonthly.Date),'\Qq yyyy') as part of an aggregate function

Ali

 

[ali32j]

Hi Hookom

Sorry figured it out needed to change the expression in groupby and orderby too, works great thank you

Ali

 

[PHV]

You [!]OBVIOUSLY[/!] had to replace Format$(StrategyMonthly.Date,'\Qq yyyy') [!]THREE[/!] times:
1) in the select
2) in the group by
3) in the order by

 

[dhookom]

My assumption was that Ali would change the query in design view rather than SQL view. I think this would have updated all three occurances of the expression.

Duane
Hook'D on Access
MS Access MVP