MODIFY DATA IN TABLE

[pelagic]

Hi All,
I have Access table named tblCatDog, which included the following:
SUB CATEGORY PRICE BEFORE PRICE AFTER
CAT 100 200
CAT 105 200
CAT 150 200
CAT 250 200
CAT 300 200
DOG 400 500
DOG 450 500
DOG 550 500
How would I modify the price before to the smallest and to largest value base on SUB_CATEGORY?
For example: All CAT price before would be 100
All DOG price before would be 550
The new table would be something like this
SUB CATEGORY PRICE BEFORE PRICE AFTER
CAT 100 200
CAT 100 200
CAT 100 200
CAT 100 200
CAT 100 200
DOG 550 500
DOG 550 500
DOG 550 500

I have tried update query with the MIN() AND MAX()
function but it does not work. I am still trying to
figure this out. Any hint would help.

Thanks in advance
regard,

 

[PHV]

Have you tried this ?

UPDATE tblCatDog t1
   SET [PRICE BEFORE]=(
       SELECT MIN([PRICE BEFORE]) FROM tblCatDog t2
        WHERE t2.[SUB CATEGORY]="CAT")
 WHERE t1.[SUB CATEGORY]="CAT"

If this works, you can easily do the MAX stuff for DOG.

Hope This Help
PH.