SQL Statement in Query

[EcologyHelp]

SELECT WSPZ1_C.ID, WSPZ1_C.Wetland_Site_ID, WSPZ1_C.PZ1_SP1_Stratum,WSPZ1_C.PZ1_SP1_DominantSp ecies, WSPZ1_C.[PZ1_SP1_Height Class],WSPZ1_C.[PZ1_SP1_Cover Class], WSPZ1_C.PZ1_SP1_CofC, WSPZ1_C.[Wetland SitePlant Zone 1 + NWPL].GP, WSPZ1_C.[Wetland Site Plant Zone 1 + NWPL].WMVC,WSPZ1_C.PZ1_SP2_Stratum, WSPZ1_C.PZ1_SP2_DominantSpecies,WSPZ1_C.[PZ1_SP2_Height Class], WSPZ1_C.[PZ1_SP2_Cover Class],WSPZ1_C.PZ1_SP2_CofC, WSPZ1_C.WSPZ1_A.NWPL_2014.GP, WSPZ1_C.WSPZ1_A.NWPL_2014.WMVC,WSPZ1_C.PZ1_SP3_Str atum, WSPZ1_C.PZ1_SP3_DominantSpecies, WSPZ1_C.[PZ1_SP3_HeightClass], WSPZ1_C.[PZ1_SP3_Cover Class], WSPZ1_C.PZ1_SP3_CofC,WSPZ1_C.WSPZ1_B.NWPL_2014.GP, WSPZ1_C.WSPZ1_B.NWPL_2014.WMVC,WSPZ1_C.PZ1_SP4_Str atum, WSPZ1_C.PZ1_SP4_DominantSpecies,WSPZ1_C.[PZ1_SP4_Height Class], WSPZ1_C.[PZ1_SP4_Cover Class], WSPZ1_C.Pz1_SP4_CofC,WSPZ1_C.NWPL_2014.GP, WSPZ1_C.NWPL_2014.WMVC, WSPZ1_C.PZ1_SP5_Stratum,WSPZ1_C.PZ1_SP5_DominantSp ecies, WSPZ1_C.[PZ1_SP5_Height Class],WSPZ1_C.[PZ1_SP5_Cover Class], WSPZ1_C.PZ1_SP5_CofC, NWPL_2014.GP,NWPL_2014.WMVC, WSPZ1_C.PZ1_SP6_Stratum, WSPZ1_C.PZ1_SP6_DominantSpecies,WSPZ1_C.[PZ1_SP6_Height Class], WSPZ1_C.[PZ1_SP6_Cover Class],WSPZ1_C.PZ1_SP6_CofC, WSPZ1_C.PZ1_SP7_Stratum, WSPZ1_C.PZ1_SP7_DominantSpecies,WSPZ1_C.[PZ1_SP7_Height Class], WSPZ1_C.[PZ1_SP7_Cover Class], WSPZ1_C.PZ1_SP7_CofC,WSPZ1_C.PZ1_SP8_Stratum, WSPZ1_C.PZ1_SP8_DominantSpecies,WSPZ1_C.[PZ1_SP8_Height Class], WSPZ1_C.[PZ1_SP8_Cover Class],WSPZ1_C.PZ1_SP8_CofC


FROM WSPZ1_C LEFT JOIN NWPL_2014 ONWSPZ1_C.PZ1_SP5_DominantSpecies = NWPL_2014.Scientific_Name;


Above is the SQL statement that I am receiving in regards to the SQL code also pasted above. I am designing a query that references previously saved queries. I am using the same types of joins and tables to add data in incremental order. The first 3 increments worked fine, but I received this statement on the 4th. No process had been changed between the 3rd and forth increment and I am stumped as to how field could refer to more then one table in the from clause if there is only one table it could reference from. Any help would be appreciated.

Thank you.

[ssanfu]

It looks to me that there is a field "NWPL_2014.GP" (in "WSPZ1_C") just above the error message and there is a field "GP" in the table(?) "NWPL_2014".
In your query, which table/query does "NWPL_2014.GP" refer to? That is the cause of the error message. See the lines in RED below.

SELECT 
WSPZ1_C.ID, 
WSPZ1_C.Wetland_Site_ID, 
WSPZ1_C.PZ1_SP1_Stratum,
WSPZ1_C.PZ1_SP1_DominantSpecies, 
WSPZ1_C.[PZ1_SP1_Height Class],
WSPZ1_C.[PZ1_SP1_Cover Class], 
WSPZ1_C.PZ1_SP1_CofC, 
WSPZ1_C.[Wetland SitePlant Zone 1 + NWPL].GP,      (<<-- spaces and special characters) 
WSPZ1_C.[Wetland Site Plant Zone 1 + NWPL].WMVC,     (<<-- spaces and special characters)
WSPZ1_C.PZ1_SP2_Stratum, 
WSPZ1_C.PZ1_SP2_DominantSpecies,
WSPZ1_C.[PZ1_SP2_Height Class], 
WSPZ1_C.[PZ1_SP2_Cover Class],
WSPZ1_C.PZ1_SP2_CofC, 
WSPZ1_C.WSPZ1_A.NWPL_2014.GP, 
WSPZ1_C.WSPZ1_A.NWPL_2014.WMVC,
WSPZ1_C.PZ1_SP3_Stratum, 
WSPZ1_C.PZ1_SP3_DominantSpecies, 
WSPZ1_C.[PZ1_SP3_HeightClass], 
WSPZ1_C.[PZ1_SP3_Cover Class], 
WSPZ1_C.PZ1_SP3_CofC,
WSPZ1_C.WSPZ1_B.NWPL_2014.GP, 
WSPZ1_C.WSPZ1_B.NWPL_2014.WMVC,
WSPZ1_C.PZ1_SP4_Str atum, 
WSPZ1_C.PZ1_SP4_DominantSpecies,
WSPZ1_C.[PZ1_SP4_Height Class], 
WSPZ1_C.[PZ1_SP4_Cover Class], 
WSPZ1_C.Pz1_SP4_CofC,
WSPZ1_C.NWPL_2014.GP, 
WSPZ1_C.NWPL_2014.WMVC, 
WSPZ1_C.PZ1_SP5_Stratum,
WSPZ1_C.PZ1_SP5_DominantSpecies, 
WSPZ1_C.[PZ1_SP5_Height Class],
WSPZ1_C.[PZ1_SP5_Cover Class], 
WSPZ1_C.PZ1_SP5_CofC, 
NWPL_2014.GP,
NWPL_2014.WMVC, 
WSPZ1_C.PZ1_SP6_Stratum, 
WSPZ1_C.PZ1_SP6_DominantSpecies,
WSPZ1_C.[PZ1_SP6_Height Class], 
WSPZ1_C.[PZ1_SP6_Cover Class],
WSPZ1_C.PZ1_SP6_CofC, 
WSPZ1_C.PZ1_SP7_Stratum, 
WSPZ1_C.PZ1_SP7_DominantSpecies,
WSPZ1_C.[PZ1_SP7_Height Class], 
WSPZ1_C.[PZ1_SP7_Cover Class], 
WSPZ1_C.PZ1_SP7_CofC,
WSPZ1_C.PZ1_SP8_Stratum, 
WSPZ1_C.PZ1_SP8_DominantSpecies,
WSPZ1_C.[PZ1_SP8_Height Class], 
WSPZ1_C.[PZ1_SP8_Cover Class],
WSPZ1_C.PZ1_SP8_CofC

FROM WSPZ1_C LEFT JOIN NWPL_2014 ON WSPZ1_C.PZ1_SP5_DominantSpecies = NWPL_2014.Scientific_Name;

NOTE: shouldn't use spaces, punctuation or special characters in object names.



Other things:

And it looks to me that your tables are not normalized. You have repeating fields in the left hand table (WSPZ1_C).
In "NWPL_2014", "2014" should be data. Is there a table "NWPL_2013" or "NWPL_2015"?
You have linked the tables on a text field. Not the best thing to do. While you can use a text field as a PK field, it is slow.
See: Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp
(the paragraph Only Use One Numeric Field as the Primary Key )

[John_G]

From what I can see, your table NWPL_2014 is a component of query WSPZ1_A, query WSPZ1_B, query WZPZ1_C, and the current query Query1 (as a lookup?). I suspect that Access is getting totally confused which occurance you are referring to when you reference the fields GP and WMVC. What you could try is to use distinct aliases for each occurance of those fields.

[EcologyHelp]

Thanks for the replies.

Unfortunately I didn't build the database or do any of the original naming of the fields. in response to ssnafu, the lines in red refer back to the original table that each of the incremental queries have been linked to. There are repeating fields in the table but each one is named slightly different. The data comes from an ecological study and the table is a collection of plant species and there relative cover and dominance in a specific plant zone. The NWPL_2014 table is the National Wetland Plant List updated in 2014 that has the indicator status for each plant. So I have to link the tables for through the plant names because that is the only field that they have in common. So in order to get the correct indicator status for each plant in each field I have to build a query on a query 8x. I hope that clears up what is happening in the SQL code.

@John_G You are correct that NWPL_2014 is a component of each query. I will try to use aliases for each occurrence to see if that clears up the issue.

Thanks so much for the feedback I appreciate it.