combine three tables into one

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Have a look at cross-tab queries





Juan C said:
Hello

I've been trying to combine three tables into one, without any success.
Click to expand...
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
Click to expand...
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
Click to expand...
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???
 
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.
 
Back
Top