Join several tables in one query

[Maital]

Dear experts,

I'm a beginner with ms-access.
I have data from 3 years of observations on a certain field, divided
into North and South sections. The first table has information for
about 600 species. The other tables indicate weather a plant was found
on the field that year.

I'd like to create a query that combines the data for the 3 years, and
presents only the plants that grew in the field for at least one year.

Here's an example of the data:

Plants:
Key Genus Species
----- ------ ----------
1 Allium oriente
2 Olea europaea
3 Phalaris paradoxa
4 Lotus colinus
5 Pistacia lentiscus
6 Vicia sativa
7 Silene colorata

Year1:
Key N1 S1
---- ---- ----
1 + +
2 +
3 + +

Year2:
Key N2 S2
---- ---- ----
1 +
2 +
4 +

Year3:
Key N3 S3
---- ---- ----
1 +
2 +
4 +
5 +

I would like to present the following output:

Key Genus Species N1 N2 N3 S1 S2 S3
---- ------ ------- ---- ---- ---- ---- ---- ----
1 Allium oriente + + + +
2 Olea europaea + + +
3 Phalaris paradoxa + +
4 Lotus colinus + +
5 Pistacia lentiscus +
Any idea how do I do that with an Access sql query??
I'd be greatful for any suggestion.

Thanks,
Maital.

[pietlinden]

join the tables in your query and outer join them if necessary. Then
you'll get all plants even if they have no readings.

SELECT plant.Key, plant.genus, plant.species, Year1.N1, Year1.N2...
FROM plant LEFT JOIN Year1 ON Plant.Key = Year.Key....

[maitala]

pi********@hotmail.com wrote:

join the tables in your query and outer join them if necessary. Then
you'll get all plants even if they have no readings.

SELECT plant.Key, plant.genus, plant.species, Year1.N1, Year1.N2...
FROM plant LEFT JOIN Year1 ON Plant.Key = Year.Key....

Thank you for your answer. I managed to do that. The thing is, that I
don't want to get plant names that have no reading, since the plant's
list has 600 entries, but on the field only about 100 species were
observed during the years (The plants table serves other experiments,
in other fields, with different species).

I tried to left join and right join with all possible combinations, to
no avail. I think that the outer join command would do the job, but
Access doesn't support it. Therefore, I need a workaround.

[hoabo]

Maital
It looks to me like the first problem is in the number of tables. Am I
understanding the design of your tables correctly that you have a table
for North plants, one for South plants, and then a table for each year
of data that you are collecting? If yes, you only need one table for
the plants with a field to identify North or South and then one table
for your data with a field to identify the year.
By reducing the number of tables you are following proper database
design principles which will result in being able to create a query
much more easily.
DbGeek

[Maital]

ma*****@gmail.com wrote in message news:<11**********************@o13g2000cwo.googleg roups.com>...

The following SQL does the job:

SELECT
plants.Key, plants.Genus, plants.Species, year1.N1, year1.S1,
year2.N2, year2.S2, year3.N3, year3.S3
FROM
((plants LEFT JOIN year1 ON plants.Key = year1.key) LEFT JOIN year2 ON
plants.Key = year2.key) LEFT JOIN year3 ON plants.Key = year3.key
WHERE
(Year1.N1 <> Null or Year1.S1 <> Null or Year2.N2 <> Null or Year2.S2
<> Null or Year3.N3 <> Null or Year3.S3 <> Null);
That's not too complicated if each year's table had only 3 fields
(key, North, South). But I actually have 60 fields for each year's
table :(

Can anyone suggest a more simple solution?

[maitala]

DbGeek,

The DB structure is as follows:
Plants (Key, Genus, Species).
Year1 (Key, A1, B1, C1, D1, E1, F1, G1 … ïƒ* 60 fields).
Year2 (Key, A2, B2, C2, D2, E2, F2, G2 … ïƒ* 60 fields).
Year3 (Key, A3, B3, C3, D3, E3, F3, G3 … ïƒ* 60 fields).

“Plants� is a master table with plants information (Key, Genus,
Species). Key is a unique code for each plant. It has 600 records.

“Year1�, “Year2� and “Year3� hold information gathered over
3 consecutive years in the same field. The field is divided into 60
plots (and not only North and South, as I stated before – I just
wanted to simplify things).
Key is a unique plant code. It is the same code as in the Plants table
(I don’t know the proper term for the relationships between the
tables. A plant in a Year table *for sure* has a record in the Plants
table, but not all plants in the Plants table have records in the Years
tables).

I used A1, B1, C1, A2, B2, C2 etc. to distinguish the fields on the 3
tables. The idea behind that is to present the information for each
plot in the following way:

Key Genus Species A1 A2 A3 B1 B2 B3
--- ------ --------- --- --- --- --- --- ---
1 genus1 species1 + + +
2 genus2 species2 + +

The “+� sign indicates that the plant was observed in the specific
plot. If a plant did not grow in the field in a certain year, it will
not have a record that year.

Since there are 600 records in the Plants master table, and only about
100 plants in each Year’s table, I would like to present only the
data for plants that exist on at least one of the Year’s tables….
I hope that wasn’t too complicated. I managed to find a solution for
a small number of fields in each Year’s table (see my previous
posting), but since I have so many fields it gets complicated.

Maybe the solution will be to change the database structure – but I
can’t think of any better structure…

Thanks for your time and advice, I deeply appreciate it.

Maital.

[Theo Peterbroers]

ma*****@gmail.com wrote in message news:<11**********************@o13g2000cwo.googleg roups.com>...

DbGeek,

The DB structure is as follows:
Plants (Key, Genus, Species).
Year1 (Key, A1, B1, C1, D1, E1, F1, G1 =E2=80=A6 =EF=83=A0 60 fields).
Year2 (Key, A2, B2, C2, D2, E2, F2, G2 =E2=80=A6 =EF=83=A0 60 fields).
Year3 (Key, A3, B3, C3, D3, E3, F3, G3 =E2=80=A6 =EF=83=A0 60 fields).
8<-----------Snip

I hope that wasn't too complicated. I managed to find a solution for
a small number of fields in each Year's table (see my previous
posting), but since I have so many fields it gets complicated.

Maybe the solution will be to change the database structure but I
can't think of any better structure.
Thanks for your time and advice, I deeply appreciate it.

Maital.

Maybe you can find a solution outside your DBMS.
You structured your data as if in a spreadsheet.
If you import your three tables into separate worksheets,
you may be able to consolidate your data in a fourth one.

This may be improved upon by importing not tables, but
queries like
SELECT p.Genus, p.Species, y.*
FROM Plants p, Year1 y
WHERE p.key=y.key.

As to your database design, three tables will do.
Plants (Key, Genus, Species) Key is primary key, the wise man places a
unique key on (genus, species).
Plots (PlotNo) PlotNo is primary key
Observations (PlotNo, Year, Key) all 3 columns constitute the primary key.