enter zero if no record found?

[jozino01]

hi,
is there any way to do a query, which would read two tables/queries like

table1
date
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005

table2
date amount
May 2005 500.00
June 2005 1,258.50
October 2005 247.00

and the output of that new query would be like:
new query
date amount
March 2005 0.00
April 2005 0.00
May 2005 500.00
June 2005 1,258.50
July 2005 0.00
August 2005 0.00
September 2005 0.00
October 2005 247.00
November 2005 0.00

 

[PHV]

You need an outer join:
SELECT A.date, Nz(B.amount,0) AS Amount
FROM table1 AS A LEFT JOIN table2 AS B ON A.date = B.date

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jozino01]

thanks, this is it.

can it be expanded by another table/parameter

table2
date acct amount
April 2003 50 $24,065.95
April 2003 60 $6,776.72
April 2003 87 $110,836.18
April 2003 97 $9,457.97
April 2004 17 -$33,476.08
April 2004 51 $51.62
April 2004 52 $33.38
April 2004 60 $24,363.14

table3
acct
05
17
50
51
52
60
87
97

so the final output would include combination of all table1.date, table3.acct, table2.amount (with zero if no value for specific date/acct)