CODEWITHSUNDEEP
sqlms-accessjoinalias
BilliD
BilliD

Reputation: 617

Giving an alias to a subquery containing a join in access

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"));

Upvotes: 2

Views: 13577

Answers (2)

HansUp
HansUp

Reputation: 97101

"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.

Upvotes: 2

Daniel
Daniel

Reputation: 13122

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.

Upvotes: 3

Related Questions