Access Query - Fiscal Month Ordering - Honda-Tech - Honda Forum Discussion


General Discussion and Debate Discuss, Debate, and Converse with other Honda-Tech members in a mature, intelligent manner.

Access Query - Fiscal Month Ordering

Reply
 
 
 
Thread Tools Search this Thread
Old 10-12-2017, 09:25 AM   #1
BCICAN
Thread Starter
 
Dunc's Avatar
 
Join Date: Jan 2005
Location: Cincinnati, OH
Posts: 3,199
Default Access Query - Fiscal Month Ordering

I'm losing my mind here. I'm trying to get records to be sorted first by fiscal year and then by fiscal month. We are converting from calendar to fiscal years here. So instead of being Jan-Dec it needs to be Apr-Mar.

Why is the attached screenshot occurring? A FiscalMo of 10 does not come before 1-9. There are fields before the Fiscal Year in the conditions, but nothing that is sorted and they only contain the calculated fiscal year. If there are no other sorts to the left of the Fiscal Year that should be the first sorted field right? If I place a copy after all other fields of FiscalMo and FiscalYr in that order and don't "show" the fields it groups then it works kind of. For example, it groups all fiscal months equal to 1 and then sorts the fiscal year within that group. That gives me [month =1, year =15,16,17,18] [month=2 year=15,16,17,18] but I want [year=15 month =1,2,3,4,5] [year=16 month=1,2,3,4,5]. I can't understand how a sort order of Mo then Yr works, but not Yr then Mo.
Attached Images
 
Dunc is offline   Reply With Quote
Old 10-12-2017, 09:29 AM   #2
B20 Bhai M.B.B.S
 
b20sedan's Avatar
 
Join Date: Mar 2006
Location: Firebird, AZ, USA
Posts: 1,235
Default Re: Access Query - Fiscal Month Ordering

What's the data type for the numerical month field?

I'd set the data type to that field to a number
b20sedan is offline   Reply With Quote
Old 10-12-2017, 10:19 AM   #3
0x5359-0055
 
213374U's Avatar
 
Join Date: Feb 2005
Location: Texas doe, they do everything big. u mad?
Posts: 5,731
Default Re: Access Query - Fiscal Month Ordering

I got bad news for you OP....

Quote:
What does End of Life mean?

Office 2007, like almost all Microsoft products, has a support lifecycle during which we provide new features, bug fixes, security fixes, and so on. This lifecycle typically lasts for 10 years from the date of the product’s initial release, and the end of this lifecycle is known as the product’s End of Life. When Office 2007 reaches its End of Life on October 10, 2017, Microsoft will no longer provide:
  • Technical support for issues
  • Bug fixes for issues that are discovered
  • Security fixes for vulnerabilities that are discovered
Because of the changes listed above, we strongly recommend that you upgrade as soon as possible.

213374U is offline   Reply With Quote
Old 10-12-2017, 10:36 AM   #4
BCICAN
Thread Starter
 
Dunc's Avatar
 
Join Date: Jan 2005
Location: Cincinnati, OH
Posts: 3,199
Default Re: Access Query - Fiscal Month Ordering

Quote:
Originally Posted by b20sedan View Post
What's the data type for the numerical month field?

I'd set the data type to that field to a number
Both the FiscalYr: "yyyy" and FiscalMo: "m" fields in the linked Excel table are formatted as number. I feel like I'm losing my mind because there is nothing to me saying this shouldn't work.
Quote:
Originally Posted by 213374U View Post
I got bad news for you OP....
Well that is interesting. How does that header bar description reconcile to this?
Attached Images
 
Dunc is offline   Reply With Quote
Old 10-12-2017, 10:39 AM   #5
B20 Bhai M.B.B.S
 
b20sedan's Avatar
 
Join Date: Mar 2006
Location: Firebird, AZ, USA
Posts: 1,235
Default Re: Access Query - Fiscal Month Ordering

What about the column format in Access specifically?
b20sedan is offline   Reply With Quote
Old 10-12-2017, 10:43 AM   #6
BCICAN
Thread Starter
 
Dunc's Avatar
 
Join Date: Jan 2005
Location: Cincinnati, OH
Posts: 3,199
Default Re: Access Query - Fiscal Month Ordering

Quote:
Originally Posted by b20sedan View Post
What about the column format in Access specifically?
Do you mean for the Date Master table? It would also be a number since that is how it's formatted in the Excel file. I can't change that since Date Master is a linked table unless I convert it to local table then check that.
Dunc is offline   Reply With Quote
Old 10-12-2017, 10:50 AM   #7
Loves Tossing Salad
 
09chaplak's Avatar
 
Join Date: Mar 2014
Location: Uh, uh DIPSY
Posts: 685
Default Re: Access Query - Fiscal Month Ordering

Move the month before the fiscal year
09chaplak is offline   Reply With Quote
Old 10-12-2017, 10:57 AM   #8
BCICAN
Thread Starter
 
Dunc's Avatar
 
Join Date: Jan 2005
Location: Cincinnati, OH
Posts: 3,199
Default Re: Access Query - Fiscal Month Ordering

Quote:
Originally Posted by 09chaplak View Post
Move the month before the fiscal year
It seems like that is my only option and will have to live with it.

I just didn't want the results to be:
Month/Year
1/2015
1/2016
1/2017
1/2018
2/2015
2/2016
2/2017
2/2018

Instead I wanted the results to be:
Month/Year
1/2015
2/2015
3/2015
4/2015
5/2015
1/2016
2/2016
3/2016
4/2016
5/2016
Dunc is offline   Reply With Quote
Old 10-12-2017, 11:01 AM   #9
0x5359-0055
 
213374U's Avatar
 
Join Date: Feb 2005
Location: Texas doe, they do everything big. u mad?
Posts: 5,731
Default Re: Access Query - Fiscal Month Ordering

Quote:
Originally Posted by Dunc View Post
Well that is interesting. How does that header bar description reconcile to this?
Hmmmmm, maybe the DB was created in A2007 and that's why?
213374U is offline   Reply With Quote
Old 10-12-2017, 11:03 AM   #10
0x5359-0055
 
213374U's Avatar
 
Join Date: Feb 2005
Location: Texas doe, they do everything big. u mad?
Posts: 5,731
Default Re: Access Query - Fiscal Month Ordering

Y/M/D is what you would need to sort the way you describe above

2015/1
2015/2
2015/3
...
2016/1
2016/2
2016/3

Also, stretch your digits for month out to 2 for all so that it shows as 01, 02, 03, and so on. This will make '10' appear where it should.
213374U is offline   Reply With Quote
Old 10-12-2017, 11:09 AM   #11
Loves Tossing Salad
 
09chaplak's Avatar
 
Join Date: Mar 2014
Location: Uh, uh DIPSY
Posts: 685
Default Re: Access Query - Fiscal Month Ordering

Move the month before fiscal year then try sorting it again
09chaplak is offline   Reply With Quote
Old 10-12-2017, 11:35 AM   #12
BCICAN
Thread Starter
 
Dunc's Avatar
 
Join Date: Jan 2005
Location: Cincinnati, OH
Posts: 3,199
Default Re: Access Query - Fiscal Month Ordering

So I solved the problem, and wow I feel extra dumb right now.

In my excel data file that I'm query data records I currently have transactions for 1/1/15 to present. This is because we are currently on calendar years. The sorting is working just fine and as I thought it should. I'm so dumb because obviously when we convert the fiscal year to be Apr-Mar I would need to pull in records from 4/1/14 to get a full year view of fiscal '15. All this time I was thinking it wasn't working but knew it was something simple and dumb that was causing the problem. At least now I realize why fiscal '15 month 10 was the first to show in the results since that's the oldest record I have in my data file. Facepalm.
Quote:
Originally Posted by 213374U View Post
Hmmmmm, maybe the DB was created in A2007 and that's why?
That was my first thought so I opened a new file and sure enough it also says 2007 at the top.
Dunc is offline   Reply With Quote
Old 10-12-2017, 12:13 PM   #13
Loves Tossing Salad
 
09chaplak's Avatar
 
Join Date: Mar 2014
Location: Uh, uh DIPSY
Posts: 685
Default Re: Access Query - Fiscal Month Ordering

Thank you for the update so subsequent users may now know the answer if they ever also come across this problem.

It's been a pleasure interwebing with you good sir.
09chaplak is offline   Reply With Quote
 
 
Reply

Related Topics
Thread Thread Starter Forum Replies Last Post
So about those wikileaks and the aliens... 2LEM1 General Discussion and Debate 55 10-14-2016 06:52 AM
Please explain what I should expect for a Raise in this sistutation whited General Discussion and Debate 39 08-18-2016 09:38 AM
negative interest rates...NIRP signalpuke General Discussion and Debate 59 06-26-2016 08:23 PM
Philly approves tax on soda Tim2179 General Discussion and Debate 107 06-20-2016 09:29 AM
Banks lent a record 4.67 trillion yuan ($709 billion) in the first quarter signalpuke General Discussion and Debate 3 06-01-2016 12:05 PM


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off



All times are GMT -8. The time now is 04:51 PM.