[atsukoarai86]

Hi everyone, I'm a little stumped. I have a database at work that tracks independent contractor credentials and their expiry dates. Using MS Access 2003, I have several tables that I need to query data from, which are as follows:

• (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?

[atsukoarai86]

Just wanted to post and say I fixed it myself. I realized there was too much data to link the joins up correctly, so I just made two base queries and joined those, like so:

Base Query 1:
In the first query, I grab the position description, its identifier, the cost center abbreviation, employee ID, and the contractor first and last name.
Then I was able to perform two join the contractors table and locations table via the cost center number key, and the positions table and contractors table on the position code key. This query also filters out inactive employees.

SELECT tblPositions.strDescription AS [Positions], tblPositions.strPositionCode AS [PosCode], tblLocation.strAbbreviation AS [Site], tblContractors.intEmpID AS [EmpID], tblContractors.strFirstName AS [First Name], tblContractors.strLastName AS [Last Name]
FROM tblPositions INNER JOIN (tblContractors INNER JOIN (tblLocation)
ON tblContractors.intCCnumber = tblLocation.intCCnum)
ON tblPositions.strPositionCode = tblContractors.strPosCode
WHERE tblContractors.Active = Yes
ORDER BY tblPositions.strDescription;

Base Query 2
The second query was a bit more simple, performing only one join between the credentials table and contractor credentials table key field, grabbing only the employee ID, credential ID, expiration date, and credential description.

SELECT B.intID, B.strCredentialID, B.dteExpires, A.strDescription AS [Credential]
FROM tblCredentials AS A INNER JOIN tblContractorCredentials AS B
ON B.strCredentialID = A.strCredentialID;

New Query:
For the new nested query, I can pull the full position name, the site abbreviation, contractor first/last name, the full credential name, and its expiration date, joining the two base queries on just one field, the employee ID, simplfying the WHERE clause to select records matching the user-selected form control value.

SELECT A.[Positions], A.Site, A.[First Name], A.[Last Name], B.Credential, B.dteExpires AS [Expires]
FROM AllbyPosCode_BaseQuery AS A INNER JOIN All_BaseQuery2 AS B
ON A.EmpID = B.intID
WHERE A.PosCode = (Forms!frmCustomDataReport!cmbPosition)
ORDER BY A.Positions, A.Site, A.[Last Name], B.Credential;

So now, instead of a mile-long WHERE clause, I have a very simple INNER JOIN, with a very simple WHERE clause.

If there are any comments or questions on what I've done, I'd be happy to hear them.

[Hary]

Personally, I would go for the single query instead of the two last ones. It's not too important a query is well readable for you, but it has to be for the DBMS. And that one does not care about long queries. The main advantage of one query is that the DBMS is able to optimise the search plan for everything, and (re)using indices on tables hit.

[rdai]

Not to disagree with Hary, personally I'd prefer smaller clearer queries as long as it doesn't hurt the performance too much.

I inherited a datawarehouse built by this guy that featured nested queries that are 10 levels deep with 0 documentation. That is not fun. So if it worked for you, more power to ya!

This post has been edited by rdai: 17 February 2009 - 08:36 PM

[baavgai]

I also prefer to break down larger problems into smaller ones. A sub query is required for things like joins with group bys. However, this one seems simple enough, unless I'm missing something.

SELECT pos.strDescription AS [Positions], 
		loc.strAbbreviation AS [Site], 
		c.strFirstName AS [First Name], 
		c.strLastName AS [Last Name],
		cr.strDescription AS [Credential],
		ccr.dteExpires AS [Expires]
	FROM tblPositions pos
		INNER JOIN tblContractors c 
			ON pos.strPositionCode = c.strPosCode
				AND c.Active = Yes
		INNER JOIN tblLocation loc
			ON c.intCCnumber = loc.intCCnum
		INNER JOIN tblContractorCredentials ccr
			ON c.intEmpID = ccr.intID
		INNER JOIN tblCredentials cr
			ON ccr.strCredentialID = cr.strCredentialID

I would highly recommend using a consistent formating style for your queries. Hope this helps.

[Hary]

I do agree it is more readable, but I do prefer a combination, a well documented single query. Inline comments remove the need to issue two single queries, but you keep the single query the DBMS can optimize.

For DW performance this is one of the main issues. You can buy more hardware, but a smart programmer is way cheaper... However, you nedver want a hacker (i.e. without comments/docs)