Interesting and Urgent Join Query Question

[pinecrest515]

Dear forumers,

I would like to consult urgently on the following:

Suppose I have two tables in Access, and want to join them:

Table 1:
Company This_Year_Sales
Disney 1000
Pepsi 2000


Table 2:


Company Last_Year_Sales
Coke 3000
Target 1000

I want to join these tables together so that all the information is presented, with three columns: Company, This_Year_Sales, Last_Year_Sales, like this:

Company This_Year_Sales Last_Year_Sales
Disney 1000 n/a
Pepsi 2000 n/a
Coke n/a 3000
Target n/a 1000

How do I do it?

[pbaldy]

Presuming you have a table with all possible companies, you'd use a LEFT JOIN against that table:

FROM CompaniesTable LEFT JOIN Table1 ON CompaniesTable.Company = Table.Company

You can add the other once you see how that one looks in design view. You can use the Nz() function to replace the Null value when a company doesn't have a record in Table1 with "n/a".

[pinecrest515]

The problem is that I don't have an actual master table for company. Suppose the company names listed in Table1 and Table2 are different. Table1 maps companies to last year sales, Table2 maps companies to this year sales. I want a joined table listing all companies in Tables 1 and 2, with two columns, one for last year sales and the other for this year sales. It's ok if some companies don't have last year sales and some companies don't have this year sales.

* please keep in mind that the table and data is fake, a very simpified mock version of the real tables.

[pbaldy]

In place of a master table, you can use a UNION query to get a list of distinct companies from the two tables.

[pinecrest515]

I tried the following UNION ALL query. However, I am getting an error from Access saying I cannot Union inside a sub-query. Help!!

SELECT
company,
last_year_sales,
this_year_sales
FROM
(
SELECT
company, 0 as last_year_sales, this_year_sales FROM
Table1
UNION ALL
SELECT
company, last_year_sales, 0 as this_year_sales FROM
Table2) x
GROUP BY
company, last_year_sales, this_year_sales

[pbaldy]

That's actually not the way I was thinking of going, but it would work. Let the UNION query stand alone, then make a second query a totals query that groups on company and sums the other two fields. That will pull any companies with data in both tables back into one record.

[pinecrest515]

I tried just doing the UNION/UNION ALL first, but got an error saying SELECT statement includes an argument name that is missing or mispelled.

SELECT
company, 0 as last_year_sales, this_year_sales FROM
Table1
UNION ALL
SELECT
company, last_year_sales, 0 as this_year_sales FROM
Table2

SELECT
company, 0 as last_year_sales, this_year_sales FROM
Table1
UNION
SELECT
company, last_year_sales, 0 as this_year_sales FROM
Table2

[pbaldy]

Not all together, right? I'd expect this to work:

SELECT company, 0 as last_year_sales, this_year_sales FROM
Table1
UNION ALL
SELECT company, last_year_sales, 0 as this_year_sales FROM
Table2

Are those your actual field names? If not, you might try bracketing them, in case you have a reserved word in there. Can you post the db, or a representative sample?

[weekend00]

Besides all above replies, you can try another UNION query like this:

select table1.company,this_year_sales,last_year_sales from table1 left join table2 on table1.company=table2.company
union
select table2.company,this_year_sales,last_year_sales from table1 right join table2 on table1.company=table2.company where table1.company is null