• 0

[ACCESS] SQL tomfoolery


Question

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)

 employments (staffID, jobID)
	   jobdevices (jobID, deviceID)
	   devices (deviceID, deviceName)
	   assessments (staffID, deviceID, assessmentType)

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.

help......

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

Try getting rid of those parentheses.

This will NOT return rows where assessments are null

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
	LEFT JOIN assessments ON jobdevices.deviceID = assessments.deviceID
WHERE 
	employments.staffID = 111
	AND assessments.staffID = 111;

This WILL return rows where assessments can be null

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
	LEFT JOIN assessments ON jobdevices.deviceID = assessments.deviceID AND assessments.staffID = 111
WHERE 
	employments.staffID = 111;

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.