Add FY to Report header

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

I have posted before but was unable to access link.
I have a report using a crosstab. How do I post the FY in the report header.
TRANSFORM Count(DRIVELOG.[Driving Observation]) AS [CountOfDriving
Observation1]
SELECT Employees.SSI, [Last Name] & ", " & [first name] AS Expr2,
DRIVELOG.[Driving Observation], Employees.Craft, Count(DRIVELOG.[Driving
Observation]) AS [Total Of Driving Observation]
FROM DRIVELOG RIGHT JOIN Employees ON DRIVELOG.SSiNumber = Employees.SSI
WHERE (((Employees.Craft) Like "city carrier" Or (Employees.Craft) Like
"rural carrier" Or (Employees.Craft) Like "ptF" Or (Employees.Craft) Like
"TE"))
GROUP BY Employees.SSI, [Last Name] & ", " & [first name], DRIVELOG.[Driving
Observation], Employees.Craft, DRIVELOG.[Driving Observation], Employees.Craft
PIVOT Format([ActionDate],"mmm") In
("Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep");
 
Without a little more (lot more) information, I can only suggest you add a
label with a caption of: the FY

If you want a less flippant answer, tell us where the FY might be stored,
your table structures and other necessary information. None of us can see
your application.
 
Sorry, I was not sure of what was needed. This is where the FY is stored do
you need more? 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",DateAdd("m",3,[ActionDate])))=[Enter Fiscal Year]))
ORDER BY EmployeeProfile.ActionDate;
 
IMO, parameter prompts in queries are never good user interface. Always use
controls on forms.

The query you showed ealier was a crosstab query. It isn't clear how that
query relates to your new SQL.

However, try:
SELECT [Enter Fiscal Year] as FY, [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",DateAdd("m",3,[ActionDate])))=[Enter Fiscal Year]))
ORDER BY EmployeeProfile.ActionDate;

--
Duane Hookom
Microsoft Access MVP


Nick said:
Sorry, I was not sure of what was needed. This is where the FY is stored do
you need more? 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",DateAdd("m",3,[ActionDate])))=[Enter Fiscal Year]))
ORDER BY EmployeeProfile.ActionDate;


Nick said:
I have posted before but was unable to access link.
I have a report using a crosstab. How do I post the FY in the report header.
TRANSFORM Count(DRIVELOG.[Driving Observation]) AS [CountOfDriving
Observation1]
SELECT Employees.SSI, [Last Name] & ", " & [first name] AS Expr2,
DRIVELOG.[Driving Observation], Employees.Craft, Count(DRIVELOG.[Driving
Observation]) AS [Total Of Driving Observation]
FROM DRIVELOG RIGHT JOIN Employees ON DRIVELOG.SSiNumber = Employees.SSI
WHERE (((Employees.Craft) Like "city carrier" Or (Employees.Craft) Like
"rural carrier" Or (Employees.Craft) Like "ptF" Or (Employees.Craft) Like
"TE"))
GROUP BY Employees.SSI, [Last Name] & ", " & [first name], DRIVELOG.[Driving
Observation], Employees.Craft, DRIVELOG.[Driving Observation], Employees.Craft
PIVOT Format([ActionDate],"mmm") In
("Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep");
Click to expand...
Click to expand...
 
Back
Top