Results 1 to 2 of 2

Thread: Query Help

  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Query Help

    Hi: I post this question before but I am sorry i forgett to show the Prize table. There are three tables in my database. Table Main I use to give stars and table Star Redemption also used to give stars but with different type of prizes. There is another table name Prize which is linked with table Star Redemption. In table Prize it shows different prizes and number of stars on each prize. My question is how do i write a query so that I can subtract total number of stars 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 in query i need the name and the difference which will be (347-350) -3. In Table Star Redemption, it shows only the name of the prize but how many stars this prize hold, this information is 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

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178

    Thumbs up

    mustish1,

    You obviously have a master table containing all members (maybe it's called tblMembers) including Acito Lisa. In that table, add a field, maybe call it "MemStarBal" (member's star balance). Each time you assign a quantity of stars to a member in the Main table, update the MemStarBal field in the tblMembers table. Each time you subtract stars because of penalty (coming in late) or because of star redemption (such as a prize), again update the MemStarBal field in the tblMembers table. A simple update query will handle this. Initializing this field for first-time use is also fairly easy.

    Hope this helps,

    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •