formatting of months in a summaried query

  • Thread starter Thread starter JulieD
  • Start date Start date
J

JulieD

hi all

i have a query - SQL view
SELECT Month([AttendDate]) AS MonthNumber, ztbl_Department.Department,
Sum(TBL_Courses.CourseCost) AS SumOfCourseCost
FROM (ztbl_Department INNER JOIN TBL_Staff ON ztbl_Department.Department =
TBL_Staff.Department) INNER JOIN (TBL_Courses INNER JOIN TBL_CoursesAttended
ON TBL_Courses.CourseName = TBL_CoursesAttended.Course) ON TBL_Staff.StaffID
= TBL_CoursesAttended.StaffID
GROUP BY Month([AttendDate]), ztbl_Department.Department;

which displays the Months as a number (1 = Jan etc), how do i (in the same
query) format the months to show in words - i've tried
Months: Format(Month([AttendDate]) ,"mmm")
but it doesn't work.

Cheers
JulieD
 
SELECT Format([AttendDate], "mmm") AS MonthName, ztbl_Department.Department,
Sum(TBL_Courses.CourseCost) AS SumOfCourseCost
FROM (ztbl_Department INNER JOIN TBL_Staff ON ztbl_Department.Department =
TBL_Staff.Department) INNER JOIN (TBL_Courses INNER JOIN TBL_CoursesAttended
ON TBL_Courses.CourseName = TBL_CoursesAttended.Course) ON TBL_Staff.StaffID
= TBL_CoursesAttended.StaffID
GROUP BY Format([AttendDate], "mmm")), ztbl_Department.Department;

HTH
Van T. Dinh
MVP (Access)
 
Hi Van

thanks so much! ... talk about not seeing the trees for the forrest!

Cheers
JulieD

Van T. Dinh said:
SELECT Format([AttendDate], "mmm") AS MonthName, ztbl_Department.Department,
Sum(TBL_Courses.CourseCost) AS SumOfCourseCost
FROM (ztbl_Department INNER JOIN TBL_Staff ON ztbl_Department.Department =
TBL_Staff.Department) INNER JOIN (TBL_Courses INNER JOIN TBL_CoursesAttended
ON TBL_Courses.CourseName = TBL_CoursesAttended.Course) ON TBL_Staff.StaffID
= TBL_CoursesAttended.StaffID
GROUP BY Format([AttendDate], "mmm")), ztbl_Department.Department;

HTH
Van T. Dinh
MVP (Access)


JulieD said:
hi all

i have a query - SQL view
SELECT Month([AttendDate]) AS MonthNumber, ztbl_Department.Department,
Sum(TBL_Courses.CourseCost) AS SumOfCourseCost
FROM (ztbl_Department INNER JOIN TBL_Staff ON ztbl_Department.Department =
TBL_Staff.Department) INNER JOIN (TBL_Courses INNER JOIN TBL_CoursesAttended
ON TBL_Courses.CourseName = TBL_CoursesAttended.Course) ON TBL_Staff.StaffID
= TBL_CoursesAttended.StaffID
GROUP BY Month([AttendDate]), ztbl_Department.Department;

which displays the Months as a number (1 = Jan etc), how do i (in the same
query) format the months to show in words - i've tried
Months: Format(Month([AttendDate]) ,"mmm")
but it doesn't work.

Cheers
JulieD
Click to expand...
Click to expand...
 
just a quick followup
how would i now get it to sort in month order not in alphabetical order?

regards
JulieD

JulieD said:
Hi Van

thanks so much! ... talk about not seeing the trees for the forrest!

Cheers
JulieD

Van T. Dinh said:
SELECT Format([AttendDate], "mmm") AS MonthName, ztbl_Department.Department,
Sum(TBL_Courses.CourseCost) AS SumOfCourseCost
FROM (ztbl_Department INNER JOIN TBL_Staff ON ztbl_Department.Department =
TBL_Staff.Department) INNER JOIN (TBL_Courses INNER JOIN TBL_CoursesAttended
ON TBL_Courses.CourseName = TBL_CoursesAttended.Course) ON TBL_Staff.StaffID
= TBL_CoursesAttended.StaffID
GROUP BY Format([AttendDate], "mmm")), ztbl_Department.Department;

HTH
Van T. Dinh
MVP (Access)


JulieD said:
hi all

i have a query - SQL view
SELECT Month([AttendDate]) AS MonthNumber, ztbl_Department.Department,
Sum(TBL_Courses.CourseCost) AS SumOfCourseCost
FROM (ztbl_Department INNER JOIN TBL_Staff ON
Click to expand...
Click to expand...
ztbl_Department.Department
=
TBL_Staff.Department) INNER JOIN (TBL_Courses INNER JOIN TBL_CoursesAttended
ON TBL_Courses.CourseName = TBL_CoursesAttended.Course) ON TBL_Staff.StaffID
= TBL_CoursesAttended.StaffID
GROUP BY Month([AttendDate]), ztbl_Department.Department;

which displays the Months as a number (1 = Jan etc), how do i (in the same
query) format the months to show in words - i've tried
Months: Format(Month([AttendDate]) ,"mmm")
but it doesn't work.

Cheers
JulieD
Click to expand...
Click to expand...
Click to expand...
 
just a quick followup
how would i now get it to sort in month order not in alphabetical order?

regards
JulieD

JulieD said:
Hi Van

thanks so much! ... talk about not seeing the trees for the forrest!

Cheers
JulieD

Van T. Dinh said:
SELECT Format([AttendDate], "mmm") AS MonthName, ztbl_Department.Department,
Sum(TBL_Courses.CourseCost) AS SumOfCourseCost
FROM (ztbl_Department INNER JOIN TBL_Staff ON ztbl_Department.Department =
TBL_Staff.Department) INNER JOIN (TBL_Courses INNER JOIN TBL_CoursesAttended
ON TBL_Courses.CourseName = TBL_CoursesAttended.Course) ON TBL_Staff.StaffID
= TBL_CoursesAttended.StaffID
GROUP BY Format([AttendDate], "mmm")), ztbl_Department.Department;

HTH
Van T. Dinh
MVP (Access)


hi all

i have a query - SQL view
SELECT Month([AttendDate]) AS MonthNumber, ztbl_Department.Department,
Sum(TBL_Courses.CourseCost) AS SumOfCourseCost
FROM (ztbl_Department INNER JOIN TBL_Staff ON
Click to expand...
ztbl_Department.Department
=
TBL_Staff.Department) INNER JOIN (TBL_Courses INNER JOIN
TBL_CoursesAttended
ON TBL_Courses.CourseName = TBL_CoursesAttended.Course) ON
TBL_Staff.StaffID
= TBL_CoursesAttended.StaffID
GROUP BY Month([AttendDate]), ztbl_Department.Department;

which displays the Months as a number (1 = Jan etc), how do i (in the same
query) format the months to show in words - i've tried
Months: Format(Month([AttendDate]) ,"mmm")
but it doesn't work.

Cheers
JulieD
Click to expand...
Click to expand...
Click to expand...

Order By [AttendDate];
 
Back
Top