A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Left Join



 
 
Thread Tools Display Modes
  #1  
Old November 27th, 2006, 07:51 PM posted to microsoft.public.access.queries
mustish1 via AccessMonster.com
external usenet poster
 
Posts: 9
Default Left Join

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200611/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:14 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.