Adding tables in SELECT query, messes up Sum calculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Darren Kozey - 25 Apr 2005 19:37 GMT

I have a multi-tab subform where on one page I am summarizing data from
other pages on the form. Because I am using calculated text boxes, the
record source for the form is a SELECT query as follows:

SELECT tblEstimateMaterials.*, tblEstimateHoursEpic.*,
tblEstimatehoursTrades.* FROM tblEstimateMaterials, tblEstimateHoursEpic,
tblEstimatehoursTrades;

On the summary page, I have a text box control with this as its control
source:

=Sum([tblEstimateMaterials.Quantity]*[tblEstimateMaterials.UnitPrice])

The problem is that with the other tables in the SELECT query present, the
calculation is wrong...in fact it is quite a bit higher than it should be.
If I remove the Sum portion of the expression, it works. With the Sum part
of the expression, it seems almost that it is summing something in all
tables in the SELECT query combined, because the number is so high.

Does anyone know what is happening here?

Thanks,
Darren

Reply to this Message

Sprinks - 25 Apr 2005 19:56 GMT

Hi, Darren.

I believe you need to place your summary field in the footer of your
subform, and then refer to it in the main form:

=Me!MySubform.Form!MySummaryControl

Hope that helps.
Sprinks

> I have a multi-tab subform where on one page I am summarizing data from
> other pages on the form. Because I am using calculated text boxes, the
[quoted text clipped - 19 lines]
> Thanks,
> Darren

Reply to this Message

Darren Kozey - 26 Apr 2005 02:12 GMT

Sprinks, Thanks for the response.

I'll try to describe better what is going on, and what I am doing.

First, I don't have any headers or footers in these subforms...

I'm not sure what you mean by refering to it in the main form. I have a
fairly complex (at least in my mind) set of forms, subforms, and tabs.

1) Main form is a multi-tab form, and most of the fields are from a main
table.

2)There is one page (or tab) on the main form that contains 2
subforms...but no problems with the way they are functioning.

3)One page(tab)on the main form contains a subform that is also a multi-tab
form. This subform does not have an underlying table, nor does one of the
pages on it (the summary page) have an underlying table (hence the SELECT
query). The other pages of this multi-tab subform contain their own
tables/subforms (sub-subforms?). The summary page (tab) is where I am
placing my text box controls in order to do my calculations from the
underlying tables of the other pages (tabs) on the subform. It is also the
page/sub-subform that has the SELECT query to bring in the required fields
for the calculated summaries.

It is this subform and its pages/sub-subforms that do not use headers and
footers. Are they needed? Does the multi-tab subform need a record source?
Is this how it gets synchronized with the main form? (I do not have
synchronization right now).

If I only have the "tblEstimateMaterials" table in the SELECT query for the
sub-subform, the sum calculation is perfect. If I add any other table (see
my example) that I will eventually need to bring in fields on this page,
the calculation goes caputt.

I guess the question is: why is the addition of another table in the SELECT
query affecting my calculation?

Sorry for the long-winded and perhaps confusing post.

Thanks,
Darren

Reply to this Message

Sprinks - 26 Apr 2005 15:55 GMT

Darren,

I'm not sure exactly what is going on; perhaps one of the VIP wizards can
clarify, but I don't think your approach is sound.

Use Access to help you--you'd like to calculate a summary field for a
recordset fully defined on your subform. The natural place to do this then
is on the subform's footer. You can then place a textbox on your main form
summary form whose ControlSource is set to the name of the summary control,
using the proper syntax to refer to a subform control:

=MySubForm.Form!MySubformControl

See the Subtotal control on the Orders form in the sample Northwind database
(on the Help menu), which gives a total of all line items in the Order
Details subform.

Note that you can set the summary control's Visible property to No, and
minimize the space allocated to the footer so that there is no impact to your
application.

Hope that helps.
Sprinks

> Sprinks, Thanks for the response.
>
[quoted text clipped - 38 lines]
> Thanks,
> Darren

Reply to this Message

Darren Kozey - 26 Apr 2005 19:36 GMT

Hi Sprinks.
Thanks for opening my eyes a little. I've done basically what you said, and
it seems to be working well.

One other question...when I go and change data in my subforms, then switch
to the summary page, it does not immediately show the updated data. I have
to advance, and return using the record selector before the controls show
the updated data.

How can I have all the summary fields updated as soon as the summary page
is in focus?

I've tried using a requery macro, but it doesn't work for me so far...I'm
probably not using it correctly.

Any thoughts?

Thanks again,
Darren

Reply to this Message

Sprinks - 26 Apr 2005 21:16 GMT

Darren,

This may be a repost; but I don't see the message I thought I'd posted about
an hour ago.

Me.Recalc

forces a recalculation of all calculated controls. Place the code in an
appropriate event procedure.

Hope that helps.
Sprinks

> Hi Sprinks.
> Thanks for opening my eyes a little. I've done basically what you said, and
[quoted text clipped - 15 lines]
> Thanks again,
> Darren

Reply to this Message