creating/defining relationships between two tables

[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.

Brian

 

[Allen Browne]

1. Create a query containing both tables.

2. In the upper pane of the query design window, drag Table1.Watershed onto
Table2.Watershed. Access shows a line joining the 2 fields.

3. Now drag Table1.Type onto Table2.Type. You get a second line, parallel to
the first.

This is a 2-field join, and if I understand you correctly, it should give
what you want.

 

[Guest]

thank you. Actually pretty simple. I figure out another way to. Thanx for the
response.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can relate two tables using more than one column. If table2 is the
"many" side of the one to many relationship you can use the following
DDL statement to create the relationship. Watershed & Type must be the
Primary Key or a Unique Key in table1. It would be a good idea to place
an index on Watershed & Type in table2.

ALTER TABLE table2 ADD CONSTRAINT
FK_WatershedType FOREIGN KEY (Watershed, Type)
REFERENCES table1 (Watershed, Type)

Substitute your table names for table1 and table2. Place this statement
in a QueryDef's SQL view & run it.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmQO4YechKqOuFEgEQJ31ACffUpJAGFNTF9sVCd3qtlaVHaKCIsAoLPt
dj1d07BlqxNSj5UiiN8LzW+s
=r2pg
-----END PGP SIGNATURE-----