SQL Statement Error for Text Field

[needmoremoney]

Hello all I'm getting an error when I try to run this sql statement. The field "jobcode" is a text field. On the other hand, when I run the statement on a number field, the statement works fine. Any ideas, please let me know.

.....

SELECT EPayHist.id, EPayHistDetail.jobCode AS jobcodereg
INTO tempreport2
FROM (EPayHist
INNER JOIN EPayHistDetail
ON EPayHist.transaction = EPayHistDetail.transaction)
INNER JOIN CEarn
ON EPayHistDetail.detCode = CEarn.eCode
WHERE EPayHist.process >= [paramBeginProcess] AND
EPayHist.process <= [paramEndProcess] AND
EPayHistDetail.transactionType <> "X" AND
EPayHistDetail.det = "E" AND
CEarn.earnTypeCode = "Reg"
GROUP BY EPayHist.id;

UPDATE tempreport1
INNER JOIN tempreport2
ON tempreport1.id = tempreport2.id
SET tempreport1.jobcodereg = tempreport2.jobcodereg;

.....

 

[vongrunt]

Error... where? How it looks like?

And UPDATE syntax is Access-like...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[needmoremoney]

I'm running this through an AD-HOC command. It just says "Cannot process Query".

I have the created the below:

SELECT co, id, lastName, firstName,
0.0 AS currGross,
0.0 AS curr401kee,
0.0 AS curr401ker,
0.0 AS curr401kloan,
0.0 AS jobcodereg
INTO tempreport1
FROM EInfo;

 

[needmoremoney]

I see the error now:

"You tried to execute a query that doesn't include the specified expression 'jobcode' as part of an aggregate function."

Any help.. thanks..much..

 

[vongrunt]

The way SQL aggregates work, all columns specified in SELECT must be either aggregates (SUM, COUNT etc.) or also specified in GROUP BY.

You have EPayHist.id, EPayHistDetail.jobCode AS jobcodereg in SELECT and only EPayHist.id in GROUP BY.

<rant>
Btw. this forum is for M$ SQL Server, not Access
</rant>

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[needmoremoney]

Okay, I'll go post this in another forum.