Add a column to a table with info from another table

[CostReport101]

Sounds easy, but here it comes.

I have following tables:
tbl1
code sub code total

tbl2
sub code total

I need:
tbl3
code tbl1 sub code tbl1 total1 tbl1 total2 tbl2 but only for those
records where the sub codes matches tbl1. cells where the sub codes do not
match should stay empty.

it should look like this:
code sub code total1 total2
123 A 5,000 500
345 B 10,000
678 C 80,000 1,000

When I do a regular query combining tbl1 and 2 it puts all the numbers in one
column, but I need 2 columns. 1 showing the total1 and 2 showing total2, not
combined since I want to add a column called total3 where I subtract total2
from total1.

I hope that makes sense. PLEASE HELP!!!!!!!!!!!!!!!!!!!!!!!!!!!

Thanks a bunch!

 

[Vincent Johns]

You might try this:

SELECT tbl1.code, tbl1.[sub code],
tbl1.total1, tbl2.total2
FROM tbl1 LEFT JOIN tbl2
ON tbl1.[sub code] = tbl2.[sub code];

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Marshall Barton]

Sounds easy, but here it comes.

I have following tables:
tbl1
code sub code total

tbl2
sub code total

I need:
tbl3
code tbl1 sub code tbl1 total1 tbl1 total2 tbl2 but only for those
records where the sub codes matches tbl1. cells where the sub codes do not
match should stay empty.

it should look like this:
code sub code total1 total2
123 A 5,000 500
345 B 10,000
678 C 80,000 1,000

When I do a regular query combining tbl1 and 2 it puts all the numbers in one
column, but I need 2 columns. 1 showing the total1 and 2 showing total2, not
combined since I want to add a column called total3 where I subtract total2
from total1.

Well, I don't know what you've tried, but I think this will
do what you asked:

SELECT tbl1.code, tbl1.subcode,
tbl1.total As Total1,
tbl2.total As Total2,
tbl1.total - Nz(tbl2.total, 0) As Total3
FROM tbl1 LEFT JOIN tbl2
ON tbl1.subcode = tble2.subcode

 

[CostReport101]

Thank You, Marshall! It worked! I spent a lot of time trying to figure this
out! Thanks again!

Sounds easy, but here it comes.

[quoted text clipped - 21 lines]

combined since I want to add a column called total3 where I subtract total2
from total1.

Well, I don't know what you've tried, but I think this will
do what you asked:

SELECT tbl1.code, tbl1.subcode,
tbl1.total As Total1,
tbl2.total As Total2,
tbl1.total - Nz(tbl2.total, 0) As Total3
FROM tbl1 LEFT JOIN tbl2
ON tbl1.subcode = tble2.subcode