Nested query problem

[Aaron]

My issue is trying to get nested joins to work in Access. I'm open to
any suggestion that will work however I cannot alter the data
structure.
First of all, in order to get the data I need, I have to match three
ID fields as there is no unique field so to join the first two tables
I have to do this: (Code is generated by Access)

SELECT OARentRoll.PropID, OARentRoll.VersionID,
OAMarketLeasing.PropID, OAMarketLeasing.VersionID,
OARentRoll.TenantID, OARentRoll.BaseRentDetailID,
OARentRoll.ReimbursementID, OARentRoll.AbatementID,
OAMarketLeasing.VersionID
FROM OARentRoll LEFT JOIN OAMarketLeasing ON OARentRoll.MarketID =
OAMarketLeasing.MLAID
WHERE (((OARentRoll.PropID)=[GetPropID])
AND ((OARentRoll.VersionID)=[GetVersionID])
AND ((OAMarketLeasing.PropID)=[OARentRoll].[PropID])
AND ((OAMarketLeasing.VersionID)=[OARentRoll].[VersionID]));

I've joined the OaRentroll table and the OAMarketLeasing table on
PropID, Version ID and MarketID. The left join on MarketID is so
thatALL matching OARentroll records are included even if they dont
have a MarketID.

I need to bring in one additional table for each of the ID fields in
the select statement (TenantID, BaseRentDetailID, etc) and do the
three field join for each of those
(OARentRoll.PropID=BaseRentTable.PropID AND
OARentRoll.VersionID=BaseRentTable.VersionID AND
OARentRoll.BaseRentDetailID=BaseRentTable.BaseRentID)

Each of those additional joins has to be ALL records from OARentRoll
and only those fields from addtional tables where joined fields are
equal.

Can I continue this pattern and bring in 5 or 6 additional tables
showing 3 or 4 fields from each table? Does every field from every
table need to be listed in the first Select statement?

Thanks very much!

 

[Neil]

First, no, you don't have to list every field from every table. Only show
the fields you need.

And, second, yes, just continue to do a left outer join from OARentRoll to
each of the tables, separately. That will show all records from OARentRoll
and only matching records from the others (NULL if it doesn't match).

I would suggest using the Access graphical query builder, rather than trying
to do it in SQL. It gets a bit complex when you have multiple joins between
tables. Using the graphical interface will be much simpler.

Neil

 

[Aaron]

Thanks for the reply Neil! Got the first subquery working but I cant
do it in the design window. I have to do it in SQL then switch to the
design window....weird.

 

[Access Developer]

Query Builder view to/from SQL View is supported for "normal" queries, but
doesn't even claim to handle "all" -- for example, UNION and UNION ALL
queries can only be done in SQL View. OTOH, I continue to be astonished at
the complexity of some queries that can be switched between the two views.

 

[Neil]

Thanks for the reply Neil! Got the first subquery working but I cant
do it in the design window. I have to do it in SQL then switch to the
design window....weird.

In your original post, you said you wanted to join additional TABLES to the
main table using outer joins. I mentioned that it wouldn't be a problem to
do that with multiple tables. Now you're saying you want to join multiple
subqueries, not tables? That should be fine, as well; but you can't do that
directly in the graphical user interface. As you note, you have to do that
in SQL, but then it'll show up in the GUI.

OTOH, if you're just using the term "subquery" when you mean "table,"
they're not the same. A table is just a table, and a subquery is a SQL
statement (or a stored query).

If your "subquery" is a stored (named) query, then you should still be able
to easily add it to the GUI.

If you're still working on this, why don't you specify what, exactly, you're
doing, and why you're unable to do it in the design window.

Neil

 

[Neil]

Thanks for the reply Neil! Got the first subquery working but I cant
do it in the design window. I have to do it in SQL then switch to the
design window....weird.

P.S. If you are adding SQL statement subqueries, then you might want to just
save each as a named query, and then add it using the GUI. Or, a trick you
can use to make it easier to do, is create a table that has the same fields
and structures as the subqueries. Add it to your query using the GUI, as
many times as needed, creating the appropriate joins each time. Give it an
alias each time (a, b, etc., or whatever you want). Then go into the SQL
view and replace the table name each time with a SQL statement (enclosed in
parentheses).

For example, if the dummy table you're using is called MyTable, in your SQL,
after adding it multiple times and giving it the alias a, b, etc., you'll
see places where it says: From MyTable As A.... From MyTable As B.... etc.
Just replace the word "MyTable" with your SQL statement so that it instead
says something like: "From (Select * From Widgets) As A."

That will allow you to add multiple subqueries with outer joins without
having to deal with all the complexities of multiple joins in Access SQL.

 

[Phil Hunt]

When you said subquery, you probabaly mean a 'saved query'.
You will need them if you have multiple outer join, because Access dos not
always understand which run first. By using a 'save query', it tells Access
to run it first.