LEVEL clause error 3

[mThomas]

I have the following query which runs fine without the GROUP BY clause.

SELECT IIf(J.PMT_JOB_STATUS='EXEMPT','EXEMPT','NOT-EXEMPT') AS [Employee Status], 
E.PMT_EMP_NAME AS [Employee Name], E.PMT_EMP_SALARY AS [Employee Salary]
FROM PMT_JOBCODE_TABLE AS J, PMT_EMPLOYEE_TABLE AS E
WHERE J.PMT_JOB_TITLE_CODE=E.PMT_JOB_TITLE_CODE;
GROUP BY E.PMT_EMP_SALARY;

However when I add the GROUP BY clause, it returns the following error:

"The LEVEL clause includes a reserved word or argument that is misspelled or missing, or the punctuation in incorrect."

Any ideas on what I'm doing wrong?

tia...mike

 

[dhookom]

I see two ";"s in the sql. Try"

SELECT IIf(J.PMT_JOB_STATUS='EXEMPT','EXEMPT','NOT-EXEMPT') AS [Employee Status], 
E.PMT_EMP_NAME AS [Employee Name], E.PMT_EMP_SALARY AS [Employee Salary]
FROM PMT_JOBCODE_TABLE AS J, PMT_EMPLOYEE_TABLE AS E
WHERE J.PMT_JOB_TITLE_CODE=E.PMT_JOB_TITLE_CODE
GROUP BY E.PMT_EMP_SALARY;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mThomas]

Hi dhookom,

Thanks for the response. The extra ; was a typo on my part. Access will not let me save the query with the GROUP BY clause as it returns an error. So I have been deleting it (the GROUP BY clause) after trying to get the query to run with the GROUP BY clause and left the errant ; when I typed it in to paste as my example.

Interestingly enough though, for some reason, now when I run the query, minus the extra ; I get the following error:

"You tried to execute a query that does not include the specified expression 'IIf(J.PMT_JOB_STATUS='EXEMPT','EXEMPT','NOT-EXEMPT')' as part of an aggregate function."

Here is my code, minus the extra ;

SELECT IIf(J.PMT_JOB_STATUS='EXEMPT','EXEMPT','NOT-EXEMPT') AS [Employee Status], 
E.PMT_EMP_NAME AS [Employee Name], E.PMT_EMP_SALARY AS [Employee Salary]
FROM PMT_JOBCODE_TABLE AS J, PMT_EMPLOYEE_TABLE AS E
WHERE J.PMT_JOB_TITLE_CODE=E.PMT_JOB_TITLE_CODE
GROUP BY E.PMT_EMP_SALARY;

tia...mike

 

[dhookom]

That's a very good error message. You would need to group by:

GROUP BY IIf(J.PMT_JOB_STATUS='EXEMPT','EXEMPT','NOT-EXEMPT'), 
E.PMT_EMP_NAME, E.PMT_EMP_SALARY

Is there a specific reason you didn't?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

and you don't normally need a group by clause if you're not using an aggregate function (like SUM, COUNT, AVG, etc.). What are you trying to accomplish with the GROUP BY?

Leslie

 

[mThomas]

Thanks dhookom and lespaul,

dhookom, your suggestion worked like a charm. The reason I'm wanting to group the employees by salary within job code within job status. The job code is in the PMT_JOBCODE_TABLE. Here is my GROUP BY clause

GROUP BY IIf(J.PMT_JOB_STATUS='EXEMPT','EXEMPT','NOT-EXEMPT'),
E.PMT_JOB_TITLE_CODE, E.PMT_EMP_SALARY, E.PMT_EMP_NAME;

mike

 

[PHV]

As you don't use any aggregate function you may try this:
SELECT IIf(J.PMT_JOB_STATUS='EXEMPT','EXEMPT','NOT-EXEMPT') AS [Employee Status],
E.PMT_JOB_TITLE_CODE AS [Job Code], E.PMT_EMP_SALARY AS [Employee Salary], E.PMT_EMP_NAME AS [Employee Name]
FROM PMT_JOBCODE_TABLE AS J, PMT_EMPLOYEE_TABLE AS E
WHERE J.PMT_JOB_TITLE_CODE=E.PMT_JOB_TITLE_CODE
ORDER BY 1,2,3,4;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mThomas]

Hi PHV,

I was wondering why I was getting an aggregate error when I wasn't using an aggregate function, like (SUM, AVG etc...) I like your suggestion, though I kept the job code out of the results table as an exercise in grouping with columns that would not be returned in the results set.

I've been playing with MySQL and Access while reading Sams Teach Yourself SQL in 24 hours. I've found the book to be helpful, though a lot/some of what it covers is not implmented in Access or MySQL.