Calculate sale price

[zenfold]

Here is the table:

The assignment is to create a query that will update the prices t
reflect a 75% off sale. Can anyone help?



ID Acquired PPrice LPrice Manufacturer Units In Stock Produc
Name Discontinued
1 5/19/2003 $599.99 $799.99 Proform 13 treadmill No
2 6/30/2002 $299.99 $499.99 Quest 58 recumbent bike No
3 4/13/2000 $499.99 $899.99 Nautilus 24 bowflex Yes
4 9/24/2003 $599.99 $799.99 Proform 29 elliptical No
5 3/28/2003 $29.99 $40.99 Body Trends 58 VHS/DVD No
6 5/9/2001 $199.99 $399.99 Sears 58 treadmill No
7 4/6/2002 $599.99 $799.99 Quest 59 weight set No
8 12/5/2003 $59.99 $79.99 Proform 40 yoga mat No
9 11/28/2002 $599.99 $899.99 Sears 47 rowing machine No
10 3/24/2001 $299.99 $499.99 Quest 58 crossbow No
11 2/3/2004 $199.99 $159.99 Proform 24 weight bench No
12 1/31/2004 $29.99 $49.99 Home Body 88 weights No
13 2/14/2004 $15.99 $20.99 Sears 129 VHS/DVD No
14 3/6/2004 $899.96 $1,099.99 Nautilus 101 bowflex 2 No
15 2/12/2004 $456.36 $623.99 Home Body 59 weight bench No
16 3/6/2004 $56.99 $88.99 Proform 98 pilates starter No
17 3/6/2004 $99.99 $209.99 Proform 56 home gym No
18 3/4/2004 $86.39 $139.98 Sears 189 soccer set No
19 5/4/2003 $199.99 $599.99 Quest 258 stair climber No
20 2/28/2004 $29.99 $39.99 WW Fitness 59 basketball No
21 3/15/2004 $15.99 $25.99 Home Body 125 wrestling mats Yes
22 4/25/2001 $299.99 $599.99 Quest 52 bike No
23 3/16/2004 $45.99 $129.99 Quest 99 yoga kit No
24 1/25/1999 $299.99 $499.99 Home Body 29 skiing machine No
25 2/25/2004 $159.99 $179.99 Proform 26 weight set No
26 1/25/2003 $199.99 $299.99 Sears 59 basketball net No
27 2/29/2004 $25.99 $55.99 Home Body 33 yoga DVDs No
28 3/11/2004 $39.99 $59.99 Quest 104 pilates ball No
29 2/22/2004 $15.99 $20.99 Home Body 69 jump rope No
30 2/13/2004 $5.99 $10.99 Home Body 49 gym socks No
31 3/14/2004 $20.99 $30.99 WW Fitness 49 sports bra No
32 2/18/2004 $128.99 $135.99 Nike 128 white shoes No
33 3/16/2004 $158.99 $179.99 Reebok 149 black shoes No
34 5/17/2003 $49.99 $79.99 Home Body 28 massage wand No
35 10/25/2003 $199.99 $299.99 Quest 258 treadmill No
36 11/19/2000 $250.99 $599.99 WW fitness 149 treadmill No
37 4/15/2004 $300.99 $500.99 Sears 59 home gym No
38 3/16/2004 $199.99 $299.99 Quest 144 bike No
39 2/11/2001 $29.99 $59.99 Home Body 24 yoga mat No
40 3/15/2004 $29.99 $39.99 Spalding 58 basketball N

 

[Nikos Yannacopoulos]

What you need is a simple Update query like:

UPDATE TableName SET TableName.LPrice = [LPrice] * 0.25

The question is, though, is this the best way to do it? Will you not be
selling the same products at the original price after the sale period?
Generally speaking, in a well designed system, pricing would be kept in a
separate table, with a product ID field (linked 1-to-many to the main
product table), a price field and a start or end validity date, so you can
handle different prices for the same product over time, as well as keep
pricing history. You could even have another field for price list, and
assign different price lists to different customers.

HTH,
Nikos