Ms Access, group by specific date
Multi tool use
Ms Access, group by specific date
Is it possible to group by specific date in ms access? I would like to create a monthly report which will bring me the expenses/income from my database. However, I want this report to group the results NOT BY YEAR (1/1-31/12) but from 1/9-31/8 (my business year is from September-August). Is it possible to group the results the way I want?
Yes, correct. I want to group by fiscal year.
– Manolis Efstratiou
Jul 2 at 19:51
1 Answer
1
You can try:
Select
Format(DateAdd("m", 4, YourDateField), "yyyymm") As FiscalYearMonth,
<other fields>
From
YourTable
Group By
Format(DateAdd("m", 4, YourDateField), "yyyymm")
For your specific table, it could be something like:
Select
Description,
Format(DateAdd("m", 4, [YourDateField]), "yyyymm") As FiscalYearMonth,
Sum([Sum]) AS [Sum]
From
[MyTable]
Where
[MyTable].Category = "Misc"
Group By
[MyTable].Description,
Format(DateAdd("m", 4, YourDateField), "yyyymm")
Can you please tell me where exactly I should insert this code? My access SQL looks like:<br/>Select [MyTable].Description, Sum([My.Table].Sum) AS Sum<br>FROM [MyTable]<br/>WHERE (([MyTable].Category)="Misc"))<br/>GROUP BY [MyTable].Description;
– Manolis Efstratiou
Jul 2 at 20:08
Yes. See edited answer, please.
– Gustav
Jul 3 at 7:42
Thank you very much! Worked great!
– Manolis Efstratiou
Jul 3 at 14:17
Great! Then please mark as answered.
– Gustav
Jul 3 at 14:35
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
To clarify, you're looking to group all income/expense per fiscal year (or business year) where the fiscal year starts on September 1st. Correct?
– STLDeveloper
Jul 1 at 18:50