Add a column to a table with info from another table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CostReport101 - 05 Jan 2006 19:48 GMT

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!

Reply to this Message

Vincent Johns - 05 Jan 2006 21:21 GMT

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 <vjohns@alumni.caltech.edu>
Please feel free to quote anything I say here.

> Sounds easy, but here it comes.
>
[quoted text clipped - 25 lines]
>
> Thanks a bunch!

Reply to this Message

Marshall Barton - 05 Jan 2006 21:29 GMT

>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

Signature

Marsh
MVP [MS Access]

Reply to this Message

CostReport101 - 05 Jan 2006 23:45 GMT

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 - 11 lines]
>FROM tbl1 LEFT JOIN tbl2
> ON tbl1.subcode = tble2.subcode

Reply to this Message