Sum values in another table

[Sheila D]

I know I should never have calculated fields in a table but the problem I
have is this. An existing database storing invoice details based on 1 value
per invoice now needs to change to cater for several line items per invoice.
No problem - a 1 to many relationship. The difficulty is that several reports
are based on the total invoice amount currently in the 1 table and I have
only been allocated 1 day to do this job.

So if I have several line items in the many table can I 'roll up' the
invoice total to the one table. I know that I will need to do it in any form
that allows the figures to change but that seems to me a quicker option than
modifying all existing reports which I could not possibly do in 1 day. Any
suggestions much appreciated - I'm not very good at VB though so hope that is
not the answer.

Sheila

 

[Tom van Stiphout]

On Sun, 11 Jan 2009 10:09:01 -0800, Sheila D

Management should give you enough time to accomplish a task, and you
should be able to clearly explain why a particular task takes (more
than) a certain amount of time.
I understand your reports are currently bound to a table. It takes
only a few minutes to create a totals query that essentially returns
the columns of that table, plus has a calculated field for the sum of
the lineitems. The current Sum field in the table should be renamed
(my convention is to rename to z<ObjectName>) so it is not
accidentally used somewhere. Then changing the recordsource property
of those reports to your new query is also just a few minutes of work.
A change like this is important enough to warrant a beta test cycle.
That's a whole topic by itself, but the basics is that you would have
people exercise the new version against a beta back-end.

-Tom.
Microsoft Access MVP

 

[Sheila D]

Thank you for your response Tom. I understand everything you say but would
still like to know whether it is possible to roll up the values.......