I have 2 tables.



Table A

--------------

COUNTER     PRICE     TOTAL

-------   ----------  --------

GGGG         5.40       NULL

OAGC         3.50       NULL

BBKK         4.00       NULL



Table B

----------------

COUNTER    YEAR    QUANTITY

-------   ------  -----------

GGGG       1998       10

GGGG       1999       15

GGGG       1997       20

OAGC       1997       13

OAGC       1998       15

OAGC       2000       16

OAGC       1999       14

BBKK       1998       20

BBKK       1999       22      





1) How to select the most recent QUANTITY value for each counter from 

table B ?



e.g The latest quantity for GGGG is 15 at 1999, OAGC is 16 at 2000 and 

BBKK is 22 at 1999.



COUNTER  YEAR  QUANTITY

------------------------

GGGG     1999   15

OAGC     2000   16

BBKK     1999   22



2) How to get these quantity to times with the value of PRICE from table A 

to get the total and insert 

the total value into table A?



e.g  TOTAL for GGGG = 15 * 5.40

    TOTAL for OAGC = 16 * 3.50

    TOTAL for BBKK = 22 * 4.00 

  

and finally the Table A will look like this:



COUNTER     PRICE     TOTAL

-------   ----------  --------

GGGG         5.40       81.00

OAGC         3.50       56.00

BBKK         4.00       88.00



I am using Access and ASP. Is there anyway to construct a function that 

will handle all these task? 

I just want to get the table A filled in with values of Total. 

1) Try



SELECT MAX(a.Year), a.Counter, a.Quantity

FROM TableB a

GROUP BY a.Counter, a.Quantity



2)

Not sure how you're going to do this. Why are you tables arranged the way

they are? What's the primary key on the second table? Perhaps if you could

explain in more detail what your entities and their attributes are, we could

help more



Cheers

Ken

Try using an SQL subselect with the MAX function -- you can use this in

place of a table name:



SqlSubSelectString = "( SELECT MAX( left( cStr(Year),4 ) & '- ' " _

& " left(Counter,4) & '-' & right( '000' & cStr(Quantity),3 ) ) AS MaxString

" _

& " FROM [TableB]" _

& " GROUP BY Counter ) AS B"



Does this get you started?