Inner Join Question

[galantis]

Hi,

I am a newbie to SQL and MS access, and from I can tell, it's a simple join question. Can anyone please help me with this question?

Table1:
Name, Type, CurrentWeight,CurrentAmount
AC31,Air Coolers,141.606, 299,721.44
AC32, Air Coolers, 338.494, 52,117.57
AC34, Air Coolers,90.238, ,765.439
PM01, PAU's, 555.815, 617.29
PM13, PAU's, 736.222, 984.737

Table2:
Name, Type, PrevWeight, PrevAmount
AC32, Air Coolers, 258.000, 251.507
AC34, Air Coolers, 84.000, 384.94
PM01, PAU's, 160.00, 251.51

i would like the resulting table to be:
Name, Type, CurrentWeight, CurrentAmount, PrevWeight, PreAmount,
AC31,Air Coolers,141.606, 299,721.44,0, 0
AC32, Air Coolers, 338.494, 52,117.57, 258.000, 251.507
AC34, Air Coolers,90.238, ,765.439, 84.000, 384.94
PM01, PAU's, 555.815, 617.29, 160.00, 251.51
PM13, PAU's, 736.222, 984.737, 0, 0

Thanks in advance.

 

[Jon K]

You can use an outer join and the Nz() function.

SELECT Table1.Name, Table1.Type, Table1.CurrentWeight, Table1.CurrentAmount,
Nz(Table2.PrevWeight)+0 AS PrevWeight, Nz(Table2.PrevAmount)+0 AS PrevAmount
FROM Table1 LEFT JOIN Table2 ON Table1.Name = Table2.Name;
.