Problem with Subquery

[toast]

No, this is the problem. They may have none, or some, or many entries in the movement table.

But I need this query to return a list of every employee, irrespective of whether they have any entries in the movement table. But if they do have entries in the movement table, I want to display the latest entry too.

 

[vbaInet]

And have you tested the SQL statement I wrote in my post #16?

 

[toast]

Yes, this is what I get when I use

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

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

Multiple entries per employee if there is more than 1 entry in the movement table - each entry corresponding to each record in the movements table sharing the same employeeID.

Essentially, just what I would expect from an outer join.

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]

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.

I don't understand this kind of relationship Don't you have referential integrity set?

 

[toast]

Yes, it is a one to many join on tblEmployee to tblMovement

tblEmployee
EmployeeID (primary key)

tblMovement
MovementID (primary key)
EmployeeID (foreign key)

So there can in principle be an entry in tblMovement which does not have an employee allocated to it (this is prevented in the forms, but I am testing the code from the table level and I thought you wanted me to see what happens in that scenario?)

 

[vbaInet]

There's something going wrong somewhere. With the kind of relationship you've set up and with referential integrity set, you shouldn't be able to have an anomaly in tblMovement. Unless you are speculating that you can do it without trying it.

 

[toast]

No speculation, I have done it... but it behaves exactly as I'd expect it to

The only requirement in tblMovement is that it has a unique primary key. Whether a value is entered for the foreign key is surely irrelevant to maintaining referential integrity?
If I delete the employee, any entries in tblMovement sharing that employeeID will be deleted if I set cascade delete, and if I change the employeeID it will update in tblMovement if I cascade update.

I'm not sure why that is incorrect table design? I could set the foreign key to required, and then it would behave as you describe.

 

[vbaInet]

The only requirement in tblMovement is that it has a unique primary key. Whether a value is entered for the foreign key is surely irrelevant to maintaining referential integrity?
If I delete the employee, any entries in tblMovement sharing that employeeID will be deleted if I set cascade delete, and if I change the employeeID it will update in tblMovement if I cascade update.

I understood what I was referencing in my post #24 to mean that you were able to enter an EmployeeID in the Movements table that didn't correspond to an EmployeeID in the Employees table. But after re-reading it, I get your point.

So, upload a stripped down version of your db and I'll have a quick look.

 

[toast]

Thank you very much for the offer - as soon as I get the opportunity to strip it down that would be wonderful.