Dealing with Duplicates and Boolean Values

[EcologyHelp]

Since I received such helpful feedback before I thought I would try this again. A few notes before I ask my question, I did not build the database so I am just doing my best to work with how the database is set up, and I know that I am using names that would not be recommended for proper naming conventions.
In the pictures above I have a dataset that uses duplicate values for each record. The purpose of that was (I believe) to include the data for a specific site from two different forms on one table in access. In addition to that I have a presence/absence box, that at least in this example is checked for each record (that is not always the case). What I am trying to accomplish, is to fill the 3rd column over from the check boxes with values from the Species_List table from the Final C_Value field. When I try a outer join as you can see in the second picture the query returns many more duplicates than the original has, as seen in the 3rd picture. The goal is to fill each VP1_SP#_CofC column in the Intensive_VegPlot_1 table with the corresponding Final C_Values from the Species_List table. I am assuming that I am going to have to build upon queries to fill each column.
Any help would be appreciated

[June7]

Is the Scientific_Name not unique in the Species_List table?

If you want to provide db for analysis, follow instructions at bottom of my post.

[EcologyHelp]

EIA_Copy.zipThe Scientific_Name is unique, there are no duplicates in that field. I am not sure how helpful it would be for me to upload the db, the whole database if fairly large and I am just working in a small part of it, but I have attached it if needed.

[John_G]

No, you don't need to build successive chained queries (at least I don't think so). You are using the Species_List table as a lookup to obtain the Final C_Value data; no problem there. However, because you have several fields in the query (at least 8?) looking up data from Species_List, you are going to need one occurrance of that table for each lookup you have in the query. So, VP1_SP1 links to the first occurance (as you show), VP2_SP2 links to the second one, and so on.

Without seeing the data, I can't really say why there are duplicates in the query results, but it suggests there may be duplicate rows in a table somewhere.

Is Intensive_Vegplot_1 a table or an earlier query?

[EcologyHelp]

No, you don't need to build successive chained queries (at least I don't think so). You are using the Species_List table as a lookup to obtain the Final C_Value data; no problem there. However, because you have several fields in the query (at least 8?) looking up data from Species_List, you are going to need one occurrance of that table for each lookup you have in the query. So, VP1_SP1 links to the first occurance (as you show), VP2_SP2 links to the second one, and so on.

That makes perfect sense thank you.

Intensive_VegPlot_1 is a table if that helps, and there are duplicate rows in that table. I believe that is a big part of my problems, but I am not sure how to work around it as the table is set up to allow for duplicate values.

[June7]

Scientific_Name field does not have unique values. There are 8,057 names that have duplicates. Do this query to see which ones:

SELECT Species_List.Scientific_Name, Count(Species_List.Scientific_Name) AS CountOfScientific_Name
FROM Species_List
GROUP BY Species_List.Scientific_Name;

Or just sort the table by Scientific_Name to see the duplications.

This is why you get 'duplicate' records in query that joins the two tables.

[EcologyHelp]

Scientific_Name field does not have unique values. There are 9,623 names that have duplicates. Do this query to see which ones:

SELECT Species_List.Scientific_Name, Count(Species_List.Scientific_Name) AS CountOfScientific_Name
FROM Species_List
GROUP BY Species_List.Scientific_Name;

Or just sort the table by Scientific_Name to see the duplications.

This is why you get 'duplicate' records in query that joins the two tables.

Of course I assumed that when this table was added there weren't any duplicates in it. I guess that's what happens when you assume. Thanks so much June7