- (tblContractors): intEmpID, strLastName, strFirstName, (FK)strPosCode, (FK)intCCNumber, Active;
- (tblPositions):strPositionCode, strDescription;
- (tblCredentials) : strCredentialID, strDescription
- (tblLocation): intCCnum, strAbbreviation, strName;
- (tblContractorCredentials):(FK)intID, (FK)strCredentialID, dteExpires;
Those are the tables I need data from. I'm trying to rewrite the queries I have right now that have all these conditions in the WHERE clause and try to use INNER JOIN to simplify things a little.
What I put together after some strenuous thinking looks like this:
SELECT tblPositions.strDescription, tblLocation.strAbbreviation, tblContractors.strLastName, tblContractors.strFirstName, tblCredentials.Description, tblContractorCredentials.dteExpires
FROM tblLocation INNER JOIN (tblContractors INNER JOIN (tblCredentials INNER JOIN (tblContractorCredentials)
ON tblCredentials.strCredentialID = tblContractorCredentials.strCredentialID)
ON tblContractors.intEmpID = tblContractorCredentials.intID)
ON tblLocation.intCCnum = tblContractors.intCCnumber
WHERE tblPositions.strPositionCode = (Forms!frmCustomDataReport!CmbPosition) AND tblContractor.Active = Yes;
So basically, I want the query to show the full position name (tblPositions.strDescription), the abbreviation of the cost center (tblLocation.strAbbreviation), the contractor's last and first name, the full name of the credential listed for them (tblCredentials.description), and its expiration date. The twist is that all of this depends on the value the user has selected in a form control, the value for what position to report on.
I kind of have the habit of making things "more complicated than they need to be", but it just really sticks in my craw that I can't get this to work. Does anyone have any suggestions?