A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

creating/defining relationship between two tables



 
 
Thread Tools Display Modes
  #1  
Old April 18th, 2005, 06:21 PM
Giz
external usenet poster
 
Posts: n/a
Default creating/defining relationship between two tables

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.
  #2  
Old April 18th, 2005, 08:12 PM
Giz
external usenet poster
 
Posts: n/a
Default

forget it, I figured it out. duh.

"Giz" wrote:

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.

  #3  
Old April 18th, 2005, 09:24 PM
Ed Warren
external usenet poster
 
Posts: n/a
Default

SELECT Table2.*, Table1.WDRl, Table1.R6ml, Table1.R2m
FROM Table1 RIGHT JOIN Table2 ON (Table1.Watershed = Table2.Watershed) AND
(Table1.Type = Table2.Type);


will produce the "table" (Query1) below, however, this may not be what you
want since you it is possible you don't have the tables properly
'normalized'.


Ed Warren.
------------------------------------------------------------------------------------------query
results.

Query1 Watershed Site Type_sub Type WDRl R6ml R2m
Middle Blackfoot Blan1 C4 C 18.9 0.08 0.05
Middle Blackfoot CttnBlk4 C3 C 18.9 0.08 0.05
Middle Blackfoot CttnBlk2 C4 C 28.8 0.08 0.05
Middle Blackfoot CttnBlk2 C4 C 18.9 0.08 0.05
Middle Blackfoot CttnBlk0 C4 C 28.8 0.08 0.05
Middle Blackfoot CttnBlk0 C4 C 18.9 0.08 0.05
Middle Blackfoot Buck1 B3 B 10.5 0.04 0.04
Middle Blackfoot CttnBlk4 C3 C 28.8 0.08 0.05
Middle Blackfoot Blan1 C4 C 28.8 0.08 0.05
Middle Blackfoot Fraz3 E3 E 9.7 0.14 0.1
Middle Blackfoot Mont5b C4 C 18.9 0.08 0.05
Middle Blackfoot Mont5b C4 C 28.8 0.08 0.05
Middle Blackfoot Mont7 C4 C 18.9 0.08 0.05
Middle Blackfoot Mont7 C4 C 28.8 0.08 0.05
Middle Blackfoot Mont10 C4 C 18.9 0.08 0.05
Middle Blackfoot Mont10 C4 C 28.8 0.08 0.05
Middle Blackfoot Mont12 C3 C 18.9 0.08 0.05
Middle Blackfoot Mont12 C3 C 28.8 0.08 0.05
Middle Blackfoot Klein2 E5 E 9.7 0.14 0.1
Nevada Creek Braz2 B4 B 10.9 0.11 0.08
Nevada Creek Buff2 B4 B 10.9 0.11 0.08
Nevada Creek Buff2b
E 7.9 0.7 0.63
Nevada Creek BlkBr4 E5 E 7.9 0.7 0.63
Nevada Creek Doug2 B4 B 10.9 0.11 0.08
Nevada Creek Doug3 E4 E 7.9 0.7 0.63
Nevada Creek CttnNev2b E4 E 7.9 0.7 0.63
Nevada Creek Nev9 E4 E 7.9 0.7 0.63
Nevada Creek Doug7 C4 C 20 0.1 0.08
Nevada Creek Nev3 C3 C 20 0.1 0.08
Nevada Creek Gall2 E4 E 7.9 0.7 0.63
Nevada Creek Gall2b C4 C 20 0.1 0.08
Nevada Creek Jeff1 B4 B 10.9 0.11 0.08
Nevada Creek Jeff2 F4 F 18.6 0.16 0.11
Nevada Creek McEl1b F4 F 18.6 0.16 0.11
Nevada Creek McEl1 E5 E 7.9 0.7 0.63
Nevada Creek Nev2b B3 B 10.9 0.11 0.08
Nevada Creek Doug5 F4 F 18.6 0.16 0.11



"Giz" wrote in message
...
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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Compiling Information From Subordinate Databases SGT Beene General Discussion 6 April 11th, 2005 10:33 PM
Same database or another? accesskastle Database Design 2 April 11th, 2005 07:29 PM
need to join local tables with sql 7 tables using guids Susan via AccessMonster.com Running & Setting Up Queries 0 January 31st, 2005 09:58 PM
Relationships Jerome Database Design 2 September 30th, 2004 03:56 PM
tables repeat in relationship window and will not stay deleted Marcy General Discussion 1 September 1st, 2004 10:40 PM


All times are GMT +1. The time now is 11:40 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.