Left Join

[mustish1 via AccessMonster.com]

Hi: There are three tables in my database. Table Main I use to give stars and
table Star Redemption is used to give prize. There is another table name
Prize which is linked with table Star Redemption. In table Prize it shows how
many points employee will get on the basis of the Prize. My question is how
do i write a query so that I subtract total number of points from table Main
to table Star Redemption. For example Acito Lisa has total stars in table
main is 347 and Acito Lisa has total stars from table Star Redemption is 325.
So i need the difference which will be (347-350) -3. In Table Star Redemption,
the stars are define in table Prize. Also I need a left join in table Main so
that if there is no record of any employee in table Star Redemption it still
shows record from table main with total stars.

Thanks.

Main
----
ID Date Name #_Stars
1725 11/22/2005 Acito Lisa 1
2379 6/30/2006 Acito Lisa 6
2376 5/30/2006 Acito Lisa 25
2326 4/30/2006 Acito Lisa 25
2344 5/30/2006 Acito Lisa 10
1583 10/17/2005 Acito Lisa 1
2346 5/30/2006 Acito Lisa 5
2339 5/31/2006 Acito Lisa 2
1956 1/31/2006 Acito Lisa 5
1210 8/29/2005 Acito Lisa 5
2145 3/31/2006 Acito Lisa 10
1907 1/31/2006 Acito Lisa 10
2742 9/30/2006 Acito Lisa 4
1650 10/31/2005 Acito Lisa 5
1014 7/27/2005 Acito Lisa 10
2224 4/28/2006 Acito Lisa 5
2227 4/30/2006 Acito Lisa 10
962 6/30/2005 Acito Lisa 5
1619 10/26/2005 Acito Lisa 2
2253 4/30/2006 Acito Lisa 5
1034 7/27/2005 Acito Lisa 10
953 6/30/2005 Acito Lisa 3
1041 7/29/2005 Acito Lisa 1
1133 8/25/2005 Acito Lisa 2
929 6/30/2005 Acito Lisa 5
856 6/9/2005 Acito Lisa 1
908 6/29/2005 Acito Lisa 2
1599 10/21/2005 Acito Lisa 1
1849 12/30/2005 Acito Lisa 5
2178 3/31/2006 Acito Lisa 5
1123 8/25/2005 Acito Lisa 5
2771 9/30/2006 Acito Lisa 5
2304 5/31/2006 Acito Lisa 4
822 5/30/2005 Acito Lisa 5
2842 10/19/2006 Acito Lisa 1
1588 10/19/2005 Acito Lisa 10
940 6/30/2005 Acito Lisa 6
279 11/30/2004 Acito Lisa 2
1257 9/2/2005 Acito Lisa 3
1552 10/5/2005 Acito Lisa 5
390 1/25/2005 Acito Lisa 5
1818 12/29/2005 Acito Lisa 2
2816 9/30/2006 Acito Lisa 10
1522 9/23/2005 Acito Lisa 10
1251 8/31/2005 Acito Lisa 5
1332 9/2/2005 Acito Lisa 3
1524 10/4/2005 Acito Lisa 1
1492 9/30/2005 Acito Lisa 2
1490 9/30/2005 Acito Lisa 5
2614 8/31/2006 Acito Lisa 5
2701 9/29/2006 Acito Lisa 2
3039 11/22/2006 Acito Lisa 10
2692 8/30/2006 Acito Lisa 5
36 8/6/2004 Acito Lisa 2
1744 11/30/2005 Acito Lisa 2
2472 6/30/2006 Acito Lisa 25
1718 11/17/2005 Acito Lisa 2
2415 6/30/2006 Acito Lisa 5
1759 11/30/2005 Acito Lisa 5
1793 12/8/2005 Acito Lisa 3
2477 7/28/2006 Acito Lisa 6
1972 2/10/2006 Alfarone, Valerie 10
1984 2/13/2006 Alfarone, Valerie 5
1835 12/30/2005 Alfarone, Valerie 3
1644 11/4/2005 Alfarone, Valerie 2
1749 12/5/2005 Alfarone, Valerie 2
2107 3/17/2006 Alfarone, Valerie 2
2058 3/2/2006 Alfarone, Valerie 5
2101 3/16/2006 Alfarone, Valerie 5
2758 9/30/2006 Attanasio, Jennifer 4
2743 9/30/2006 Attanasio, Jennifer 6
2942 10/30/2006 Attanasio, Jennifer 4
2859 10/25/2006 Attanasio, Jennifer 5



Star Redemption
---------------
ID Date Name Prize
308 11/27/2006 Acito Lisa Come in Late
249 11/24/2006 Acito Lisa $25 Amex Gift Check
191 8/10/2006 Acito Lisa $100 Amex Gift Check
192 8/10/2006 Acito Lisa $50 Amex Gift Check
193 8/10/2006 Acito Lisa $25 Amex Gift Check
147 12/28/2005 Acito Lisa $25 Amex Gift Check
98 10/12/2005 Acito Lisa $100 Amex Gift Check
144 12/28/2005 Barnes, Sylvia $25 Amex Gift Check
250 11/24/2006 Barnes, Sylvia $25 Amex Gift Check
66 7/20/2005 Barnes, Sylvia $50 Amex Gift Check
239 9/27/2006 Barnes, Sylvia $50 Amex Gift Check
86 10/12/2005 Barnes, Sylvia $25 Amex Gift Check

Prize
----
ID Prize # Stars Dollars
1 Come in Late 25 $0.00
2 Extended Lunch 25 $0.00
3 Leave 1 hr Early 25 $0.00
4 $50 Amex Gift Check 50 $50.00
5 $100 Amex Gift Check 100 $100.00
6 $25 Amex Gift Check 25 $25.00
7 Dress Down Week 25 $0.00
8 $75 Amex Gift Check 75 $75.00
9 $125 Amex Gift Check 125 $125.00
10 $150 Amex Gift Check 150 $150.00
11 $175 Amex Gift Check 175 $175.00
12 $200 Amex Gift Check 200 $200.00