Dear Josh:
I cannot evaluate how this works functionally as you have given no
indication of how it is to funciton. However, I can take a stab at
removing mechanical difficulties.
SELECT a.field1 AS F1a, a.field2 AS F2A, c.F1B, c.F1D
FROM ATable AS a
INNER JOIN
(SELECT b.field1 AS F1B, d.field1 AS F1D
FROM BTable b
INNER JOIN DTable d ON b.ID=d.ID) AS c
ON a.ID=c.ID
WHERE c.field1=1;
I removed the double quotes around the subquery. Surely this was not
meant as a literal, but as an actual subquery, right?
The INNER JOIN inside the subquery references the tables being joined
with ON b.ID = d.ID, but you never provided the aliases b and d. I
have added them, hopefully correctly.
The subquery then returns two columns with exactly the same name
"field1" both under the alias "c". The outer query then references
field1 of c, which is ambiguous. I have aliased the two as F1B and
F1D, but because of the ambiguity, I cannot tell which you wish to
actually see. I have therefore shown both. You can decide which you
want to see and alias them appropriately.
Similarly, the main query was returning 2 columns named field1, making
it likely any outside references to the results ambiguous with respect
to field names. I have added aliases here as well. You can change
those aliases or eliminate them, but it is strongly recommended you
make each column name unique. The form can then be written to
reference the unique column names, which cannot be done if they are
ambiguous.
It is entirely possible there remain difficulties with the query as
thus reformed, but without a description of the tables and their
relationships as well as having a specification of how it is supposed
to work, I don't think I can do any more safely.
Hopefully this will at least resolve the syntax level problems.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
