Newbie Design Humps!

[Poida3934]

I have a table of student records, and would like to have a field showing
age at start of school year.
I'd like to have it calculated as the user goes through a student
maintenance form. The student date
of birth I have in a field, and in a separate "school details" table, the
start of school year date.
A datediff formula does the work, but at present it's a calculated field on
my form (as per the formula example I found on the net). My first problem
is how do I get the the actual table field to calculate itself from the
formula, or alternatively, set the table field equal to the calculated field
once I've passed through the field on the form?
My second problem is how do I load the start of school year date from my
school details table, whilst I'm in my student maintenance form, looking at
my student records table?. At present, my formula is looking at todays date
instead of start of school year date, until I work out how to access the
field.
I though of basing my form on a query, but the school details table has just
one record, bearing no relationship to any of the data in the student
records file.

In Anticipation!!
Poida3934

 

[Beetle]

First of all, you should not attempt to store a calculated value in your
table. As resident expert and Access MVP John Vinson says;

"Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact."

If your calculation is working fine as it is in your form, then you don't
need to do anything. If it ain't broke, don't fix it.

As to your second problem, you can use DLookup to retrieve the start of
school year value from that table. You will find info about DLookup in you
Access help file.

HTH

 

[Jamie Collins]

almost
any calculation will be MUCH faster than a disk fetch

I don't get this. It might apply to an OLTP northwind type app but
what about 'calculation engines' that use Jet as a dumb (possibly
denormalized) data store? Have you never pressed F9 to force an Excel
workbook to recalculate and seen the progressbar/meter in the
statusbar? it doesn't take too many thousands of cell formulas for
calculation time to exceed a Jet disk read. Then there are the
calculations at which SQL sucks e.g. amortization...

Jamie.

--

 

[Klatuu]

Create a hidden control on your form. Make its control source a DLookup that
returns the school year start date. Then reference the control in your age
at start of school year.

One other thing. A raw DateDiff will not necessarily be accurate. It
depends on when the students birthday is in relation to the compare date. It
can be off by a year. Here is a function that will correctly return an age:

Public Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
Format(DateToday, "mmdd"), 1, 0)
End Function

 

[Beetle]

Hi Jamie

I've never actually tested this theory. I was quoting John Vinson, who I am
sure knows more than I ever will about Access. I'm just a regular guy (not an
MVP or anything) so I just try to respond to people that have relatively
simple issues that I think I might be able to help with.

I have seen many of your responses in this group, so I know that you, as
well, know alot more about Access than I do (to be truthful, I don't even
know what you're talking about in some of your responses). You are probably
right that there are certain situations, perhaps in large, complicated
databases, where a disk fetch might be faster than a calculation. I don't
want to put words in John's mouth, but I believe that his statement is
directed more towards people with "average" DB's (if there is such a thing).
In other words, the kind of people that are most likely to be posting
questions in a group like this.

If you really want to know the basis of his claim, I guess you'll have to
ask him because I don't know

 

[John W. Vinson]

I have seen many of your responses in this group, so I know that you, as
well, know alot more about Access than I do (to be truthful, I don't even
know what you're talking about in some of your responses). You are probably
right that there are certain situations, perhaps in large, complicated
databases, where a disk fetch might be faster than a calculation. I don't
want to put words in John's mouth, but I believe that his statement is
directed more towards people with "average" DB's (if there is such a thing).
In other words, the kind of people that are most likely to be posting
questions in a group like this.

If you really want to know the basis of his claim, I guess you'll have to
ask him because I don't know

You got it in one, Beetle.

There *are* indeed queries for which a stored calculated value is necessary -
often (as Jamie suggests) large total or count queries where many records must
be opened to get the answer. I *try* (perhaps not always successfully) to give
my stock answer in cases where the calculation is happening all in one record;
a typical beginner mistake is to store (for example) Birthdate and Age as
fields in the same table.

John W. Vinson [MVP]