A little problem (may be simple!)

[Atlas]

Access 2003 and .adp project (MS SQL Server 2000 based).

I have master(A) & detail(B) tables (1 to N):

(A): id_A, date, Description
(B): id_B, id_A, Description, valX, valY

in a classic continuos form + continuos subform fashion.

As the mainform (A) is a continuos form, I would like to show in each row,
the sums of valX and valY.
So the continuos form would show in each row something like:

textBoxId_A, textBoxDate, textBoxDecsription, textBoxSumX, textBoxSumY
textBoxId_A+1, textBoxDate, textBoxDecsription, textBoxSumX, textBoxSumY
textBoxId_A+2, textBoxDate, textBoxDecsription, textBoxSumX, textBoxSumY
textBoxId_A+3, textBoxDate, textBoxDecsription, textBoxSumX, textBoxSumY

...........
and the subform, for each record in (A) :

textBoxId_B,textBoxId_A, textBoxDecsription, valX, valY
textBoxId_B+1,textBoxId_A, textBoxDecsription, valX, valY
textBoxId_B+2,textBoxId_A, textBoxDecsription, valX, valY

...............................
.......with linkMaster/linkChild set on id_A.

Due to the nature of Access the simplest way to achieve that would be to set
the data source of textBoxSumX and textBoxSumY to 2 functions (VBA) that
perform a SELECT SUM on ValX and ValY.

That's not the best of life, running 2 queries for each row in master to get
the totals.....

So I was wandering if there is a better (and faster) way to achieve this,
like setting the mainform control source to query where (B) is "innerjoined"
and SUMs are executed straightfully inline (is it possible?). Something
like:

SELECT A.id_A, date, Description, id_B, B.id_A, Description, SUM(valX) AS
sumX, SUM (valY) AS valY
FROM A
INNER JOIN B ON A.id_A = B.id_A

I've tried this but it seems like your not allow to perform such a
query.....

Any hint appreciated
Thanks

 

[Atlas]

Help anyone????