Linking Databases: Query Problem! 1

[aageorge]

I created a query involving two tables from two different databases. The following are my tables:
Database 1 Database 2
Date Line Production Date Line Scrapped_Qty.
8/14 A 1000 8/14 A 100
8/14 B 900 8/14 B 75
8/15 A 1100
8/15 B 950

I created a simple query linking the date and time and I got:

Date Line Production Scrapped_Qty.
8/14 A 1000 100
8/14 B 900 75

I am trying to get the following output:

Date Line Production Scrapped_Qty.
8/14 A 1000 100
8/14 B 900 75
8/14 A 1100 0
8/14 B 950 0

One obvious way is to have zero entries in database 2. I want to aviod going down this route. Any suggestions will be appreciated.

 

[DanChard]

You might try something like this:

select table1.date, table1.line, table1.production, nz(table2.scrapped_qty,0)
from table1 left join table2
on table1.date = table2.date;

Obviously you'll have to substitute in the correct table names and make sure the column names are OK but that's the gist of it.

Good luck...

 

[SuicidED]

Try this
SELECT Table1.Date, Table1.Line, Table1.Production, Table2.Scrapped
FROM Table1 LEFT JOIN Table2 ON (Table1.Line = Table2.Line) AND (Table1.Date = Table2.Date);

Change table1 etc to the name of your table in the database

 

[DanChard]

Oh yeah, I forgot to join on line as well as date. If you want 0s instead of blank spaces in the scrapped column you should use nz to convert them though.

 

[SuicidED]

Hi Dan

I was inputting at the same time you where. At least we agree.

Regards Eddie

 

[aageorge]

Thanks guys,

I used the left join property and it worked like a charm.