Fiscal Year as Column (1 Viewer)

tanyamc

Registered User.
Local time
Yesterday, 20:31
Joined
Mar 7, 2019
Messages
43
I have a query that generates a list of tasks that have a due and completed date.

Name Task Due Date Completed Date

Joe Task1 10/1/16 10/30/16
Joe Task1 10/1/17 1/1/18
Joe Task1 10/1/18 2/1/19
Joe Task2 10/1/16 1/1/18

The date range for the data is three fiscal years. I want to end up with a list like this of completed dates based on FY of scheduled date. There are multiple tasks but not everyone has every task, but everyone will have at least one task.
CFY - current fiscal year. There may be multiple instances of the tasks, but I would want the most recent/latest date for that FY.

Name CFY CFY-1 CFY-2
Joe
Task1 2/1/19 1/1/18 10/30/16
Task2 1/1/18

I am thinking crosstab query but I don't know how to do it! Many thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:31
Joined
Oct 29, 2018
Messages
21,358
Hi. Are you able to post a sample db? It might be easier to just show you how using a demo.
 

June7

AWF VIP
Local time
Yesterday, 18:31
Joined
Mar 9, 2014
Messages
5,423
Start with calculating the fiscal year for each record. What is your FY period? How do you want to represent FY, like FY2019?

Example for Oct 1 2018 - Sep 30 2019 FY.

FY: "FY" & Year([CompletedDate]) + IIf(Month([CompletedDate]) < 10, 0, 1)

How can Joe complete Task 1 on 3 different dates? Are these 3 different projects?
 
Last edited:

tanyamc

Registered User.
Local time
Yesterday, 20:31
Joined
Mar 7, 2019
Messages
43
Here is data that I want to query and there is a mockup of report as well. thanks.
 

Attachments

  • qry_Farm_Visits.xlsx
    17.6 KB · Views: 85

tanyamc

Registered User.
Local time
Yesterday, 20:31
Joined
Mar 7, 2019
Messages
43
Start with calculating the fiscal year for each record. What is your FY period? How do you want to represent FY, like FY2019?

Example for Oct 1 2018 - Sep 30 2019 FY.

FY: "FY" & Year([CompletedDate]) + IIf(Month([CompletedDate]) < 10, 0, 1)

How can Joe complete Task 1 on 3 different dates? Are these 3 different projects?

The task can be repeated as often as needed during the year but only has to be done once, thus wanting to look at max value/most recent completion within the fiscal year.

Where would I put the calculation? I'm not sure if I need to query my query three times (once per FY period) and then create report from the three queries or if I can do crosstab instead?
 

June7

AWF VIP
Local time
Yesterday, 18:31
Joined
Mar 9, 2014
Messages
5,423
Consider:

TRANSFORM Max(Tasks.CompletedDate) AS MaxOfCompletedDate
SELECT Tasks.EmpName, Tasks.TaskID
FROM Tasks
GROUP BY Tasks.EmpName, Tasks.TaskID
PIVOT "FY" & Year([CompletedDate])+IIf(Month([CompletedDate])<10,0,1);
 
Last edited:

tanyamc

Registered User.
Local time
Yesterday, 20:31
Joined
Mar 7, 2019
Messages
43
Consider:

TRANSFORM Max(Tasks.CompletedDate) AS MaxOfCompletedDate
SELECT Tasks.EmpName, Tasks.TaskID
FROM Tasks
GROUP BY Tasks.EmpName, Tasks.TaskID
PIVOT "FY" & Year([CompletedDate])+IIf(Month([CompletedDate])<10,0,1);

Where do I put this? In the SQL on a blank query? Thanks.
 

June7

AWF VIP
Local time
Yesterday, 18:31
Joined
Mar 9, 2014
Messages
5,423
Can build in the query designer or in SQL view. Be sure to use your field and table names.
 

tanyamc

Registered User.
Local time
Yesterday, 20:31
Joined
Mar 7, 2019
Messages
43
Consider:

TRANSFORM Max(Tasks.CompletedDate) AS MaxOfCompletedDate
SELECT Tasks.EmpName, Tasks.TaskID
FROM Tasks
GROUP BY Tasks.EmpName, Tasks.TaskID
PIVOT "FY" & Year([CompletedDate])+IIf(Month([CompletedDate])<10,0,1);

So I want them by FY based on the scheduled date, and I want the completed (or not) date as the field data....this only references completed date...sorry I am really new at this...
 

plog

Banishment Pending
Local time
Yesterday, 21:31
Joined
May 11, 2011
Messages
11,613
So I want them by FY based on the scheduled date, and I want the completed (or not) date as the field data

First, don't change terms on us. You have 2 date fields in your SQL, neither of which are called "scheduled date". Second, the above quote uses 2 distinct date fields, yet the SQL you posted with it only uses the [completed date] field.

Third, use better field names--"name" is a reserved word and will cause issues when coding/querying. I suggest you prefix it with what it is the name of (e.g. EmployeeName). Also, no spaces in field names, just makes things more difficult. Instead capitalize (CompletedDate)

Fourth, try this SQL:

Code:
TRANSFORM Max(Tasks.CompletedDate) AS MaxOfCompletedDate
SELECT Tasks.EmployeeName, Tasks.Task
FROM Tasks
GROUP BY Tasks.EmployeeName, Tasks.Task
ORDER BY Year([DueDate]) DESC 
PIVOT Year([DueDate]);

It's not going to be exaclty what you want, but it should get you to where you are going. You will have to fix either that SQL field names or your table as I recommended, you will have to change the FY calculation and you might have to swap the date fields because your last post confused me.
 

plog

Banishment Pending
Local time
Yesterday, 21:31
Joined
May 11, 2011
Messages
11,613
To confuse matters more, your initial expected results don't make sense:

Starting Data:
Joe Task1 10/1/16 10/30/16
Joe Task1 10/1/17 1/1/18
Joe Task1 10/1/18 2/1/19
Joe Task2 10/1/16 1/1/18

Expected Results:
Task1 2/1/19 1/1/18 10/30/16
Task2 1/1/18

There is no logical reason to expect the Task2 date to fall in the same bucket as Task 1 2/1/19. 1/1/18 is not in the same fiscal year as 2/1/19, nor is 10/1/16 in the same fiscal year as 10/1/18. That result doesn't make sense.
 

June7

AWF VIP
Local time
Yesterday, 18:31
Joined
Mar 9, 2014
Messages
5,423
I expect that misplacement of Task2 1/1/18 is due to forum dropping spaces and OP probably intended it to show under the second date column.
 

tanyamc

Registered User.
Local time
Yesterday, 20:31
Joined
Mar 7, 2019
Messages
43
Sorry I was not being clear. I am trying to create a real world example here that differs from my actual use case so that answers are more universal for searches. But then I jumbled them.

I have completed some basic training but again comparing it to my actual use cases is a struggle. I think I figured it out but I made it more complicated than I need to, again an area where I struggle to plan ahead how to structure things. I am doing this in between a million other things as it is not my job, but rather trying to create tools to make my job easier/more efficient.

I have added FY calculation to each record in my source query to show what FY it was scheduled (due). Will keep muddling through. Thanks for trying to help! Again, my apologies.
 

June7

AWF VIP
Local time
Yesterday, 18:31
Joined
Mar 9, 2014
Messages
5,423
Okay, using DueDate to define FY and returning the Max(CompletedDate) as value, the output will be:

EmpName. TaskID. FY2017....... FY2018 ...FY2019
Joe .......... 1 ........ 10/20/2016 1/1/2018 2/1/2019
Joe .......... 2 ........ 1/1/2018

TRANSFORM Max(Tasks.CompletedDate) AS MaxOfCompletedDate
SELECT Tasks.EmpName, Tasks.TaskID
FROM Tasks
GROUP BY Tasks.EmpName, Tasks.TaskID
PIVOT "FY" & Year([DueDate])+IIf(Month([DueDate])<10,0,1);
 
Last edited:

Users who are viewing this thread

Top Bottom