My guess is the problem is with '981382'. This number is in single quotes
which means it is being treated as a string instead of a number. I would
thing this is hours which need to be divided by 24 to get days or the single
quotes removed. O r move the single quotes around the whole statement

WHERE 'orders.id = 981382'



" wrote:

Hi
I'm having problems creating queries to MySQL database with MS Query.
For example the following query:

SELECT orders.id AS id,
hours.hourssum AS hourssum
FROM
orders LEFT JOIN
(SELECT job.id AS id, sum(job.hours1) AS hourssum FROM job GROUP BY
job.id) AS hours ON orders.id=hours.id
WHERE orders.id = '981382';

gives me an error: "Unknown column hours.hourssum in 'field list'".
Same query works in MySQL Query Browser just fine. I know that the
query can be done a bit differently so it works in MS Query, but this
query is just an example of the problem I'm having.

The problem is that MS Query doesn't doesn't recognize the subquery
alias "hours". Is there a way around this problem or does MS Query
have some kind of special syntax for these situations?