G
Guest
Hi all, I have a question on how to create a relationship between two tables
in order to match individual records in one table to multiple records of
another table. Basically, I need to combine two tables to create one ouput
table. The first table (Table 1) looks like this... (there are more
columns/fields as well)
Watershed Type WDRl R6ml R2m
Nevada Creek B 10.9 11% 8%
Nevada Creek C 20 10% 8%
Nevada Creek E 7.9 70% 63%
Nevada Creek F 18.6 16% 11%
Middle Blackfoot B 10.5 4% 4%
Middle Blackfoot C 28.8 8% 5%
Middle Blackfoot C 18.9 8% 5%
Middle Blackfoot E 9.7 14% 10%
Middle Blackfoot F 9.7 14% 10%
The second table (Table 2) looks something like this....
Watershed_ Site Type_sub Type
Nevada Creek Nev9 E4 E
Nevada Creek BlkBr4 E5 E
Middle Blackfoot Blan1 C4 C
Nevada Creek Braz2 B4 B
Middle Blackfoot Buck1 B3 B
Nevada Creek Buff2 B4 B
Nevada Creek Buff2b E
Middle Blackfoot CttnBlk0 C4 C
Middle Blackfoot CttnBlk2 C4 C
Middle Blackfoot CttnBlk4 C3 C
Nevada Creek Doug2 B4 B
Nevada Creek Doug3 E4 E
Nevada Creek CttnNev2b E4 E
Nevada Creek Doug5 F4 F
Nevada Creek Doug7 C4 C
Middle Blackfoot Fraz3 E3 E
Nevada Creek Gall2 E4 E
Nevada Creek Gall2b C4 C
Nevada Creek Jeff1 B4 B
Nevada Creek Jeff2 F4 F
Middle Blackfoot Klein2 E5 E
Nevada Creek McEl1b F4 F
Nevada Creek McEl1 E5 E
Middle Blackfoot Mont5b C4 C
Middle Blackfoot Mont7 C4 C
Middle Blackfoot Mont10 C4 C
Middle Blackfoot Mont12 C3 C
Nevada Creek Nev2b B3 B
Nevada Creek Nev3 C3 C
As you can see, the common fields are "watershed" and "type". What I need to
do is; for every record (or "Site") in Table 2, I need to get the info from
Table 1 that corrsponds to the Site's "Watershed" and "Type", and create a
new table with the info from both tables. However, in Table 1 each watershed
has the same type values, i.e both watersheds have "B", "C", "E", "F".
Therefore I can't link by "type", I don't think. I thought of somehow writing
a conditional query, but am not sure on how to go about it. Any ideas??
Thanks.
in order to match individual records in one table to multiple records of
another table. Basically, I need to combine two tables to create one ouput
table. The first table (Table 1) looks like this... (there are more
columns/fields as well)
Watershed Type WDRl R6ml R2m
Nevada Creek B 10.9 11% 8%
Nevada Creek C 20 10% 8%
Nevada Creek E 7.9 70% 63%
Nevada Creek F 18.6 16% 11%
Middle Blackfoot B 10.5 4% 4%
Middle Blackfoot C 28.8 8% 5%
Middle Blackfoot C 18.9 8% 5%
Middle Blackfoot E 9.7 14% 10%
Middle Blackfoot F 9.7 14% 10%
The second table (Table 2) looks something like this....
Watershed_ Site Type_sub Type
Nevada Creek Nev9 E4 E
Nevada Creek BlkBr4 E5 E
Middle Blackfoot Blan1 C4 C
Nevada Creek Braz2 B4 B
Middle Blackfoot Buck1 B3 B
Nevada Creek Buff2 B4 B
Nevada Creek Buff2b E
Middle Blackfoot CttnBlk0 C4 C
Middle Blackfoot CttnBlk2 C4 C
Middle Blackfoot CttnBlk4 C3 C
Nevada Creek Doug2 B4 B
Nevada Creek Doug3 E4 E
Nevada Creek CttnNev2b E4 E
Nevada Creek Doug5 F4 F
Nevada Creek Doug7 C4 C
Middle Blackfoot Fraz3 E3 E
Nevada Creek Gall2 E4 E
Nevada Creek Gall2b C4 C
Nevada Creek Jeff1 B4 B
Nevada Creek Jeff2 F4 F
Middle Blackfoot Klein2 E5 E
Nevada Creek McEl1b F4 F
Nevada Creek McEl1 E5 E
Middle Blackfoot Mont5b C4 C
Middle Blackfoot Mont7 C4 C
Middle Blackfoot Mont10 C4 C
Middle Blackfoot Mont12 C3 C
Nevada Creek Nev2b B3 B
Nevada Creek Nev3 C3 C
As you can see, the common fields are "watershed" and "type". What I need to
do is; for every record (or "Site") in Table 2, I need to get the info from
Table 1 that corrsponds to the Site's "Watershed" and "Type", and create a
new table with the info from both tables. However, in Table 1 each watershed
has the same type values, i.e both watersheds have "B", "C", "E", "F".
Therefore I can't link by "type", I don't think. I thought of somehow writing
a conditional query, but am not sure on how to go about it. Any ideas??
Thanks.