This content has been marked as final. Show 3 replies
At least one of your problems is here:
INNER JOIN appoint_table
ON ServerSMS <= #CreateODBCDateTime(Now())# OR ServerSMS <> ''
The first part suggests ServerSMS is a timestamp or datetime field and the second part suggests that it is text. At least one of those is wrong.
Wouldn't it be simpler to do something like this for suitable columns W, X, Y, Z, etc. ?
While one problem is as Dan said, another is
INNER JOIN (SELECT SUM(Cost)as TotalCost FROM SMS_Records) ON SMS_Records.ClubID = SMS_Clubs_Table.ClubID
INNER JOIN (SELECT SUM(Cost)as TotalCost, ClubID FROM SMS_Records GROUP BY ClubID) As subTable ON subTable.ClubID = SMS_Clubs_Table.ClubID
Note that you need to alias the sub select to join to the SMS_Clubs table, and because you need the clubid to join you have to return this in the select clause, thus you then have to group the SUM by the clubid.