How to get started? (calculations)

[kbrooks]

Our facility has a financial assistance program (FAP) that will pay for a percentage of their bill if they qualify...based on family size and annual income. Currently they use several spreadsheets to record the data, and do a lot of manual calculations. I'm going to set up a database, but want some guidance before I get started to I don't set it up completely screwy. I've never done a calculated field before and I'm not exactly sure how.

I have about 15 fields that will require input from the patient. The key field will be their Medical Record Number (MR#). Then I have 3 fields that will need calculations:

FAP Payment: This would be Total_Charges x Percentage

Patient Balance: This sould be Total_Charges minus Third_Party_Payment minus Patient_Payment minus the above FAP_Payment.

Total Amount: This one is more confusing. They want a total for that patient, by MR#, for the last 12 months. Not 12 months from the current date, but 12 months from the Date_of_Determination, which they will manually key in.

A few questions. The biggest one is how exactly do I calculate these fields? And do I need to create the fields in the table, or will they be created after calculations? Also, can I just have one table or do I need several?

Thanks in advance for any help you can give me.

 

[DBL]

Access isn't designed to store dynamic calculations. What you store is all the fields that make up the calculations and then recreate the end totals in either unbound fields in forms or reports.

So you need to dermine what makes up a calculation and that is the data you store. Using an invoice system as an example. The purchaser buys 2 books at 4.99. You have stored the cost of the book in the products table so you would create a calculation to multiply 4.99 by 2 to give the total but you wouldn't save that total. A good starting point is to remember you only want to store each set of information, cost of book, once. If you then went on to give that customer a discount of 10% you could also incorporate that in the final total. If the dicount scale changes, you would want to store that purchasers discount rate in the purchaser table.

I hope that helps and hasn't confused you more! Come back if you need further clarification.

 

[The_Doc_Man]

The biggest issue that most people run into when they jump from Excel to Access is the mindset.

Access stores DATA in ordered, structured groupings called records. Excel stores anything (specifically including nothing.)

Access doesn't store computations in a table unless you really need it for an obscure reason. Instead, what Access allows you to do is to store queries for which one or more columns can be formulas. Now this sounds like a gyration of some sort, but here is the key to understanding it.

On today's faster systems, it is cheaper to recalculate simple values than it is to store the calculations. Space is more expensive than speed. Also, making the field a computation makes it dynamic and sensitive to changes in the underlying data.

So what good does this do you? Just this: When you need a form or report that seems to depend on table data but you really, REALLY need a computation included, you can use a QUERY (with that computation included as one of the columns) any place that a table could have been used. (With the warning that if you use it in a form, the computed field won't be directly updatable. It will only change if its base value changes.)

How do you include a computation?

Well, in a query grid (Click NEW, then Design View, that's the query grid), one of the columns can be your raw data, the next column could be computed. In the FIELD row for that column, just make up a name that isn't an Access reserved word. Put in a colon as a delimeter in that same box. Then put in your formula.

In reports, you can refer to the computed column by the name you gave it (in front of the colon). Ditto forms and other queries.

Does this help adjust your mindset away from Excel a little better?