Year End Sales Report in Microsoft Access. 2020 Year End Reporting. Aggregate Query w Monthly Totals

With 2020 coming to a close in a few days, this lesson will take a look at how you can generate your year-end sales reports. I'll show you how to create an aggregate query to group your sales together by year and month and total them up by month.

LEVEL: Expert

Jaime from Henderson, Nevada (a Gold Member) asks: With 2020 coming to a close soon, I need to put together my year-end sales report. I need to create a report with just sales from 1/1/2020 to 12/31/2020, showing the total sales for each month. It would be nice to not have to edit this report every year to change the dates so my secretary can just run it next year without me being involved. Can this be done?

BONUS FOR CHANNEL MEMBERS:
Silver Members and up get access to a 15 MINUTE EXTENDED CUT of this video which covers additional examples. That video will show you how to create a formatted printable report showing the month names and sales totals. We will put a text box on the main menu that will have the year in it so we don't have to keep typing it in. Lots more!

MEMBERS ONLY VIDEO:

BECOME A MEMBER:

LINKS:

ADDITIONAL RESOURCES:

WHAT DOES LEVEL MEAN:
My Access classes are divided into multiple levels. BEGINNER requires little working knowledge of Access. EXPERT assumes you know relationships and how to use functions. ADVANCED users know macros and events. DEVELOPER is full VBA programming.

KEYWORDS:
microsoft access, ms access, #msaccess, #microsoftaccess, 2020 Year End, 2021 Reporting, Year End Report, Year Function, Between Keyword, CDate Function, Parameter Query, Group By Month, Month Function, Date Functions, Year End Sales Report, End of Year Monthly Totals, Yearly End, Month End, Inequality, Inequalities

QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[minarendra]

Thanks Richard for sharing this. Its very useful.

[shafameen]

Cross tab queries are best for for this kind of reports- just an opinion. Great video Richard.

[ql]

Hello I live in the UK and I am new to databases I have an exam in January the 14th which is ICT level 3 BTEC unit 2 creating system to manage informations I am having a private tutor to help me with it but do you have any materials or videos that I could use because I struggle with calculated quieres, forms and reports

[JoshieDad]

Hi Richard, this was a great video! I learnt a lot! However, how do I use fiscal year instead of the traditional calendar year?? I tried using an IIF([OMonth]<6, [OYear]-1, [Oyear]) but it just doesn't work?? Have you got a video on that?

[alicataratas]

hi mr richard
If we have several invoices for a single client, each invoice with a different value, there is a way, WHEN THE CUSTOMER PAYS US A TOTAL SUM, TO AUTOMATICALLY DISTRIBUTE
between the invoices and the rest of the value is deducted from the remaining invoices

[ZulNorizan]

Hi richard, i love this aggregate query. I'm building a kinda same query like you show. But for case, i have a month that sometimes doesn't have any sales at all.

From your aggregate query how can i make the query show month that doesnt have any sales as 0 or null value? So the table will have a proper 12 months listed from Jan to Dec

[akocton]

Hi Richard, how to watch your extended cut year end report, im interested in this but checking it to members only idont see the video? waiting for you response

[xiixiixo]

I need to create a report with MTD information on the same report as YTD information. Is this possible?

[ehteshamshah]

Sir Will u help us how to send Balances to our customers via Text Messages on their Cell Phones