Problem with Subquery

[toast]

Hello,

I have a continuous subform listing each employee which is based on a SQL statement, which changes depending upon the toggle switch located in the subform header. I have this working OK.

I now wish to add another field to the subform which will be based on a joined table. I am struggling to get the syntax correct for this subquery.

TblEmployee:
employeeID
employeeDept
employeeStatus

TblMovement:
movementID
employeeID (foreign key from TblEmployee)
movementDate

What would the correct syntax be for a subquery which finds the most recent movementDate in Tblmovement?
I have tried the following but it does not work:

(SELECT TOP 1 Temp.movementDate FROM TblMovement INNER JOIN TblEmployee ON TblMovement.[employeeID]
=TblEmployee.[employeeID] As Temp WHERE Temp.[employeeID] = TblMovement.[employeeID]
And Temp.[movementDate] <= Date() ORDER BY Temp.movementDate DESC, 
Temp.movementID) AS LastMove

Where the entire SQL statement would become:

SELECT TblEmployee.[employeeID], TblEmployee.[employeeDept], TblEmployee.[employeeStatus],
(SELECT TOP 1 Temp.movementDate FROM TblMovement INNER JOIN TblEmployee ON TblMovement.[employeeID]
=TblEmployee.[employeeID] As Temp WHERE Temp.[employeeID] = TblMovement.[employeeID]
And Temp.[movementDate] <= Date() ORDER BY Temp.[movementDate] DESC, Temp.[movementID]) AS LastMove
FROM TblEmployee, TblMovement WHERE (((TblEmployee.[employeeStatus])=1;

 

[vbaInet]

What would the correct syntax be for a subquery which finds the most recent movementDate in Tblmovement?

Here's how:

SELECT tblEmployee.* 
FROM tblEmployee 
WHERE tblEmployee.EmployeeID =  
    (
     SELECT TOP 1 tblMovement.EmployeeID
     FROM tblMovement 
     ORDER BY MovementDate DESC
    )
;

You can incorporate your other SQL into that.

 

[toast]

Many thanks for the reply. I still can't get that to work, but I am not sure I am approaching it correctly and I perhaps created confusion by oversimplifying my explanation thinking the only issue was the subquery. So I will take it back a step...

The subform record source was set in vba using the following SQL statement:

"SELECT tblEmployee.*
FROM tblEmployee
WHERE tblEmployee.employeeStatus=" & Me.OnOff & ";"

This worked to allow me to toggle between those employees who are "on" and those who are "off" (the value of employeeStatus).

I now want to add an extra control to the subform which will show me when they last came on duty if they are currently "on", or when they last went off duty if they are currently "off"... There is another field in tblMovement which specifies if each movement is an arrival or a departure.

The goal is therefore to be able to find the most recent arrival when employeeStatus = ON, and the most recent departure when employeeStatus = OFF, and insert that value into the subform control.

So I thought I needed to use a subquery to find the most recent movement by that employee (ignoring for the time being whether it was an arrival or departure). So the only change I have made to your code is add an extra ORDER BY to ensure that it only ever returns one record.

"SELECT tblEmployee.* 
FROM tblEmployee 
WHERE tblEmployee.employeeStatus=" & Me.OnOff & "
AND tblEmployee.EmployeeID =  
    (
     SELECT TOP 1 tblMovement.EmployeeID
     FROM tblMovement 
     ORDER BY MovementDate DESC, movementID
    )
;"

Now I don't get any errors, but I'm getting only one result for ON and none for OFF when previously I was (correctly) getting many.

But what I now am thinking is that the subquery shouldn't be run at the form's record source, but as the control source on the textbox...?

 

[vbaInet]

Since you're using code to build your sql statement I'm sure you fully understand how to concatenate, so here:

"SELECT tblEmployee.* 
FROM tblEmployee 
WHERE tblEmployee.employeeStatus=" & Me.OnOff & "
AND tblEmployee.EmployeeID =  
    (
     SELECT TOP 1 tblMovement.EmployeeID
     FROM tblMovement 
     WHERE tblMovement.[COLOR=Red]MovementStatus[/COLOR] = " & IIF(Me.OnOff = "on", "arrival", "departure") & 
     ORDER BY MovementDate DESC
    )"

 

[toast]

Thank you once again for the prompt reply. It is still not working, I'm afraid - it returns 1 record (no errors).

I decided to strip the SQL statement down to see where it is going wrong. I removed the criteria to differentiate between ON/OFF and between arrival/departure movements:

"SELECT tblEmployee.* 
FROM tblEmployee 
WHERE tblEmployee.EmployeeID =  
    (
     SELECT TOP 1 tblMovement.EmployeeID
     FROM tblMovement 
     ORDER BY MovementDate DESC
    )"

My understanding is that this should show me a complete list of every employee, whether on shift or off shift. But it doesn't - it only produces 1 record.

 

[vbaInet]

Hmmm... I've just re-read your thread and I think you mean you want the latest MovementDate per employee right?

If that's the case then here:

SELECT tblMovement.*
FROM tblMovement 
WHERE tblMovement.MovementID IN 
    (
     SELECT TOP 1 M.MovementID 
     FROM tblMovement AS M 
     WHERE M.EmployeeID = tblMovement.EmployeeID
     ORDER BY M.MovementDate DESC
    )

 

[toast]

Yes, the subform shows each employee's details, and I wanted to add their latest movement to the subform.

With your help, I have managed to get it working - I just needed to add a join between the 2 tables to the statement you gave:

SELECT tblEmployee.*, tblMovement.*
FROM tblEmployee INNER JOIN tblMovement ON tblEmployee.employeeID = tblMovement.employeeID
WHERE Tmovements.FmovementID In
 	(
	  SELECT TOP 1 M.MovementID
	  FROM tblMovement AS M
	  WHERE M.employeeID = tblMovement.employeeID
	  ORDER BY M.MovementDate DESC
	 );

Thank you for your patience and time!

 

[vbaInet]

Good job.

You're welcome!

 

[toast]

One more thing to iron out, I'm afraid...

At the moment it excludes anyone who does not yet have an entry in tblMovement... how would I go about including those people (albeit with a blank entry for their last movement date)?

Sorry to trouble you once again!

 

[vbaInet]

INNER JOIN to LEFT JOIN.

 

[toast]

Hello,

Thank you for the reply.
My understanding is that the LEFT JOIN should do the job, but it doesn't (no errors, just still returns the same result).

I've been reading around and I think it is to do with needing to move some of the criteria from the WHERE clause to the JOIN clause, but I am afraid that I can't figure out what I need to move.

The code as it stands now is:

SELECT tblEmployee.*, tblMovement.*
FROM tblEmployee LEFT JOIN tblMovement ON tblEmployee.employeeID = tblMovement.employeeID
WHERE tblEmployee.EmployeeStatus = [i]Me.OnOff[/i]
    AND Tmovements.FmovementID In
 	(
	  SELECT TOP 1 M.MovementID
	  FROM tblMovement AS M
	  WHERE M.Movementtype = [i]IIF(Me.OnOff = True, 2,4)[/i]
             AND M.EmployeeID = tblMovement.employeeID
	  ORDER BY M.MovementDate DESC
	 );

 

[vbaInet]

Remember you have another criteria set. Did it work before you added the EmployeeStatus criteria? You probably also need Is Null somewhere.

 

[toast]

I think you are right with the IsNull idea...

The code above will only generate results for Employees who have an entry in the Movements table; if there are no entries at all in the movements table which relate to that employee then they are being excluded.

If add the following:

SELECT tblEmployee.*, tblMovement.*
FROM tblEmployee LEFT JOIN tblMovement ON tblEmployee.employeeID = tblMovement.employeeID
WHERE tblEmployee.EmployeeStatus = Me.OnOff
    AND [b](IsNULL(Tmovements.FmovementID) OR [/b]Tmovements.FmovementID In
 	(
	  SELECT TOP 1 M.MovementID
	  FROM tblMovement AS M
	  WHERE M.Movementtype = IIF(Me.OnOff = True, 2,4)
             AND M.EmployeeID = tblMovement.employeeID
	  ORDER BY M.MovementDate DESC
	 )[b])[/b];

It will show all employees if there are either:
1. No entries in the Movement table; or
2. At least one entry in the Movement table in the right direction (i.e. if they are off-shift there must be at least one departure movement in the movement table).
But it completely excludes anyone who only has movements in the wrong direction for their current status (i.e. they are currently off-shift and the only entries in the movements table are for arrivals).

I've been playing around with this, and I think it is to do with the positioning of the IsNULL criteria but I can't seem to find the right spot to fix the issue.

 

[vbaInet]

Not IsNull() function, I meant Is Null statement. Like I previously asked you, what happens when you remove the extra EmployeeStatus criteria you added?

 

[toast]

Sorry, I meant to say - if I remove the tblEmployee.EmployeeStatus = Me.OnOff I get the same behaviour as before: if there are no entries in the movements table the query excludes them (and obviously the list remains the same irrespective of the Me.OnOff toggle).

SELECT tblEmployee.*, tblMovement.*
FROM tblEmployee LEFT JOIN tblMovement ON tblEmployee.employeeID = tblMovement.employeeID
WHERE Tmovements.FmovementID In
 	(
	  SELECT TOP 1 M.MovementID
	  FROM tblMovement AS M
	  WHERE M.Movementtype = IIF(Me.OnOff = True, 2,4)
             AND M.EmployeeID = tblMovement.employeeID
	  ORDER BY M.MovementDate DESC
	 );

 

[vbaInet]

So use only this:

SELECT tblEmployee.*, tblMovement.*
FROM tblEmployee LEFT JOIN tblMovement ON tblEmployee.employeeID = tblMovement.employeeID

Can you some Null values under tblMovement. Do you now see where to put the OR Is Null criteria?

 

[toast]

Sorry, I'm sure I'm being obtuse, but I know what the query needs to ask for but I can't get the syntax.

The WHERE criteria is:

Same pilot status: tblEmployee.EmployeeStatus = Me.OnOff

And

Either

-Top result if an entry in movement table exists: SELECT TOP 1 M.MovementID FROM tblMovement AS M WHERE M.Movementtype = IIF(Me.OnOff = True, 2,4) AND M.EmployeeID = tblMovement.employeeID ORDER BY M.MovementDate DESC

Or

-there is no entry in the movement table corresponding to the right movementtype... ?! OR (movementtype = IIF(Me.OnOff = True, 2,4) AND M.EmployeeID = tblMovement.employeeID) IS NULL

But that still doesn't change anything? Should I be using a NOT EXISTS criteria?

 

[vbaInet]

What did you get with the query I posted in my last post? Did you see some Null entries that don't correspond to tblMovements table?

 

[toast]

I'm sorry, I don't follow re the null value that "Null entries that don't correspond to tblMovements table"

I get 1 entry per employee if the employee has either (i) no entries in movement table, or (ii) 1 entry in the movement table.

There are multiple entries per employee if there is more than 1 entry in the movement table.

If I add an entry into the movement table which doesn't correspond to any employee (ie the employeeID is blank) that does not show up

 

[vbaInet]

So are you saying that ALL employees have at least one Movement record?