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 relationships 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. Brian |
#2
|
|||
|
|||
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "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. Brian |
#3
|
|||
|
|||
thank you. Actually pretty simple. I figure out another way to. Thanx for the
response. "Allen Browne" wrote: 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "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. Brian |
#4
|
|||
|
|||
-----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/AwUBQmQO4YechKqOuFEgEQJ31ACffUpJAGFNTF9sVCd3qtlaVH aKCIsAoLPt dj1d07BlqxNSj5UiiN8LzW+s =r2pg -----END PGP SIGNATURE----- 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. Brian |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Relationships between Tables | Giovanni | Database Design | 4 | March 2nd, 2005 06:33 PM |
Can't drag tables in relationships view | WC | Running & Setting Up Queries | 3 | January 27th, 2005 05:15 AM |
Understanding Primary Keys | Khai | Database Design | 3 | January 20th, 2005 10:47 PM |
double tables in relationships | Joel | General Discussion | 2 | October 15th, 2004 07:28 AM |
Mutliple Tables lookup? | Westley | Database Design | 4 | June 15th, 2004 01:07 AM |