Advice?

[sueviolet]

What I am working with:

I have 3 tables:

Table 1:

Contains data about a specific geographic location

Table 2:

Contains Type "x" data found at a site recorded in table 1

Table 3:

Contains Type "y" data found at a site recorded in table 1


At the moment table 1 and table 2 are joined in a one-to-many relationship and table 1 and table 3 are joined in a one-to-many relationship.


What I want to me able to do:

Bring the tables together in a query so I can view all Type X AND Type y data associated with a particular site in table 1.


At the moment I can only bring table 1 and table 2 or table 1 and table 3 into a query, because table 2 and table 3 are not related to one another.

Does anybody have any suggestions as to how I can re-design my tables to produce the result I want.

Thanks for your time.

 

[RichMorrison]

Question:
is there always an "x" and a "y" for each site in table 1 ?

Sounds like no.

So you need:
1) sites with x and y
2) sites with x only
3) sites with y only

and then put it all together.

Right ?

Start with this and we can move forward.

RichM

 

[sueviolet]

Thanks Rich


Yes, you are right



1) sites with x and y
2) sites with x only
3) sites with y only


is correct

 

[RichMorrison]

OK, you need any site with:
X and Y,
or
X,
or
Y

And you probably want a site "together" in one row.

Query1 will select all sites from table 1.
Query2 will select site and X from table 2.
Query3 will select siet and Y from table 3.

Query 4 will put it all together.
It will have Query1 on the left side of the Design window.
It will have Query2 and Query3 on the right side.
The Site from Query1 will be a "Left Join" to the Site in Query2 and Query 3.

You will drag the Site from Query1 down to the grid.
You will drag X from Query2 and Y from Query3 down to the grid.
You will enter "Is Not Null" as Criteria under X.
You will enter "Is Not Null" as Criteria under Y ***** in the second row *****.

This produces SQL that says roughly:
Select Site, X, Y From Query1, Query2, Query 3
Join Stuff
Where (X is Not Null) OR (Y is Not Null);

RichM

 

[RichMorrison]

Pat,

I am trying to produce rows of this type:

Site=1 X=1 Y=2
Site=2 X=null Y=3
Site=3 X=4 Y=null

etc.

The Cartesian would be (more or less)
1 X=1
1 Y=2
2 Y=3
3 X=4

RichM

 

[sueviolet]

Thanks to you both for your help

I will give both of you're suggestions a try