what i am trying to do, is get a list of jobdevices that relate to a member of staff, and get any assessment data:
SELECT device.deviceID, device.deviceName, assessments.assessmentType
FROM ((employments
INNER JOIN jobdevices ON employments.jobID = jobdevices.jobID)
INNER JOIN device ON jobdevices.deviceID = devices.deviceID)
WHERE employments.staffID = 111;
When i put the assessments table in (LEFT JOIN assessments ON jobdevices.deviceID = assessments.deviceID)
it works fine, now i want assessments only by our guy adding AND assessments.staffID = 111 the the end of the join wont work in access (ive tried everyway i can find) nor does adding stuff onto the WHERE clause.
best i get is the jobdevices that have been assessed by the guy or not assessed at all. the devices that have been assessed on (by other people) just vanish into the ether.
Question
Ricky Baby
this is in access 2000, i cant use a more update version :/ and i can't redevelp into vba.net as a standalone application due to time constraints.
Okie i have the following tables: (few more fields but not part of the issue)
what i am trying to do, is get a list of jobdevices that relate to a member of staff, and get any assessment data:
When i put the assessments table in (LEFT JOIN assessments ON jobdevices.deviceID = assessments.deviceID)
it works fine, now i want assessments only by our guy adding AND assessments.staffID = 111 the the end of the join wont work in access (ive tried everyway i can find) nor does adding stuff onto the WHERE clause.
best i get is the jobdevices that have been assessed by the guy or not assessed at all. the devices that have been assessed on (by other people) just vanish into the ether.
help......
Link to comment
Share on other sites
2 answers to this question
Recommended Posts