Calculating Fiscal Year Sales in a query
- Thread starter gmath
- Start date
You would put criteria underneath the FY field. You could put this:
>=2013
Which would yield FY 2013, 2014, 2015 data. However, since the whole point of me taking you down this complicated road was to eliminate the need to update queries manually, I would put in code to calculate what 3 years ago FY was. So I would advise using this as criteria:
>=GetFiscalYear(DateAdd("yyyy", -3, Date())
Using that will always yield the 3 years you want, no matter when you run it--now, in 60 days or in 6 years.
>=2013
Which would yield FY 2013, 2014, 2015 data. However, since the whole point of me taking you down this complicated road was to eliminate the need to update queries manually, I would put in code to calculate what 3 years ago FY was. So I would advise using this as criteria:
>=GetFiscalYear(DateAdd("yyyy", -3, Date())
Using that will always yield the 3 years you want, no matter when you run it--now, in 60 days or in 6 years.
namlian--
1. I've had them change. So instead of hunting every instance of that code, I just do it in a function.
2. You can catch and handle Null/non-date values easier.
3. The code is simpler. Instead of nested functions, you just have one call. Which, if you are calculating a date to get its fiscal year (like I did in the criteria I just posted), it makes it a lot simpler still.
4. And I just made him a little less scared of code.
1. I've had them change. So instead of hunting every instance of that code, I just do it in a function.
2. You can catch and handle Null/non-date values easier.
3. The code is simpler. Instead of nested functions, you just have one call. Which, if you are calculating a date to get its fiscal year (like I did in the criteria I just posted), it makes it a lot simpler still.
4. And I just made him a little less scared of code.
namlian - I must say I was a little skeptical of the need for the code at first, but after I had a little more time to think about it I do agree with plog's approach. Even though I will probably end up redoing this database once I have it the way I like it with fixed data samples to branch it out to update with live data, this is definitely the way to approach it.
plog - I appreciate your help, although scared of code and unsure of how to use it in Access are 2 different things. You did challenge me to think as opposed to just giving me the answer, which in the end, is a much better approach. Like teaching a man to fish, he will eat for a lifetime, give a man a fish and he will eat for a day.
Hopefully, I can take what I learned and solve some of the other obstacles I have in front of me.
plog - I appreciate your help, although scared of code and unsure of how to use it in Access are 2 different things. You did challenge me to think as opposed to just giving me the answer, which in the end, is a much better approach. Like teaching a man to fish, he will eat for a lifetime, give a man a fish and he will eat for a day.
Hopefully, I can take what I learned and solve some of the other obstacles I have in front of me.
I thought the set-up was good to go when I realized, if I use >=GetFiscalYear(DateAdd("yyyy", -3, Date()), then I get multiple lines; however, if I change that to >=GetFiscalYear(DateAdd("yyyy", -0, Date()) then I get singular lines which works great until I try to total the years to get a 4 year total. See attached.
I have tried to calculate the total which works fine where there is values in all the fields, I tried it two different ways with the same result
option 1 - 4yr Sales: =FY2015 + FY2014 + FY2013 + FY2012
option 2 - 4yr Sales: Sum([FY2015] + [FY2014] + [FY2013] + [FY2012])
As I said some of the fields don't have values and these set-ups will not return a value.
So I thought I might go into create another subQuery [see sub_queryFYSales-design.jpg] that calculates just the 4 year total and pulls that into this query. I ran into the same problem of getting up to 4 lines for 1 product at one customer, a line for 2012, 2013, 2014, and 2015.
Then I decided if I could through a 0 value into the null fields, so I tried
FY2015: IIF([FYSales] is null, 0, [FYSales])
but get a Syntax error (comma) in query expression
I have tried to calculate the total which works fine where there is values in all the fields, I tried it two different ways with the same result
option 1 - 4yr Sales: =FY2015 + FY2014 + FY2013 + FY2012
option 2 - 4yr Sales: Sum([FY2015] + [FY2014] + [FY2013] + [FY2012])
As I said some of the fields don't have values and these set-ups will not return a value.
So I thought I might go into create another subQuery [see sub_queryFYSales-design.jpg] that calculates just the 4 year total and pulls that into this query. I ran into the same problem of getting up to 4 lines for 1 product at one customer, a line for 2012, 2013, 2014, and 2015.
Then I decided if I could through a 0 value into the null fields, so I tried
FY2015: IIF([FYSales] is null, 0, [FYSales])
but get a Syntax error (comma) in query expression
Attachments
You've dumped a ton of images and queries and fields and failed attempts on me, I really don't know what you are trying to achieve. I do see that you are having trouble with Null values and converting them to 0 values. To do that, you should use the Nz function (http://www.techonthenet.com/access/functions/advanced/nz.php).
If you need other assistance provide me with 2 sets of data:
A. starting sample data
B. expected resulting data based on A.
If you need other assistance provide me with 2 sets of data:
A. starting sample data
B. expected resulting data based on A.
RainLover
VIP From a land downunder
- Local time
- Tomorrow, 07:13
- Joined
- Jan 5, 2009
- Messages
- 5,041
I read most of this, but not all.
May I suggest that you look at some of the names you are using.
e.g. "Date" Date is a reserved word. If you use it, it could throw an error without you knowing. Suggest you Google and find a list of Reserved words to refer to.
May I suggest that you look at some of the names you are using.
e.g. "Date" Date is a reserved word. If you use it, it could throw an error without you knowing. Suggest you Google and find a list of Reserved words to refer to.
I tried using the Nz function, but I'm guessing it isn't returning a number, but txt.
As far as what I'm trying to accomplish, you helped me take my data and break it up into the last 3 fiscal years and current fiscal year. Now I would like to total that data.
So I took the qryFYSales query you helped me generate which has Customer Number, Product, Whse, FY, FYSales, FYSales_1, FYSales_2, FYSales_3.
I wanted to have the following information in a query to generate a report with this information
Account Name - Pulled from CustNum-Trend
Customer Number - Pulled from qryFYSales
Product - Pulled from qryFYSales
Description - Pulled from PDZRN1
FY2012 - Pulled from qryFYSales (Field FY2012: Nz([qryFYSales].[FYSales_3],0)
FY2013 - Pulled from qryFYSales (Field FY2013: Nz([qryFYSales].[FYSales_2],0)
FY2014 - Pulled from qryFYSales (Field FY2014: Nz([qryFYSales].[FYSales_1],0)
FY2015 - Pulled from qryFYSales (Field FY2015: Nz([qryFYSales].[FYSales],0)
4yrSale - Needs to be calculated (This is my problem)
LastSaleDate - Pulled from qryFirstLast Sale
qryFYSalesHist(1)-Design and qryFYSalesHist(1)-view are images of the query qryFYSalesHist I'm trying to generate, I can't include the 4yrSale in this because it is a mismatch.
The bulk of my starting data comes from the attached qryFYSales image. This is the data I'm struggling manipulating.
As far as what I'm trying to accomplish, you helped me take my data and break it up into the last 3 fiscal years and current fiscal year. Now I would like to total that data.
So I took the qryFYSales query you helped me generate which has Customer Number, Product, Whse, FY, FYSales, FYSales_1, FYSales_2, FYSales_3.
I wanted to have the following information in a query to generate a report with this information
Account Name - Pulled from CustNum-Trend
Customer Number - Pulled from qryFYSales
Product - Pulled from qryFYSales
Description - Pulled from PDZRN1
FY2012 - Pulled from qryFYSales (Field FY2012: Nz([qryFYSales].[FYSales_3],0)
FY2013 - Pulled from qryFYSales (Field FY2013: Nz([qryFYSales].[FYSales_2],0)
FY2014 - Pulled from qryFYSales (Field FY2014: Nz([qryFYSales].[FYSales_1],0)
FY2015 - Pulled from qryFYSales (Field FY2015: Nz([qryFYSales].[FYSales],0)
4yrSale - Needs to be calculated (This is my problem)
LastSaleDate - Pulled from qryFirstLast Sale
qryFYSalesHist(1)-Design and qryFYSalesHist(1)-view are images of the query qryFYSalesHist I'm trying to generate, I can't include the 4yrSale in this because it is a mismatch.
The bulk of my starting data comes from the attached qryFYSales image. This is the data I'm struggling manipulating.
Attachments
"Date" was pulled out a long time ago by a recommendation by plog
"Description"on the other hand wasn't, but I can't see how that would throw an error in this case.
Got it, I after some messing around I ended up having to make the statement
FYSales1: CCur(Nz([sub_QueryFY].[FYSales],0))
because it was pulling a mismatch on the data giving me problems.
Thanks for the help.
Attachments
RainLover
VIP From a land downunder
- Local time
- Tomorrow, 07:13
- Joined
- Jan 5, 2009
- Messages
- 5,041
So does this mean you have found a solution that meets you needs.
If so then mark the thread as solved.
BTW.
Two other solutions.
One is to export to a Temp Table in the front end.
Two is to write some code and simply loop through the records.
The other thing is that I would suggest you posting the actual Database. Cut down to a bear minimum.
The queries as presented are untidy and difficult to interoperate.
Good luck with the project.
If so then mark the thread as solved.
BTW.
Two other solutions.
One is to export to a Temp Table in the front end.
Two is to write some code and simply loop through the records.
The other thing is that I would suggest you posting the actual Database. Cut down to a bear minimum.
The queries as presented are untidy and difficult to interoperate.
Good luck with the project.
