Syntax error (group by)

[weihann.ttsh]

Hi All,

I am running out of idea now
below are the scenario, i am trying to get a FY (financial year) from another table definition based on the posting_start_date in the main table.

Below are the sql statement that is working before including the FY scenario:
------------------------------------------
SELECT Clinical_Staff_Feedback.Hospital, Clinical_Staff_Feedback.Posting_Name, Clinical_Staff_Feedback.Clinical_Group, Clinical_Staff_Feedback.Staff_Name, Count(Clinical_Staff_Feedback.Q7) AS [No of Votes], (select count(*) from Clinical_Staff_Feedback c1 where
c1.Hospital=Clinical_Staff_Feedback.Hospital and
c1.Posting_Name=Clinical_Staff_Feedback.Posting_Na me and
c1.Clinical_Group=Clinical_Staff_Feedback.Clinical _Group and
c1.Staff_Name=Clinical_Staff_Feedback.Staff_Name and
Year(c1.Posting_Start_Date)=Year(Clinical_Staff_Fe edback.Posting_Start_Date)
) AS totalcount, ([No of Votes]*100/totalcount) AS Perc, Year(Clinical_Staff_Feedback.Posting_Start_Date) AS [Posting Year]
FROM Clinical_Staff_Feedback
WHERE (((Clinical_Staff_Feedback.Q7)="Yes"))
GROUP BY Clinical_Staff_Feedback.Hospital, Clinical_Staff_Feedback.Posting_Name, Clinical_Staff_Feedback.Clinical_Group, Clinical_Staff_Feedback.Staff_Name, Year(Clinical_Staff_Feedback.Posting_Start_Date)


ORDER BY Count(Clinical_Staff_Feedback.Q7) DESC;
------------------------------------------------

Once i am trying to link the 2 tables, by adding "(select FY from fy_definition where (Clinical_Staff_Feedback.Posting_Start_Date>=start _date) and (Clinical_Staff_Feedback.Posting_Start_Date<=end_d ate))" i keep getting syntax error I did try to do it from a simpler table and it works...

below is the workable query (without group by):
SELECT Clinical_Staff_Feedback.Hospital, Clinical_Staff_Feedback.Staff_Name, Clinical_Staff_Feedback.Teacher_Strength_Comment, Clinical_Staff_Feedback.Suggestion_For_Improvement _Comment, Clinical_Staff_Feedback.Other_Comment, (select FY from fy_definition where Clinical_Staff_Feedback.Posting_Start_Date>=start_ date and Clinical_Staff_Feedback.Posting_Start_Date<=end_da te) AS [Posting Year]
FROM Clinical_Staff_Feedback
WHERE (((Trim([Clinical_Staff_Feedback].[Teacher_Strength_Comment]))<>'')) OR (((Trim([Clinical_Staff_Feedback].[Suggestion_For_Improvement_Comment]))<>'')) OR (((Trim([Clinical_Staff_Feedback].[Other_Comment]))<>''))
ORDER BY Clinical_Staff_Feedback.Staff_Name;

syntax error query
SELECT Clinical_Staff_Feedback.Hospital, Clinical_Staff_Feedback.Posting_Name, Clinical_Staff_Feedback.Clinical_Group, Clinical_Staff_Feedback.Staff_Name, Count(Clinical_Staff_Feedback.Q7) AS [No of Votes], (select count(*) from Clinical_Staff_Feedback c1 where
c1.Hospital=Clinical_Staff_Feedback.Hospital and
c1.Posting_Name=Clinical_Staff_Feedback.Posting_Na me and
c1.Clinical_Group=Clinical_Staff_Feedback.Clinical _Group and
c1.Staff_Name=Clinical_Staff_Feedback.Staff_Name and
Year(c1.Posting_Start_Date)=Year(Clinical_Staff_Fe edback.Posting_Start_Date)
) AS totalcount, ([No of Votes]*100/totalcount) AS Perc, Year(Clinical_Staff_Feedback.Posting_Start_Date) AS [Posting Year],
(select FY from fy_definition where (Clinical_Staff_Feedback.Posting_Start_Date>=start _date) and (Clinical_Staff_Feedback.Posting_Start_Date<=end_d ate))
FROM Clinical_Staff_Feedback
WHERE (((Clinical_Staff_Feedback.Q7)="Yes"))
GROUP BY Clinical_Staff_Feedback.Hospital, Clinical_Staff_Feedback.Posting_Name, Clinical_Staff_Feedback.Clinical_Group, Clinical_Staff_Feedback.Staff_Name, Year(Clinical_Staff_Feedback.Posting_Start_Date),( select FY from fy_definition where (Clinical_Staff_Feedback.Posting_Start_Date>=start _date) and (Clinical_Staff_Feedback.Posting_Start_Date<=end_d ate))
ORDER BY Count(Clinical_Staff_Feedback.Q7) DESC;


I am really out of idea what is happening any help will be greatly appreciated. Thanks!

[June7]

FY is not a field in the source table. How can records be grouped on a field that doesn't exist? The nested SELECT FY in the GROUP BY clause won't work. This data is unrelated.

Would have to JOIN fy_definition to Clinical_Staff_Feedback. What would be the linking fields?