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 » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

creating/defining relationships between two tables



 
 
Thread Tools Display Modes
  #1  
Old April 18th, 2005, 06:28 PM
Giz
external usenet poster
 
Posts: n/a
Default 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  
Old April 18th, 2005, 07:24 PM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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

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  
Old April 18th, 2005, 08:47 PM
MGFoster
external usenet poster
 
Posts: n/a
Default

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

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


All times are GMT +1. The time now is 02:49 PM.


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