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
1  1114 
I don't understand why you would ever have a negative value such as -3:
In this scenario, you assigned a prize without the person having
reached the required number of points. co*********@gmail.com wrote:
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Japhy |
last post by:
Hello. I am writing my first web based PHP/MYsql application.
I have used the following code to retrieve the hightest value in a
field :
<?php
mysql_connect("localhost", "root", "") or...
|
by: Michael Levin |
last post by:
I've got the following problem. I'm a biologist and I have a device at work
which monitors my frog habitat. The device has a bunch of sensors, and runs
an embedded html server with some java...
|
by: Karen |
last post by:
Hi
This may be a really easy answer for you! I usually work with Oracle
but have an Access project to work on.
I have a table of clients (name, addr1, addr2, addr3, pcode etc) which
I need...
|
by: Bob Alston |
last post by:
I am looking for any Microsoft Access based software that could be used
for a United Way agency that provides basic needs assistance - food,
clothing, financial (rent, utilities, Rx, gasoline,...
|
by: Marc Verdonck |
last post by:
First:
I have some troubles in access, some fields needs to begin with a Capital
letter.
Example: louiselaan need to be Louiselaan.
How can I become this?
Second:
I'm exporting my database to...
|
by: William |
last post by:
My first time posting to clj , I have already read
http://jibbering.com/faq/#FAQ2_3 before posting.
javascript function in question:
saveText() is as follows:
function saveText(...
|
by: javelin |
last post by:
I know, that statement can make me lots of enemies. I am sorry, but
I've worked with a SQL Server back end with an MS Access ADP on the
front end, and the process of creating queries, and...
|
by: Paul Revere |
last post by:
Hi, all of you! I am new to this community. I am a web designer and a friend of mine and I have a server and manage websites (mostly ours but also a few clients). I manage the sites and he does...
|
by: Brian Tkatch |
last post by:
Is there a way to check the order in which SET INTEGRITY needs to be
applied? This would be for a script with a dynamic list of TABLEs.
B.
|
by: DayzedandConfused |
last post by:
Here I go..
I haven't worked with Access for almost a decade and due to recent abrupt job change need to write a simple data base. I am creating a data base to reference a larger one. It needs...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
