combine three tables into one

[Guest]

Hell

I’ve been trying to combine three tables into one, without any success. Here’s an example of what I would like to accomplis

Table
WE Q
12/7/03 10
12/21/03 5
12/28/03 12

Table
WE Q
11/30/03 1
12/14/03
12/21/03
12/28/03 1

Table
WE
11/30/03 No
12/07/03 De
12/14/03 De
12/21/03 De
12/28/03 De

QryResul
WE M Q1 Q
11/30/03 Nov 1
12/07/03 Dec 100
12/14/03 Dec
12/21/03 Dec 50
12/28/03 Dec 120 1

As you can see Table1 contains information for WE that sometimes the same WE it’s included in Table2, so when I do a one-to-one relationship I only get WE 21 & 28, if I chose one (Table1)-to-many (Table2) I get the three weeks from table 1 or vice versa the four weeks of Table2 but not the 5 weeks that I am looking for shown in Qry Resul

When I try to create a relationship based on Table 3 to Table 1 and 2 I get the msg of SQL ambiguous outer joins that I need to force the creation of a first join and then use it for the second join??

Can anyone please point me to the right direction of creating this union

Thanks a lo

Juan C Buitrago

 

[JohnFol]

Have a look at cross-tab queries

Hello

I've been trying to combine three tables into one, without any success.

Here's an example of what I would like to accomplish

Table1
WE Q1
12/7/03 100
12/21/03 50
12/28/03 120

Table2
WE Q2
11/30/03 10
12/14/03 5
12/21/03 5
12/28/03 12

Table3
WE M
11/30/03 Nov
12/07/03 Dec
12/14/03 Dec
12/21/03 Dec
12/28/03 Dec

QryResult
WE M Q1 Q2
11/30/03 Nov 10
12/07/03 Dec 100
12/14/03 Dec 5
12/21/03 Dec 50 5
12/28/03 Dec 120 12

As you can see Table1 contains information for WE that sometimes the same

WE it's included in Table2, so when I do a one-to-one relationship I only
get WE 21 & 28, if I chose one (Table1)-to-many (Table2) I get the three
weeks from table 1 or vice versa the four weeks of Table2 but not the 5
weeks that I am looking for shown in Qry Result

When I try to create a relationship based on Table 3 to Table 1 and 2 I

get the msg of SQL ambiguous outer joins that I need to force the creation
of a first join and then use it for the second join???

 

[John Spencer (MVP)]

Something like the following UNTESTED SQL statement might work.

SELECT Table3.WE, Table3.M, Q1, Q2
FROM (Table3 LEFT JOIN Table2
On Table3.WE = Table2.WE)
LEFT JOIN Table1
On Table3.WE = Table1.WE

IF not, try making one query to get the first set of records and then using that
as part of a second query.

SELECT Table3.WE, Table3.M, Q1
FROM (Table3 LEFT JOIN Table2
On Table3.WE = Table2.WE)


SELECT QryOne.WE, QryOne.M, Q1, Q2
FROM QryOne LEFT JOIN Table1
ON qryOne.WE = Table1.WE

Another method might be to make a UNION QUERY from Table1 and Table2 and then
use that query as the basis for a crosstab query.