Calculated fields in Access for multiple tables
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think I have a sample demonstrating this, ...I'll post it tonight if I get a chance
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
Access 2010 does have the new calculated field option, but Allen Browne has written a pretty authoritative explanation why it ought not be used:
http://allenbrowne.com/casu-14.html
http://allenbrowne.com/casu-14.html
"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 !
mx
ASKER
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.
mx