Thank you, I do get the data for the fiscal year requested but totals and
months of information are seprates by year, Is there away to combine totals
so that I get a single total for both years. I know it something to do with
[4584 Log].Expr1 but do not know how to correct it.
Again Thank You!
:
You did not apply the offset that I suggested nor John.
Use this in query1 --
WHERE ((EmployeeProfile.Situation)="4584") AND
(DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[Enter Fiscal Year])
ORDER BY EmployeeProfile.ActionDate;
Use this in query2 --
FROM [4584 Log] RIGHT JOIN Employees ON [4584 Log].SSiNumber = Employees.SSI
WHERE (((Employees.Craft) Like "te" Or (Employees.Craft) Like "city carrier"
Or (Employees.Craft) Like "ptf" Or (Employees.Craft) Like "rural carrier"))
GROUP BY Employees.SSI, [Last Name] & ", " & [first name], Employees.Craft,
[4584 Log].Expr1 AND (DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[Enter
Fiscal Year])
PIVOT Format([ActionDate],"mmm") In
("Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep");
--
KARL DEWEY
Build a little - Test a little
:
Query 1
SELECT [Last Name] & ", " & [First Name] & " " & [MiddleName] & "." AS
[Employee Name], EmployeeProfile.SSiNumber, EmployeeProfile.ActionDate,
EmployeeProfile.Situation AS [Driving Observation],
EmployeeProfile.Resolution AS Comments, DatePart("yyyy",[ActionDate]) AS Expr1
FROM Employees LEFT JOIN EmployeeProfile ON Employees.SSI =
EmployeeProfile.SSiNumber
WHERE (((EmployeeProfile.Situation)="4584") AND
((DatePart("yyyy",[ActionDate]))=[Enter Year]))
ORDER BY EmployeeProfile.ActionDate;
Query 2
TRANSFORM Count([4584 Log].[Driving Observation]) AS [CountOfDriving
Observation]
SELECT Employees.SSI, [Last Name] & ", " & [first name] AS Expr2,
Employees.Craft, [4584 Log].Expr1, Count([4584 Log].[Driving Observation]) AS
[Total Of Driving Observation]
FROM [4584 Log] RIGHT JOIN Employees ON [4584 Log].SSiNumber = Employees.SSI
WHERE (((Employees.Craft) Like "te" Or (Employees.Craft) Like "city carrier"
Or (Employees.Craft) Like "ptf" Or (Employees.Craft) Like "rural carrier"))
GROUP BY Employees.SSI, [Last Name] & ", " & [first name], Employees.Craft,
[4584 Log].Expr1
PIVOT Format([ActionDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Thank You,
:
You have to apply the criteria to FY and not calendar date. Post your SQL.
--
KARL DEWEY
Build a little - Test a little
:
Thank you, this is what I would like, However when I request data for 2008, I
only receive information from January to June, and when I enter 2007, I
receive data for October to December.
:
TRANSFORM Count([4584 Log].[Driving Observation])
AS [CountOfDriving Observation]
SELECT Employees.SSI, [Last Name] & ", " & [first name] AS Expr2,
Employees.Craft, [4584 Log].Expr1
, Count([4584 Log].[Driving Observation]) AS [Total Of Driving Observation]
FROM [4584 Log] RIGHT JOIN Employees ON [4584 Log].SSiNumber = Employees.SSI
WHERE Employees.Craft In ("te", "city carrier", "ptf", "rural carrier")
AND ActionDate Between
DateSerial(Year(DateAdd("m",3,Date()))-1,10,1) and
DateSerial(Year(DateAdd("m",3,Date())),9,30)
GROUP BY Employees.SSI, [Last Name] & ", " & [first name], Employees.Craft,
[4584 Log].Expr1
PIVOT Format([ActionDate],"mmm") In
("Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep");
I've adjusted the date range so that on October 1 of the current year (2008)
the report will switch to return data for October 2008 to Sept 2009. If that
is not what you want you can edit the date range to
AND ActionDate Between
DateSerial(Year(Date())-1,10,1) and
DateSerial(Year(Date()),9,30)
which should return data for Oct 2007 to Sept 2008 until Jan 1 of 2009.
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Nick wrote:
I am using a cross tab quer the runs from January to December. I need to
change the query to report from October of the previous year to September of
the current year. I have enclosed my query. I hope this is an easy fix.
TRANSFORM Count([4584 Log].[Driving Observation]) AS [CountOfDriving
Observation]
SELECT Employees.SSI, [Last Name] & ", " & [first name] AS Expr2,
Employees.Craft, [4584 Log].Expr1, Count([4584 Log].[Driving Observation]) AS
[Total Of Driving Observation]
FROM [4584 Log] RIGHT JOIN Employees ON [4584 Log].SSiNumber = Employees.SSI
WHERE (((Employees.Craft) Like "te" Or (Employees.Craft) Like "city carrier"
Or (Employees.Craft) Like "ptf" Or (Employees.Craft) Like "rural carrier"))
GROUP BY Employees.SSI, [Last Name] & ", " & [first name], Employees.Craft,
[4584 Log].Expr1
PIVOT Format([ActionDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Than
Thank you in advance for your help.
