[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Calculated fields in Access for multiple tables

Posted on 2011-10-27
Medium Priority
Last Modified: 2012-05-12
I am trying to design a basic database that consists of two tables.  One will be all of the initial info to record a patient being set up for payroll deduction.  The second is to record the payments as they come in.  What I'd like to accomplish (If it's doable) is having a calcualted field for Current Balance that will take the starting balance and deduct any payments made that are recorded on the second table.  I am very limited w/ my knowledge in Access so any advice is welcome...as long as it's nice.  I've attached the file since there's no data in it yet.  Thanks! HVIC-Payroll-Deduction.accdb
Question by:shaneleach
LVL 44

Accepted Solution

Arthur_Wood earned 400 total points
ID: 37039750
It is almost NEVER a good design to have a 'calculated' field in a table.  The problem being that it is almost impossible to keep the 'calculated' value in sync with the underlying values.

It is a much better design to have the 'calculated' value be created, as need, using a query.  This would only be necessary when the values were to be displayed, say in a report, or a text box on a form.

LVL 75
ID: 37040014
A2010 (and maybe A2007) has a new 'feature' in tables ... which does the calculated field thing automatically - supposedly.  Not sure I would trust it however.


Assisted Solution

BusyMama earned 400 total points
ID: 37040117
Hi, Shane-

I agree with AW.  I would suggest a couple of modifications to your database.

Technically, you probably want to have a Patient table, that houses Patient ID and Patient Name, and anything else associated with the patient.

Then you would have your Patient Agreement table, but remove Patient Name, Payment Amount and Current Balance.  This will link to the Patient table using Patient ID.

You also keep your Patient Payments table, but again remove Patient Name.  You want to add your Patient Agreement ID to this table so that you can join the payments back to the correct agreement (in case there is more than one agreement per patient).

Once you have done all of this you can create a new query to give you the calculated field.  When you create the query, you can choose the fields you want to show (Patient Name, Begin Balance, etc.).

To make the calculated field you would do something like this:

CurrentBalance: [BeginBalance]-Sum([PmtAmt])

I say "something like this" because it will really depend on your modifications to see how best to make this work.  But, the calculated field name is prior to the colon (CurrentBalance), then you put the calculation after the colon.


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37040636
I think I have a sample demonstrating this, ...I'll post it tonight if I get a chance
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 200 total points
ID: 37040690
LVL 19

Expert Comment

by:Richard Daneke
ID: 37041590
To address the calculated field, Access 2010 has added this new field option.  IMO, it would be best suited to calculate a value based on other fields in the current record.  
For example, Quantity * UnitPrice = LineTotal.   LineTotal in prior versions would be calculated in a query, form, and/or report.   Now, it can become a calculated field in the table.  
Another example, First Name and LastName can now be calculated in the table as Name (= [FirstName] & " " & [LastName])  and be available in the table record.
This becomes even more important when the table is not stored in Access, but linked to a SharePoint list.  Yet, even within Access it reduces some of the extra development for queries or unbound calculated controls.
LVL 93

Expert Comment

by:Patrick Matthews
ID: 37041779
Access 2010 does have the new calculated field option, but Allen Browne has written a pretty authoritative explanation why it ought not be used:

LVL 75
ID: 37041839

"Even more serious, the calculated results are not reliable. If you change the Expression after data has been entered into the table, the existing results may not be updated correctly (through new records are edits are updated), so you cannot rely on the results. A compact/repair does not recalculate, so there is no obvious way to repair the bad results."

You would think that ... of all the things Microsoft could have done/added/field, we get another partially implemented 'feature' like this, which is akin to Name Auto Correct !


Author Comment

ID: 37044263
Ok, so I'm sensing a theme....lol.  Thanks so much for the input from all of you.  I was trying to save myself some work since I will be the one maintaining the records, but it looks it will be best done through a query or pivot table.  

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question