Sales Chart
By Richard Rost
4 years ago
Create a Monthly Sales Chart in Microsoft Access
In this Microsoft Access tutorial, I'm going to show you how to create a chart showing sales by month for the past year. We will then use Excel to forecast future sales over the next few months.
Tim from Edison, New Jersey (a Platinum Member) asks: Can you show us how to put a sales chart in our database to quickly see the past year's sales. If you could also show us how to forecast next month, that would be awesome too.
Members
Members will learn how to forecast next month's sales using a little VBA and some SQL.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Recommended Courses
Links
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, modern charts, sales projections, sales forecast, sales forecasting, excel chart, trendline, FORECAST.LINEAR function, growth projection
Subscribe to Sales Chart
Get notifications when this page is updated
Intro In this video, I will show you how to create a sales chart for the past year using modern charts in Microsoft Access. We'll cover how to prepare your data, build the necessary queries to summarize sales by month, and add a chart directly to your database forms for quick insights. I will also show you how to bring your summarized data into Microsoft Excel for basic sales forecasting using trendlines and worksheet functions. This tutorial will help you visualize your sales trends and understand the basics of forecasting in both Access and Excel.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com.
I am your instructor, Richard Rost. In today's video, I am going to show you how to chart your sales for the past year. We are going to show sales by month in Microsoft Access using modern charts. We will do a little bit of forecasting using Microsoft Excel.
Today's question comes from Tim in Edison, New Jersey, one of my platinum members. Tim asks, can you show us how to put a sales chart in our database to quickly see the past year's sales? If you can also show us how to forecast next month, that would be awesome too.
Well, Tim, I can definitely show you how to put that sales chart in a form in Microsoft Access. Forecasting in Access is a little trickier. Excel is actually much better for forecasting future sales, and I will show you a little bit of that at the end of this video. I will show you another trick in the extended cut on how to forecast next month's sales straight in Access.
So, let's do the sales chart first. Then we will get to some forecasting after that.
Some prerequisites before we get started: If you do not know what the DateSerial function is, go watch this video on birthdays, because we are going to have to do some manipulation with dates in this video. You should also know what an aggregate query is. That is where we can take a bunch of data and group it together, like all of one month's sales, for example. You should also know the Format property and functions. We are going to have to format our year and month a certain way.
If you do not know what any of these things are, go watch these three videos first. You will find links down below in the links section. You might have to click on a little "more" link if you are on YouTube. Go watch these first and come on back.
Here I am in my TechHelp free template. This is a free database. You can go download a copy off my website if you want to. Now, in this database we have the customer form, and we have got orders. Each order has order details, which are the line items, and we calculate the order total by adding all these up.
Normally, that is how you do it, but for today's class, just to keep things simple, we are going to ignore order detail. In fact, I am going to delete it, and we are just going to use the order table. I am going to come in here and get rid of this stuff that I do not need. We do not need a customer ID, and we do not need this stuff down here. All right, we are just going to have an order date and an order total. That is all, just to keep things simple.
Let's close this and put some data in it. I am going to put in here a bunch of sales over the past year.
For those of you wondering, I am using the ISO date format. That goes year, month, day. It is a generic format so that whether you are in the United States or in the United Kingdom, your date format is always the same: year, month, day. There is a video on it if you want to go learn more.
Let's type in some orders. I am going to start with this year. I am going to go 1-10. That will go this year. We will put in $115, and then we will go 1-15 and maybe $250, and then have about $130 and $15. Then, we will do next month, 2-15, which is $144. I am just going to type in a year's worth of sales. I am going to go back, maybe, to May of last year.
It is currently June of 2022, so I am going to go back to maybe May of last year. I am not going to make you sit here and watch me type all these things. I am going to pause, and then it is going to be an instant for you. Ready, go.
And bam, I am done. See? I like the flash. You did not even see it. That is how quick I am.
So, I did 2022. I did January, February, through to June. Then, I went to last year, so we have a whole year's worth of sales going back. The reason why I put a couple for January in here is so you can see how the aggregate works when we group these together as a single month, but for the rest of them, I do not care.
There is my sales. Do something similar. Your numbers do not have to be exact. Do a year's worth of sales going back.
Now, let's make a query to show us just the sales for the past year, but I want whole months, because if I am going to chart this, I do not want to just go back 365 days or back a calendar year. Because today, when I am recording this, it is June 20th, so if I go back a calendar year, I am going to get a piece of last June. I do not want that. I want to go back a year, but I want to go back to the first of the month of whatever month was 12 months ago.
You do not want it because it will misrepresent your monthly sales if you do not get a whole month. So we need a whole month.
Go to Query Design. We do not need all this stuff here. I am going to bring in my order table and close that. We are going to bring in the order date and the order total. Now, for the order date, I am going to say, let's sort this ascending, and then for the criteria, I am going to zoom in (Shift+F2) so you can see it better.
I am going to put in: greater than or equal to DateSerial. Remember DateSerial, where you can specify the three parts of a date field: year, month, date in that order. I want last year. So, it is going to be the year of today's date minus one. So if it is currently 2022, that is going to put 2021 in there. Comma. Now, I want the same month that we are in from last year. So if it is currently June of 2022, I want to see June of 2021. So, that is going to be the month of Date. That will put a 6 in there, for example. Then, I want the first day of the month. So, comma 1. There is your way to get the first day of whatever month it was 12 months ago.
Click OK. If I run this, there are your dates. You can see that (oh, this is just a brand new one on the bottom, ignore that), you can see the last one we are seeing is 6.1 from last year. If you look in the order table, you will see I intentionally put one in there from 5.2, so we are not seeing that one. These are actually backwards; I typed these in a different order. So this is the most current one here: 6-4. The oldest one here is 6-4, and that is right there. I should not see 5-2 from 2021 because of my criteria.
All set. Good. Let's save this as OrderPastYearQ (I keep everything singular). That is just my own personal rule because it messes me up if I do not. Then I've got to go say, was it OrdersLastYear or Order? I do not remember, so I just keep everything singular.
Now we want to create an aggregate query to group this stuff together by month, so all of, for example, this stuff is grouped together. Let's close this one. You cannot do them both in the same query. When you put your criteria on like that, if you try to aggregate it, sometimes things get messed up, so it is better just to make two queries.
We are going to make another query: Create - Query Design. Bring in your OrderPastYearQ. Now I want the order date over here, but I do not want the whole date. I do not need the days; I just want the month part. So, I am going to create a calculated field. Got a video on calculated fields too. If you watched the aggregate query one, you would know about the calculated field. If you watched those other videos, then you should know this one, but here is another video if you want to go watch this if you do not know what these are.
I want just the year and month here together, so I want like 2022-01. We are going to call this OrderMonth. (Zoom in for you.) OrderMonth is going to be Format. Use the Format function. So I want you to watch it: Format([OrderDate], "yyyy-mm"). That is all you need now. This will be a string value. It is not going to be a date value anymore, but that is okay. It is only for the chart. It is just going to be displayed on a chart, and the other query already handled the date part of it, which is the criteria to limit the records. Now we just want to format it in the display method, the way we want to display it on the chart. We want that.
Then we also need the order total. If I run it right now, it is going to look like this. Now I can group these together with an aggregate. Turn the aggregates on. Group by that and we are going to sum up this. Run it now. Look. All those are grouped together in that part.
Let's save this one as OrderPastYearAggQ. I use "AggQ" to help me know aggregate query. That way I know what is an aggregate. Just be consistent.
Now that we have got this, we can make our charts! You can make a brand new chart form if you want to. I am just going to throw it on the main menu. That is fine. Design view. Let's put it out over here. Now, form design. There is the old chart guy, which is right there. That is the old chart. That has been around since Access 97. That is super old. It is like over 20-some years old, and it looks it. It was never updated that often, but then there are the new modern charts, which are over here. These are nicer. They are great, but they still need some work.
One of my pet peeves at Microsoft is they give all kinds of love to Excel, and Access is the redheaded stepchild. So these need work. In fact, I really have not covered these exhaustively in my full course yet, my regular course, just because I am waiting for Microsoft to finish them. Please, Microsoft, finish the modern charts. There are so many features you need to add. They are okay; you can make a simple, basic chart. Honestly, for Access, that is usually all you want - something simple. If you want to do some crazy, professional-level charting, usually I take my data over to Excel and chart it there. Access is for data storage; Excel is more for analyzing, but we can make some good charts in here. I think I did a pie chart in a previous class. We will go to column. We will just do a simple clustered column. Draw a box right here where you want the chart to go. I mean, these are not bad. They just need some love.
I am going to slide this over so we can see the chart as we are building it. Here are our chart settings: where you get your data from, your data source. Go to Queries, drop that down, and pick the OrderPastYearAggQ. And you are pretty much done. If you want to be done now, that is fine.
Your axis, your legend - we do not need a legend. You want to change the format? Come over here; you can change, you know, you can put a trendline on if you want to. You can give it a name. I am going to turn that off. I do not really like that. Well, let's leave it on for now. Let's leave it on. You can change the color. You want to change color over here. Make your sales dark blue.
Now, this is the chart setting for the chart control and the modern stuff, but if you close that and then double-click on this chart, it will bring up the property sheet for the chart. There is even more stuff in here. Very confusing, Microsoft. Very confusing. You should be able to have one interface to update this whole thing. So it is Chart16 right now. I do not like that. Let's call it myChart.
There are a million options in here. I encourage you to come in here and play with some of these options. You can change the colors; if there is a legend (like I am going to turn the legend off), we do not need the legend. What do you want the title to be? Has title: yes. Chart title: Sales for Previous Year. Whatever you want it to be.
There is a whole bunch of options here. I am not going to go through all of them right now. There are a lot of them, and yes, eventually, when Microsoft finishes modern charts, I will have a whole class on them. I cover them briefly in my regular course.
There you go. As you can see, right, this little guy over here is not finished because we have a little tiny bit of data there. In the extended cut, I am going to show you members how to forecast what that is going to be and put a bar in there.
We will do that in a few minutes, but for the rest of you, let's save this and close it and open it back up again. I have to save it - save, Control+S, close it, open it back up again.
There we go, and it looks pretty good for a quick at-a-glance view of what my sales look like. That looks nice, and that is what the Access modern charts are good for, just real quick summary data.
But if you want a good forecast, you want to go out like three periods, take this data and bring it over to Excel.
The data is already in this query right here. Just double-click on that, open it up, click up here, copy everything (Control+C), it is in the clipboard. Let's go over to Excel. Chances are if you have Access on your system, you have Excel too.
Here I am in Excel. Click right here, paste. There is my data. Pretty straightforward. Now, drop a chart in. I am going to select all this. I am going to go to Insert, and then there are Recommended Charts. I am just going to stick with the same kind of 2D chart, like this guy. Or, these are a little bit nicer looking. Let's go with one of these. Click a 3D Cluster chart like that, and boom, you are done. See?
If you do not want this last month skewing your results, just delete it over here. Just highlight that row, right-click, delete. Boom, gone. That fixes it. You do not want to put up an incomplete row of data there, because it will throw your forecasting off this month here.
What did I do there? I was trying to get it smooth. Let's cheat. Let's say 2022-01 is not that high. Let's go $95. That looks a lot better. Let's try to do it. I do not know what happened.
Now, you can right-click in here and go to (well, right-click in here...) Oh, I cannot get in a trendline. Could be the chart type. Let's change it: right-click, Change Chart Type. Let's change it to a standard 2D Cluster Column, and now, yep, now we can add a trendline. It has been a while.
There is our trendline. Usually, linear is what you do for sales projections. If you scroll down, you can forecast it forward a number of periods, like three periods. That will give you roughly where your sales should be. I would not go too much further than three periods, though. The people on Shark Tank: "Well, we did a half million dollars last year. We are projecting $30 million next year." No. Where do you get that from?
If you do want to forecast over here numerically, there is a FORECAST function. In order for that to work, you have to replace these with actual dates. So, let's get rid of these, and we want to start with 21/06. So, these have to be actual dates. 21/6/1, 21/7/1, and then we can autofill that down like so. It looks roughly the same in here.
Now, we can use the FORECAST function to forecast what these numbers should be over here. So: =FORECAST.LINEAR( x, known_ys, known_xs ). X is your data point. That, comma, your known ys is this stuff, comma, your known xs is this stuff. Then enter, and there is your forecast.
You can click and drag that down. We probably should have used absolute references for the front part, so this A2 right here, we hit F4, that locks that one, and on the B2 as well. It will lock that in place, so the top cell always stays on that spot on the top, and now when I drag it down, it should be more accurate now. There you go. See? It does not change the top of the range. That is called an absolute reference. I cover that in my course.
Let me just format it as currency. Where is my Format Painter? There you go, and there is your forecast. If you want to extend the range of the chart, you can by doing this.
There you go. It is linear, pretty straightforward. That is where your linear sales chart should be, right along that trendline.
Can you do this in Access? Yeah, you can. It requires some VB, and that is what I am going to show in the extended cut for the members.
All right, members, so we have got this is what our chart looks like right now with a little tiny bit of June sales in there. I am going to show you how to forecast that out and display it like that. We are only going to do one month. Can you do more? Sure. You can do a loop, and it will... it is a lot more extensive, but I am going to show you the basics, and you can take it from there and make more. But that will be in the extended cut for the members.
Silver members and up get access to all of my extended cut videos. Gold members can download these databases and have access to my code vault with all my cool VB code in it. If you want to become a member right now, click that join button. Join us! All kinds of cool stuff in my forums. Plus, you get a free class every month. Silver members and up get a free class once a month, so that is a good perk too. So, what are you waiting for? Join right now. Hope you learned something. We will see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will get one free expert class each month after you finish the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website. Not just for Access, too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond Sponsor and have your name or company name listed on a sponsors page. It will be shown in each video as long as you are a sponsor. You will get a shoutout in the video and a link to your website or product in the text below the video and on my website.
But do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more, and they will always be free.Quiz Q1. What is the primary goal of this video tutorial?
A. To chart sales by month over the past year using Microsoft Access, and demonstrate basic forecasting in Excel
B. To build an entire inventory management system in Access
C. To create customer relationship forms in Access
D. To design pivot tables in Excel
Q2. Which Microsoft Access feature is specifically recommended for forecasting future sales in this tutorial?
A. Modern chart control
B. Old chart wizard from Access 97
C. Query Design only
D. Microsoft Excel
Q3. What are the prerequisites mentioned before starting the tutorial?
A. Understanding DateSerial, aggregate queries, and format functions/properties
B. Knowing how to use SQL Server
C. Experience with PowerPoint charts
D. Familiarity with VBA class objects
Q4. Why is it important to include whole months, rather than the last 365 days, in the sales summary for the chart?
A. To ensure monthly sales are accurately represented by grouping complete months
B. To save space in the query results
C. Because Access cannot process less than a month of data
D. So that the order table is not overloaded with data
Q5. What is the purpose of the OrderPastYearQ query in the tutorial?
A. To filter the order table to include only records from the past year, starting from the first of the month 12 months ago
B. To join the customer and order tables
C. To sort orders alphabetically
D. To calculate customer discounts
Q6. Why does the tutorial use a second aggregate query (OrderPastYearAggQ) after filtering data with OrderPastYearQ?
A. To group sales data by year and month and sum the order totals for charting
B. To display customer addresses
C. To convert sales amounts into percentages
D. To find duplicate records
Q7. What function is used to create the calculated field OrderMonth for grouping data?
A. Format([OrderDate], "yyyy-mm")
B. Len([OrderDate])
C. DateDiff([OrderDate])
D. Mid([OrderDate], "yyyy-mm")
Q8. When building a chart in Access, which type of chart is recommended as more modern and visually appealing?
A. Modern charts
B. Microsoft Graph (Access 97) charts
C. Pie charts only
D. Excel embedded charts
Q9. How is data transferred from Access to Excel for further analysis and forecasting in this tutorial?
A. By copying query results in Access and pasting them into Excel
B. By exporting a PDF from Access
C. By using a VBA script
D. By importing an Excel file into Access
Q10. What Excel feature is suggested for forecasting sales trends beyond just visualizing current data?
A. Adding a trendline to the chart and using the FORECAST.LINEAR function
B. Creating a new Excel workbook for each month
C. Using conditional formatting only
D. Inserting a pivot table
Q11. What must be done to the date values in Excel before using the FORECAST.LINEAR function?
A. Convert them to actual date values
B. Change them to text strings
C. Sort them alphabetically
D. Encrypt them with a password
Q12. What is the benefit of using absolute references (e.g., $A$2) in Excel formulas when dragging them down?
A. To prevent the range from shifting as the formula is copied to additional rows
B. To hide formulas from users
C. To change all formatting to currency
D. To ensure the chart type is always 3D
Q13. According to the tutorial, why might someone choose to use Excel for charting or forecasting even if data is stored in Access?
A. Excel offers more advanced and flexible charting and forecasting features
B. Access cannot store any sales data
C. Excel is required for all database reporting
D. Access cannot sum sales totals
Q14. What membership perks are mentioned for Silver members and above?
A. Access to extended cut TechHelp videos and one free beginner class each month
B. Only being able to ask basic questions
C. Free Excel license
D. Lifetime phone support
Q15. What additional benefit do Gold members receive compared to Silver members?
A. They can download sample databases and access the Code Vault
B. They receive twice as many emails
C. They get free Microsoft Office software
D. They are allowed to take more courses on Mondays
Q16. What is one key reason given for not using incomplete months in the forecasted chart data?
A. Including incomplete months can skew the results and misrepresent monthly trends
B. Microsoft Access cannot query incomplete data
C. Customers will not trust the data
D. It increases the file size excessively
Answers: 1-A; 2-D; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone covers how you can create a sales chart that shows your sales by month for the past year using modern charting features in Microsoft Access. I will also touch briefly on forecasting next month's sales, primarily using Microsoft Excel, since Excel is much better suited for that task.
This topic was inspired by a question from one of my platinum members, who wanted a way to quickly visualize the past year's sales and also wanted to see how to forecast future sales.
First, let's go through making a sales chart within Access. While Access itself can display the data visually, it's worth noting that its forecasting capabilities are somewhat limited compared to Excel, which I will use to demonstrate a bit of forecasting.
Before you get started, there are a few foundational concepts that you should understand. You will need to be familiar with the DateSerial function for working with dates, aggregate queries for grouping your data (such as grouping all the sales within one month together), and the Format property and functions to properly display your year and month data. If these concepts are new to you, I recommend watching the supporting videos listed on my website and coming back once you have a handle on these topics.
In my example, I use the TechHelp free template database, which you can download from my website. In this template, there is a customer form, an orders table, and order details that normally track each item's order. For the sake of keeping this demonstration straightforward, I removed the details and focused only on the core orders table. This way, you only have to work with order dates and order totals.
When entering dates, I use the ISO date format (year-month-day), which is universal and avoids confusion across different country settings. I entered sales data spanning the previous twelve months, going back to May of the year before, so that we have a complete year's worth of sales for our demonstration.
When charting sales over a year, it's important to display complete months rather than just counting back 365 days. For example, if today is June 20, you should start your annual summary from the first of the month twelve months ago, not simply from June 20 last year, to ensure that your monthly groupings make sense and are accurate.
To do this, create a query that pulls sales orders where the order date is greater than or equal to the first day of the month, twelve months prior. This uses the DateSerial function to calculate the proper starting point. When you run the query, it should only pull in full months, avoiding partial months that could misrepresent your data.
After saving this query, I suggest naming it something consistent, such as OrderPastYearQ, using singular names for tables and queries to avoid confusion.
Next, you'll want to create an aggregate query to group your data by month. To do this, create a calculated field that combines the year and month (for example, "2022-01") using the Format function. This is purely for display purposes and not needed for filtering, as the previous query already took care of that. By enabling aggregation, you can sum the sales totals for each of these monthly groups. Save this query as something like OrderPastYearAggQ so you clearly know which is your aggregate query.
With your aggregate query ready, you can now add a chart to your Access database. You can either create a new chart form or add a chart to an existing form, such as your main menu. Access includes both legacy charts and modern charts; I recommend using the modern charts, which have improved formatting and are easier to work with, even though they still have some limitations and room for growth.
Set the chart's data source to your aggregate query, choose the layout and colors you like, and set the appropriate properties such as the chart title and legend. You may want to rename the chart control for clarity. There are many customizable options, so I encourage you to experiment and see what works best for your needs. Modern charts are excellent for creating simple, quick visualizations of your data directly in Access.
For more advanced forecasting, however, Access is not ideal. If you want to see trends or project future sales, I recommend exporting your aggregated data to Excel. You can simply copy the data from your Access query and paste it into an Excel worksheet. Excel's charting tools allow you to insert a chart that matches the one you built in Access, and you can go further by adding trendlines and using Excel's built-in forecasting features.
To forecast future sales, you can add a trendline to your chart (usually a linear trendline works well for sales projections) and extend that forecast by several periods, such as three months out. Excel also includes the FORECAST function, which allows you to estimate future values based on your historical data. For this, you need to enter your dates as actual date values, and use the function structure to predict upcoming sales. Remember to use absolute references in your formulas so that your calculations remain accurate when dragged across rows. Formatting your results as currency will give you a clear, professional look.
While it is possible to perform forecasting in Access using VBA, it's more complex. I cover this technique in detail for my members in the extended cut, showing you how to make sales projections and reflect them visually in your Access chart. Members will learn the method for forecasting next month's sales in Access, and with some modification it's possible to extend this to a longer range using code.
For those interested in becoming a member, there are several levels with increasing benefits. Silver members receive access to all extended cut TechHelp videos and a free beginner class each month. Gold members can also download all of my sample databases and gain access to my Code Vault, along with a free expert class each month and higher support priority. Platinum members receive the benefits of all previous levels, plus full access to all my beginner courses and a free developer class each month.
These free TechHelp videos will continue to be available, so you can keep coming back for more lessons.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Setting up an order table for sales data input
Entering sample sales records using ISO date format
Creating a query to filter sales for the past year
Using DateSerial to get the first day of the month one year ago
Saving and naming queries with a consistent convention
Building an aggregate query to group sales by month
Formatting dates as year-month text with the Format function
Summing monthly sales for aggregation
Creating a clustered column chart using Access modern charts
Assigning query data sources to modern chart controls
Customizing chart appearance, titles, and legends
Displaying sales data visually on an Access form
Copying query results from Access to Excel
Creating column charts in Excel from Access data
Removing incomplete data rows before charting
Adding and configuring a trendline for forecasting in Excel
Adjusting chart types to enable trendline features
Using the FORECAST.LINEAR function in Excel
Converting text-based year-month to date values in Excel
Applying absolute references in Excel formulas
Formatting forecasted sales results in Excel |
