Tracking annual training when my fiscal year starts October 1st

[CevinMoses]

Having problems working with fiscal year data since our fiscal year starts
October 1st, not January 1st. Therefore FY05 (Fiscal Year 2005) actually
started during calendar year 2004 (October 1, 2004). Here's my problem:

I need to create a query to return a list of all personnel who have not
attended a particular training event (Called "CTT" for short) since the
beginning of the fiscal year. I can write a query right now to return
everyone whose [personnel information].[CTT] < 01-Oct-04 (where [personnel
information] is the table and [CTT] is the field holding the date they
received the training). The hard part is making it use the year I want it to
automatically.

For any date in October, November, or December, I want to use 01-Oct of the
current year, so 01-Oct-04 above could be replaced with the following:
DateSerial(Year(Now()),10,1)

However, for any date From January through September, The year needs to be
adjusted to the year prior, so I could replace 01-Oct-04 with:
DateSerial(Year(Now())-1,10,1)

How can I write a conditional into the query to check which formula to use,
or is there a cleaner way? (There always is!)

Query's currently defined as:
SELECT [Personnel Information].[LAST NAME], [Personnel Information].[FIRST
NAME], [Personnel Information].CTT
FROM [Personnel Information]
GROUP BY [Personnel Information].[LAST NAME], [Personnel Information].[FIRST
NAME], [Personnel Information].RANK, [Personnel Information].CTT
HAVING ((([Personnel Information].CTT)<DateSerial(Year(Now())-1,10,1))) OR
((([Personnel Information].CTT) Is Null))
ORDER BY [Personnel Information].[LAST NAME], [Personnel Information].[FIRST
NAME];

 

[KARL DEWEY]

You should have a personnel table separately from the training information.
You need two queries. The first pulls a list of those that have attended
training in the FY.

SELECT [YourTable-3].Entrant, DatePart("yyyy",DateAdd("q",+1,[YourDate])) AS
FY
FROM [YourTable-3]
WHERE (((DatePart("yyyy",DateAdd("q",+1,[YourDate])))=[Enter FY (2004)]));

Have the second query to pull all from the personnel table not in first
query results.

 

[John Spencer (MVP)]

One method - assumes that Last Name plus first name uniquely identifies a person
(risky assumption). I hope I got the year calculation correct.

SELECT Distinct [P].[LAST NAME],
[P].[FIRST NAME],
FROM [Personnel Information] as P
WHERE NOT EXISTS
(SELECT *
FROM [Personal Information] as P1
WHERE P1.[Last Name] = P.[Last Name] AND
P1.[First Name] = P.[First Name] AND
P1.CTT Between
DateSerial(Year(DateAdd("m",-9,Date())),10,1) and
DateSerial(Year(DateAdd("m",-9,Date()))+1,9,30))

 

[CevinMoses]

Thanks for the quick reply. Your equation returned everyone that had
qualified in the current quarter, but it showed me how to write the code to
find everyone who HAD NOT taken the course since the previous October 1st, so
it did what I needed. Just changed the "Between" to a "<" and I was there.
Thanks for the help. I haven't been able to piece dates together with any
success yet, and you just solved a lot of problems for me that will allow me
to calulate quarterly results, such as who will be uncurrent by the end of
the next quarter, without ever having to manually change the year in the
query. I knew there was I way, i just couldn't figure it out. Serves me
right for never taking a class in this.

-Cevin

One method - assumes that Last Name plus first name uniquely identifies a person
(risky assumption). I hope I got the year calculation correct.

SELECT Distinct [P].[LAST NAME],
[P].[FIRST NAME],
FROM [Personnel Information] as P
WHERE NOT EXISTS
(SELECT *
FROM [Personal Information] as P1
WHERE P1.[Last Name] = P.[Last Name] AND
P1.[First Name] = P.[First Name] AND
P1.CTT Between
DateSerial(Year(DateAdd("m",-9,Date())),10,1) and
DateSerial(Year(DateAdd("m",-9,Date()))+1,9,30))

Having problems working with fiscal year data since our fiscal year starts
October 1st, not January 1st. Therefore FY05 (Fiscal Year 2005) actually
started during calendar year 2004 (October 1, 2004). Here's my problem:

I need to create a query to return a list of all personnel who have not
attended a particular training event (Called "CTT" for short) since the
beginning of the fiscal year. I can write a query right now to return
everyone whose [personnel information].[CTT] < 01-Oct-04 (where [personnel
information] is the table and [CTT] is the field holding the date they
received the training). The hard part is making it use the year I want it to
automatically.

For any date in October, November, or December, I want to use 01-Oct of the
current year, so 01-Oct-04 above could be replaced with the following:
DateSerial(Year(Now()),10,1)

However, for any date From January through September, The year needs to be
adjusted to the year prior, so I could replace 01-Oct-04 with:
DateSerial(Year(Now())-1,10,1)

How can I write a conditional into the query to check which formula to use,
or is there a cleaner way? (There always is!)

Query's currently defined as:
SELECT [Personnel Information].[LAST NAME], [Personnel Information].[FIRST
NAME], [Personnel Information].CTT
FROM [Personnel Information]
GROUP BY [Personnel Information].[LAST NAME], [Personnel Information].[FIRST
NAME], [Personnel Information].RANK, [Personnel Information].CTT
HAVING ((([Personnel Information].CTT)<DateSerial(Year(Now())-1,10,1))) OR
((([Personnel Information].CTT) Is Null))
ORDER BY [Personnel Information].[LAST NAME], [Personnel Information].[FIRST
NAME];