Linking Data in a Table to Autopopulated Data in a Form

[PastorD]

I apologize if I'm not asking the question correctly.

I am working with a db that stores records of people who are attending summer camp. The cost is connected to several factors: 1. The persons age

(field) Camp Fee

=IIf([Camp Attending]="Senior","$180.00",IIf([Camp Attending]="Junior","$170.00",IIf([Camp Attending]="Intermediate","$170.00")))

2. The date the app is received

(field) Pre-Pay Discount

=IIf([Postmarked Date]<="5/3/2010","$40.00")

and 3. Special discounts.

I have been able to auto-populate certain "cost" fields based on answers to the above questions. However, the amounts which are automatically entered in the fields from the data entry form do not show up in the corresponding table. Also, when tabulating the "balance due", after pre-pay discounts and various other discounts are taken, the calculation doesn't seem to work with the auto populated fields and doesn't post to the corresponding table.

(field) Balance Due
=([campfee]-[Pre Pay Credit]+[tshirt]-[siblingdiscount]-[amtenclosed]-[Add'l Payment])

This seems simple but not sure what I'm doing wrong. New at this, so please bear with me,

[Jim Doherty]

@PastorD
Welcome to bytes PastorD:

What you are doing there is creating calculations in controls. Calculations like this are worked out on the fly and are not bound to any field in your table neither should they be. You are new to Access so will not yet know how things work generally but a few pointers:-

1) Your tables stored the data
2) Your queries query the tables
3) Base your forms on queries not the tables themselves

4) You will appreciate when you learn more that you can in actual fact create those calculations as virtual columns in a query. The effect being that the calculation appears as a column of useable data

[PastorD]

Thanks Jim!

I have a solid understanding of spreadsheets. I guess I'm trying to apply the same knowledge to db expecting it to work the same way.

So, if I understand correctly, I should only use calculations in the form so I can protect the integrity of the data in the table. Better to use queries and reports to make those final calculations based on good data from the table. It's hard to not want what I see in the form go directly into the table when I feel good about the accuracy of the data.

I need "balance due" calculations for reports. Still working on figuring out the best way to get that info.

[Jim Doherty]

@PastorD
For the most part you should not need to store calculated data. on the basis that if the underlying stored data on which the calculation is based were to change for some reason (maybe an 'update' later on or something like that) then you would also have to consider the stored static calculation as well.

It is of course lovely to see absolutely everything in your table but actually, you should have little need to look at your table in principle, because your table is really only a data storage bucket having a data indexing capability to facilitate speedier searching on appropriate columns.

The majority of your work should be via queries....or as you will learn, as time goes by these in conjunction with inbuilt functions and bespoke functions that you might make yourself using VBA language in modules.

There are certain caveats I consider personally to storing data that can be 'calculated' is a matter for you to decide ultimately.

One such example caveat I believe for storing calculated data when dealing with people records and that is the AGE of a person if the sole purpose of storing it is essentially to compare against another datetime item of data in the same row.

We know that a persons age can be calculated from a date of birth but the reality is that at any given time in history a persons age is 'fixed' it only increases in numeric value as future time goes by 'Age' therefore can be comfortably stored as static in my view. This is by no means the only example

The advantages of static storage of calculated data of course are evident imagine querying 100,000 records for those records grouped by Age of the person. You can immediately query a fixed value without having to work out the persons age for each row, which would be the case were the value calculated for each row everytime. If this field is also indexed then it will also be substantially quicker to return any resultset of data. In short, the computer has less processing to do per row.

There are inevitably UP sides and DOWN sides arguments as to the data storage principles, The end decision however is yours and is a matter of your design

Regards