Regrouping Sales numbers per Year + Combo Graph
- Thread starter ggooss
- Start date
2 criticism points...
1. [Month] is a poor field name because it is a reserved word (https://docs.microsoft.com/en-us/office/troubleshoot/access/reserved-words). You should not name fields/objects with words on that list--makes writing code and qeury just a little more difficult.
2. Dates should be stored with a Date/Time field. Even though those are just month/years, you should think about changing it to a Date/Time and then just storing the first date of the month/year (e.g. 2/1/2021, 3/1/2021, etc.). Doing that allows you to use the built-in date functions of Access (https://www.techonthenet.com/access/functions/index.php) which would make this easier.
And 2 constructive points..
1. Use the Mid() function (https://www.techonthenet.com/access/functions/string/mid.php) to split out your [Month] field into month and year in a query.
2. Then convert that query to a crosstab(https://support.microsoft.com/en-us...ab-query-8465b89c-2ff2-4cc8-ba60-2cd8484667e8). That will let you convert the Year values to column names and put values beneath them.
1. [Month] is a poor field name because it is a reserved word (https://docs.microsoft.com/en-us/office/troubleshoot/access/reserved-words). You should not name fields/objects with words on that list--makes writing code and qeury just a little more difficult.
2. Dates should be stored with a Date/Time field. Even though those are just month/years, you should think about changing it to a Date/Time and then just storing the first date of the month/year (e.g. 2/1/2021, 3/1/2021, etc.). Doing that allows you to use the built-in date functions of Access (https://www.techonthenet.com/access/functions/index.php) which would make this easier.
And 2 constructive points..
1. Use the Mid() function (https://www.techonthenet.com/access/functions/string/mid.php) to split out your [Month] field into month and year in a query.
2. Then convert that query to a crosstab(https://support.microsoft.com/en-us...ab-query-8465b89c-2ff2-4cc8-ba60-2cd8484667e8). That will let you convert the Year values to column names and put values beneath them.
ggooss
New member
- Local time
- Today, 02:28
- Joined
- Nov 19, 2020
- Messages
- 10
Thanks a lot all. The crosstab query is working.
Now i was doing this also so i could create a combo graph.
What would be the trick in order to allocate last year's sales to the Primary axis and current sales to Secondary axis please?
So you can visually see the difference in sales for each month.
So it shows like that:
Thanks,
Greg
Now i was doing this also so i could create a combo graph.
What would be the trick in order to allocate last year's sales to the Primary axis and current sales to Secondary axis please?
So you can visually see the difference in sales for each month.
So it shows like that:
Thanks,
Greg
