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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |