I'm using Access 2010.
My error seems to be a result of the aliasing.
The solutions I've read specify that a different alias should be used at every level (which I've done) and that Access likes lots of SELECT * (so I added those, too) but it hasn't resolved my issue.
I'm getting an error on ServiceZipSpec.Service_Product. Any pointers? Thanks!
SELECT DISTINCT ServiceZipSpec.Service_Product, ServiceZipSpec.Specificity, Service_Availability.Available, Service_Availability.Zip_Code, Service_Availability.State
FROM
( SELECT * FROM
( SELECT * FROM Service_Availability AS C_Avail1
INNER JOIN
(
SELECT C_Avail2.Service_Product, MAX(C_Avail2.Specificity) AS Spec
FROM Service_Availability AS C_Avail2
WHERE (C_Avail2.State = "TX" OR C_Avail2.State = "CW")
GROUP BY C_Avail2.Service_Product
) MaxSpec
ON C_Avail1.Service_Product = MaxSpec.Service_Product
AND C_Avail1.Specificity = MaxSpec.Spec
) Service_Spec
LEFT JOIN TABLE_ZipCodes ON Service_Spec.State = TABLE_ZipCodes.State
) ServiceZipSpec
WHERE ServiceZipSpec.Available = TRUE AND (ServiceZipSpec.State = "TX" OR ServiceZipSpec.State = "CW")
;
The source of your problem is that you are using SELECT *
when joining tables that have similarly named columns. Try limiting to SELECT table_name.*
, or simply pick out the columns you need.
For example:
SELECT DISTINCT ServiceZipSpec.Service_Product, ServiceZipSpec.Specificity, Service_Availability.Available, Service_Availability.Zip_Code, Service_Availability.State
FROM
( SELECT Service_Spec.*, TABLE_ZipCodes.Zip_Code FROM
( SELECT C_Avail1.* FROM Service_Availability AS C_Avail1
...