(Simple?) MS Access Question

Factual Questions
1

It is a legal database. We have a table with Plaintiffs, and another table with Correspondence relating to each Plaintiff’s medical expenses incurred.

There are many more fields, but the relevant data:

Plaintiff Table: Plaintiff Name, Total Medical Expenses
i.e. John Doe, $____.00

Correspondence Table: Plaintiff Name, Individual Medical Expense
i.e. John Doe, $100.00
John Doe, $300.00

Query 1: Plaintiff Name, Sum Of All Medical Expense
i.e. John Doe, $400.00

The query works beautifully.
It adds all of the correspondence records for each plaintiff and keeps a running tally of each and every input in the Correspondence Table “Individual Medical Expense”.

What I want to do is have the running tally “Sum Of Medical Expense” populate a field in the Plaintiff form/table.

I know I have all the pieces of the puzzle, but I just can’t find a way to plop the Query field “Sum Of Medical Expenses” into the Plaintiff’s “Total Medical Expense” field in the Plaintiff table/form.

Every time I try, either the field remains blank, or I get “#Name?”

I am sure there is a very simple solution.

2

As an experienced database developer/designer I would have to ask, WHY???

One of the biggest problems with databases is having redundant data. It’s hard to maintain and too easy to get out of sync. If the Total Expenses can easliy be calculated by adding up the Individual Expenses there is no valid reason to store the Total Expenses in a separate place.

3

Agree with FatBaldGuy.

If you’re planning on using each plaintiffs’ total in a report or something, just base the report on the query, not on the original table.

There’s no reason to go through hoops to repopulate dynamic data into a source table. Just use the query, which you say works great.

4

Why? Yeah, that is a simple solution.

I am creating this for some attorneys and thought it would be a nice feature for them to open the database, see the relevant Plaintiff data and have a running tally at the bottom of the Plaintif form to let them know the total costs to date.

Then again, as you have both cleverly noted, there is no particular reason I have to or should do this.

5

All you need to do is to place a calculation field on your form, so that the information is pulled up, but doesn’t need to be stored in the database.

6

There’s you correct answer right there - and quick too :wink:

7

Thanks! And right simple as well.

This is always my problem…I can do intricate design and great reports, but will agonize for days/weeks over something that is stupidly simple.

Thanks again!