Giving an alias to a subquery containing a join in access

Question

I am more experienced with SQL server's T-SQL but i have been working in Access. My question is how to give an alias to a sub query that has its own join operation. I believe i am talking about nested join operations. I know that i could create a seprate query but i dont have another need for it and would like to keep the object list as clean as possible. My current code is as follows, i am only showing the from statement as that is the source of the error:

FROM 
(
  (
    tblPropertySpecs 
    INNER JOIN 
    (
      tblAssignedBuildingTypes 
      INNER JOIN qryAllPropertyIDs 
      ON tblAssignedBuildingTypes.PropertySpecID = qryAllPropertyIDs.PropertySpecID
    ) 
    ON (tblPropertySpecs.PropertySpecID = tblAssignedBuildingTypes.PropertySpecID) 
    AND (tblPropertySpecs.PropertySpecID = qryAllPropertyIDs.PropertySpecID)
  ) 
  INNER JOIN 
  (
    tblRefConstructionTypes 
    INNER JOIN tblAssignedConstructionTypes 
      ON tblRefConstructionTypes.ConstructionTypeID = tblAssignedConstructionTypes.ConstructionTypeID
  ) 
  ON tblAssignedBuildingTypes.AssignedBuildingTypeID = tblAssignedConstructionTypes.AssignedBuildingTypeID
) 
LEFT JOIN 
(
  tblRefFireSafetyDetectMethods 
  INNER JOIN tblAssignedFireSafetyDetections 
    ON tblRefFireSafetyDetectMethods.FireSafetyDetectedID = tblAssignedFireSafetyDetections.FireSafetyDetectedID
) [q1] 
ON tblAssignedBuildingTypes.AssignedBuildingTypeID = q1.AssignedBuildingTypeID
WHERE (((qryAllPropertyIDs.Status)="Active"));

Solution 1

I should have read the comments before working on this since you no longer care: This appears to work, though I wasn't willing to create your table structure and then put in fake data to test it. Access is willing to try to display this in design view which confirms that it believes the SQL is viable.

Select * FROM 
( select * from 
  (  select * from 
    tblPropertySpecs
    INNER JOIN 
    ( select * from 
      tblAssignedBuildingTypes 
      INNER JOIN qryAllPropertyIDs 
      ON tblAssignedBuildingTypes.PropertySpecID = qryAllPropertyIDs.PropertySpecID
    ) as a
    ON (tblPropertySpecs.PropertySpecID = a.PropertySpecID) 
    AND (tblPropertySpecs.PropertySpecID = a.PropertySpecID)
  ) 
  INNER JOIN 
  ( select * from 
    tblRefConstructionTypes 
    INNER JOIN tblAssignedConstructionTypes 
      ON tblRefConstructionTypes.ConstructionTypeID = tblAssignedConstructionTypes.ConstructionTypeID
  ) as b
  ON a.AssignedBuildingTypeID = b.AssignedBuildingTypeID
) as c
LEFT JOIN 
( select * from 
  tblRefFireSafetyDetectMethods 
  INNER JOIN tblAssignedFireSafetyDetections 
    ON tblRefFireSafetyDetectMethods.FireSafetyDetectedID = tblAssignedFireSafetyDetections.FireSafetyDetectedID
) as q1
ON c.AssignedBuildingTypeID = q1.AssignedBuildingTypeID
WHERE (((a.Status)="Active"));

In general a few oddities about complicated Access queries.

• It is very difficult to compare a value from a subquery if you do not alias it. How do you reference the value? It's the value of the particular field from the subquery.

• Access if finicky. Sometimes you need to add in extra Select * from statements to make it happy.

That being said, if you are going to do anything remotely complicated, it is far easier to make and save subqueries to your database. It's not as pretty, but it definitely is easier. Additionally, sometimes a query that gets the Query is too complex error will work by saving part of it as a query instead of using subqueries.

Solution 2

"I know that i could create a seprate query but i dont have another need for it and would like to keep the object list as clean as possible."

You can set the Hidden attribute on a saved query so that it's not displayed in the database window (Access version < 2007) or navigation pane (Access >= 2007). That will prevent it from cluttering up your query list unless you have set the Access option to Show Hidden Objects.

You can accomplish much the same thing by prefacing the query name with USys, with the difference that it won't be displayed with the other saved queries unless you set the Access option to Show System Objects.

Comments

[Fionnuala]

What is the problem? You seem to have an alias [q1]. You can say As q1 or simply (query here) a

[BilliD]

Thanks Remou but i am still having the same problem. Anyone Else have any options?

[Fionnuala]

I am not sure, I would be inclined to alias all your tables INNER JOIN tblAssignedConstructionTypes As a and ensure that you do not use the same inner aliases as outer aliases, Access is strange like that.

[BilliD]

Thanks, Ill chalk it up to an Access issue and create a seperate query, not worth the time. I appriciat all you help. If you create an answer stating the same thing as your comment i will give you credit.

[Daniel]

I'd wager it says Syntax Error in FROM Clause and points at [q1]