Cross tab to begin fiscal year

N

Nick

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.
 
K

KARL DEWEY

Try this --
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, "FY-" & Format(DateAdd("m",3,[ActionDate]), "yy") AS [FY]
PIVOT Format([ActionDate],"mmm") In
("Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep");
 
J

John Spencer

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
 
N

Nick

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.

John Spencer said:
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
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.
 
K

KARL DEWEY

You have to apply the criteria to FY and not calendar date. Post your SQL.
--
KARL DEWEY
Build a little - Test a little


Nick said:
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.

John Spencer said:
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
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.
 
J

John Spencer

I don't understand where are you entering "2007" or "2008". Nothing
that I can see if your posting indicates this.

Perhaps what you want is

Parameters [Enter Fiscal Year] as Long;
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([Enter Fiscal Year]-1,10,1) and
DateSerial([Enter Fiscal Year],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");

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

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.

John Spencer said:
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
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.
 
N

Nick

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,



KARL DEWEY said:
You have to apply the criteria to FY and not calendar date. Post your SQL.
--
KARL DEWEY
Build a little - Test a little


Nick said:
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.

John Spencer said:
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.
 
K

KARL DEWEY

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


Nick said:
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,



KARL DEWEY said:
You have to apply the criteria to FY and not calendar date. Post your SQL.
--
KARL DEWEY
Build a little - Test a little


Nick said:
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.
 
N

Nick

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!

KARL DEWEY said:
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


Nick said:
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,



KARL DEWEY said:
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.
 
K

KARL DEWEY

Use this in query1 --
WHERE ((EmployeeProfile.Situation)="4584") AND
((DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[Enter First Fiscal Year]) OR
(DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[Enter Second 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
First Fiscal Year]) OR (DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[Enter
Second 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


Nick said:
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!

KARL DEWEY said:
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


Nick said:
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.
 
N

Nick

Query 1 works but the message if receive from query 2 is "You tried to
execute a query that does not include the specified expression "Expr1" as
part of an aggergate fuction. Please let me know where I made my error.
Thanks
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 AND
((DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[EnterFirst Fiscal Year]) OR
(DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[EnterSecond Fiscal Year]))
PIVOT Format([ActionDate],"mmm") In
("Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep");
KARL DEWEY said:
Use this in query1 --
WHERE ((EmployeeProfile.Situation)="4584") AND
((DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[Enter First Fiscal Year]) OR
(DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[Enter Second 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
First Fiscal Year]) OR (DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[Enter
Second 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


Nick said:
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!

KARL DEWEY said:
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.
 
K

KARL DEWEY

My error --
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")))
AND
((DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[EnterFirst Fiscal Year]) OR
(DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[EnterSecond Fiscal Year]))
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");

--
KARL DEWEY
Build a little - Test a little


Nick said:
Query 1 works but the message if receive from query 2 is "You tried to
execute a query that does not include the specified expression "Expr1" as
part of an aggergate fuction. Please let me know where I made my error.
Thanks
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 AND
((DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[EnterFirst Fiscal Year]) OR
(DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[EnterSecond Fiscal Year]))
PIVOT Format([ActionDate],"mmm") In
("Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep");
KARL DEWEY said:
Use this in query1 --
WHERE ((EmployeeProfile.Situation)="4584") AND
((DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[Enter First Fiscal Year]) OR
(DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[Enter Second 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
First Fiscal Year]) OR (DatePart("yyyy",DateAdd("m",3,[ActionDate]))=[Enter
Second 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


Nick said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top