Group by process (Scriver? Anyone?) 2

[DesertTrip]

Scriver was extremely patient last month and taught me alot about grouping like records from one field of a query. I did great on my own for the next 5 queries that required this action. But now, I am stumped with my latest one. It keeps telling me that I am having a Syntax error and when I close the error window, the word "BY" is highlighted and flashing.

The error states: Syntax Error (missing operator) in query expression 'GROUP BY Format$([tblCaseData].[Date],"m/yy&quot'

Why? What am I missing in this SQL statement? I have compared it to my working statements in the other queries and can't find any formatting differences. Was I just lucky the others worked?

My Mess:

SELECT DISTINCTROW Format$([tblCaseData].[Date],"m/yy&quot AS DateByMonth, [tblCaseData]!

 AS Activity, tblCaseData.EmployeeNumber, Count(*) AS Count, 
Sum([tblCaseData]![OPAmt]) AS OPAmt, 
Sum([tblCaseData]![OIAmt]) AS OIAmt, 
GROUP BY Format$([tblCaseData].[Date],"m/yy"), [tblCaseData]![Code], 
HAVING (((Format$([tblCaseData].[Date],"m/yy"))=[Enter MonthYear as m/yy]) AND ((tblCaseData.EmployeeNumber)=[Enter Employee Number]));
[/color]

 

[Steve101]

There are several errors in your SQL syntax above, as hilited below:

SELECT DISTINCTROW Format$([tblCaseData].[Date],"m/yy&quot AS DateByMonth, [tblCaseData]! AS Activity, tblCaseData.EmployeeNumber, Count(*) AS Count,
Sum([tblCaseData]![OPAmt]) AS OPAmt,
Sum([tblCaseData]![OIAmt]) AS OIAmt,
GROUP BY Format$([tblCaseData].[Date],"m/yy&quot, [tblCaseData]!,
HAVING (((Format$([tblCaseData].[Date],"m/yy&quot)=[Enter MonthYear as m/yy]) AND ((tblCaseData.EmployeeNumber)=[Enter Employee Number]));

The SQL below represents corrections made to the above SQL:

SELECT DISTINCTROW Format$([tblCaseData].[Date],"m/yy&quot AS DateByMonth, [tblCaseData].Activity AS Activity, tblCaseData.EmployeeNumber, Count(*) AS Count,
Sum([tblCaseData].[OPAmt]) AS OPAmt,
Sum([tblCaseData].[OIAmt]) AS OIAmt
FROM tblCaseData
GROUP BY Format$([tblCaseData].[Date],"m/yy&quot, [tblCaseData].Activity
HAVING (((Format$([tblCaseData].[Date],"m/yy&quot)=[Enter MonthYear as m/yy]) AND ((tblCaseData.EmployeeNumber)=[Enter Employee Number]));

Note that you have used the ! symbol incorrectly; it has no role to play within SQL syntax. Where you've used it, you need to replace it with a dot and the valid table fieldname (I've called it Activity, though this may be incorrect).

You've also missed the 'From' Clause, which I've added. Also some minor corrections to the punctuation (eg. extra commas).

Finally note, that since only one table is involved in this SQL, that it can be somewhat simplified, as each field does NOT have to be qualified by the tablename. The SQL thus reduces to this:

SELECT DISTINCTROW Format$([Date],"m/yy&quot AS DateByMonth,
Activity, EmployeeNumber,
Count(*) AS Count,
Sum([OPAmt]) AS OPAmt,
Sum([OIAmt]) AS OIAmt
FROM tblCaseData
GROUP BY Format$([Date],"m/yy&quot, Activity
HAVING (((Format$([Date],"m/yy&quot)=[Enter MonthYear as m/yy])
AND ((EmployeeNumber)=[Enter Employee Number]));

By the way, its not a good idea to use Date as a field name in a table, as Date is a reserved word in Access (ie. it is a data type AND it is a function), and this can cause ambiguities and problems. However, the fact that its included in square brackets above, should insulate you from these problems.

Hope this helps,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[MichaelRed]

Steve Lewy,

Kudos (and a Star) for your patience.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[DesertTrip]

Oh dear! I see that missing field now and as MichaelRed stated, kudos for your patience. I appreciate it greatly when anyone takes the time to me in my Access education.

I will make the corrections and try again this afternoon after I get back from an out of town meeting.

Thank you;
Leia

 

[Steve101]

Leia,

Correction to my above post, in order to make it work:

SELECT DISTINCTROW Format$([Date],"m/yy&quot AS DateByMonth,

,
 [EmployeeNumber],
           Count(*) AS [Count],
           Sum(tblCaseData.OPAmt) AS OPAmt, Sum(tblCaseData.OIAmt) AS OIAmt
    FROM tblCaseData
    GROUP BY [EmployeeNumber], Format$([Date],"m/yy"), [Code]
    HAVING (((Format$([Date],"m/yy"))=[Enter MonthYear as m/yy])
    AND    ((EmployeeNumber)=[Enter Employee Number]));

Note the inclusion of the employeeNumber in the GROUPBY clause, and the qualifiers required to the two fields in the Sum(...) expressions, required because the same names are used in the AS parts of the SELECT expressions. This will hopefully allow the statement to work.

Cheers,

 Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Steve101]

Oh horror of horrors I've really messed up above. Please add the word Code[/blue] between the two commas on the top line. In my last post its been misinterpreted as Process TGML (shock horror !!), and disappeared.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[DesertTrip]

It works!

You get a vote & star from me also, for your patience and assistance! Your help was much appreciated and quite informative.

Leia/DesertTrip