subquery help

[Maresdd]

I have created a query in my database using several tables. My table has a
list of shifts that an employee has done in a day. For each shift completed
there is a start time and an end time. I need to find the first start time
and the end time and work out the difference between the two in hours. So far
I have:

(SELECT (Max([EndTime])-Min([StartTime])*24)
FROM table as tb
WHERE table.emp = tb.emp AND table.servdate = tb.servdate)

I get an ERROR in the value.

Can somebody please tell me where I'm going wrong.

 

[John Spencer]

Try repositioning your parentheses.

The subquery appears to be syntactically correct but I think you want to do
the subtraction before you do the multiplication. Right now you are
multiplying Min(StartTime) by 24 and THEN subtracting that from Max(EndTime).

Access has a precedence for arithmetic operators. Multiplication and Division
take place before addition and subtraction. To force a different order you
need to use parentheses (Operations in parentheses occur first).

(SELECT ((Max([EndTime])-Min([StartTime]))* 24
FROM table as tb
WHERE table.emp = tb.emp AND table.servdate = tb.servdate)

IF that fails try to simplify the subquery and build it up a step at a time

(SELECT Max(EndTime) FROM Table as Tb)

(SELECT Max(EndTime)-Min(StartTime) FROM Table as Tb)

(SELECT Max(EndTime)-Min(StartTime) FROM Table as Tb WHERE table.emp = tb.emp)
Keep adding components until you get the error. Once you have the error
occur, you can start working on why the error is occuring.



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Maresdd]

thank you, I eventually got there.

Try repositioning your parentheses.

The subquery appears to be syntactically correct but I think you want to do
the subtraction before you do the multiplication. Right now you are
multiplying Min(StartTime) by 24 and THEN subtracting that from Max(EndTime).

Access has a precedence for arithmetic operators. Multiplication and Division
take place before addition and subtraction. To force a different order you
need to use parentheses (Operations in parentheses occur first).

(SELECT ((Max([EndTime])-Min([StartTime]))* 24
FROM table as tb
WHERE table.emp = tb.emp AND table.servdate = tb.servdate)

IF that fails try to simplify the subquery and build it up a step at a time

(SELECT Max(EndTime) FROM Table as Tb)

(SELECT Max(EndTime)-Min(StartTime) FROM Table as Tb)

(SELECT Max(EndTime)-Min(StartTime) FROM Table as Tb WHERE table.emp = tb.emp)
Keep adding components until you get the error. Once you have the error
occur, you can start working on why the error is occuring.



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I have created a query in my database using several tables. My table has a
list of shifts that an employee has done in a day. For each shift completed
there is a start time and an end time. I need to find the first start time
and the end time and work out the difference between the two in hours. So far
I have:

(SELECT (Max([EndTime])-Min([StartTime])*24)
FROM table as tb
WHERE table.emp = tb.emp AND table.servdate = tb.servdate)

I get an ERROR in the value.

Can somebody please tell me where I'm going wrong.

.