Looks difficult to me but.....

[Atlas]

......may be stupid for somebody else!

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

 

[Vadim Rapp]

Hello Atlas:
You wrote in conference microsoft.public.access.adp.sqlserver on Sat, 12
Jun 2004 16:09:49 +0200:

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

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

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

A> in a classic continuos form + continuos subform fashion.

Does Access 2003 allow that? Access 2002 does not. I.e., you can't have
continuous form if it has subform.


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

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

If I understand correctly, you are not afraid to display all child records
for each record of the main form, but you are afraid to display two totals?
Hmm.


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

You can create in the subform an invisible calculated textbox txtTotalX with
controlsource = "=Sum(VaxX)"; then in the main form your field with total of
X will have controlsource = MySubform.Form.txtTotalX.



Vadim

 

[Atlas]

A> in a classic continuos form + continuos subform fashion.

Does Access 2003 allow that? Access 2002 does not. I.e., you can't have
continuous form if it has subform.
Yes.



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

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

If I understand correctly, you are not afraid to display all child records
for each record of the main form, but you are afraid to display two totals?
Hmm.

Vadm thanks for answering,

Worst case child records is up to 10/15.
Anyhow, even if it looks like this, it's not only summing up the columns but
also filter out some childs in the sum.

 

[Marshall Smith]

The best way to produce a join such as you are discussing is to create a
view that groups on id_A, and sums valX and valY. Then, join that view to
the master table.

 

[Vadim Rapp]

Hello Atlas:
You wrote on Mon, 14 Jun 2004 17:25:46 +0200:

??>> Does Access 2003 allow that? Access 2002 does not. I.e., you can't
??>> have continuous form if it has subform.

A> Yes.

What's amazing, I looked at MS site and did not find this information. Their
page http://www.microsoft.com/office/editions/prodinfo/compare.mspx about
new features in Office 2003 mentions exactly 2 new features of Access:
Improved error checking and Field property changes. I wonder, who is Office
target customer in the imagination of MS marketing people.

thanks,

Vadim