Selecting a new calculated field in same query

[Hallel86]

Hi everyone, extremely new to SQL.
Okay, basically. I want to calculate a field that is Sales*6+Salary, then in that same query I want to select ONLY the greatest Salary per employee. So for example

SELECT EmpId, MonthDate, Sales, Salary, [Sales]*6+[Salary] AS SalTot
FROM EmpTable S1
WHERE SalTot = (SELECT MAX(SalTot) FROM EmpTable S2 WHERE S1.EmpId = S2.EmpId);

Can I not select a value that has been calculated this query? What am I doing wrong here?

 

[plog]

Can you post some sample data from your table and then what the results should be based on that sample data? Include table and field names and use this format:

TableNameHere
Field1Name, Field2Name, Field3Name, ...
17, 5/12/2010, David
92, 6/8/2007, Steve
21, 4/1/2009, Larry

 

[CJ_London]

Try

SELECT EmpId, MonthDate, Sales, Salary, [Sales]*6+[Salary] AS SalTot
FROM EmpTable S1
WHERE SalTot = (SELECT [COLOR=red]MAX([Sales]*6+[Salary])[/COLOR] FROM EmpTable S2 WHERE S1.EmpId = S2.EmpId);

 

[programmingmiss]

i WANT TO CREATE A NEW POST ABOUT A CALCULATED FIELD IN QUERY, BUT i CANT LOCATE THAT....READ SIMILAR POSTS.

I WANT TO DO A SIMPLE THING ADD 2 VALUES (BOTH NUMBER TYPE) AND FROM SAME TABLE

IN DESIGN VIEW IN QUERY I DEFINED SUM: [VARIABLEA]+ [VARIABLEB], BUT i GET AN OVERFLOW ERROR, WHICH IS THINK IS A TYPE CLASH. CAN YOU ASSIST OR AT LEAST DIRECT ME TO WHERE I CANPOST ?. THX!!!

 

[CJ_London]

You need to click on one of the options under Microsoft Access Help to the top left of the page. Then select New Thread to the left of the page - this is so the forum knows under which topic you want to cover.

Also, it is bad form to use CAPS in the way you have, please do not do so going forward or you will get many complaints.

Re your question Sum is a reserved word so this is almost certainly the reason for your problem - suggest change it to Total or other word.

Here is a link to a list of reserved words

http://support.microsoft.com/kb/286335

 

[programmingmiss]

C J London;

Thank you for the orientation and I had the Caps on b/c of access; I forgot it/'s like shouting. Also, I tried other words for calculated variable like sumofprodab, but I'll keep an eye out for reserved words. A 1,000 THANKS (caps intentional this time). ;-)