populating on field based on the value of another

[BJM]

I have a table containing student information. One field
is the student's birthdate. This field is populated from
external data. I would like the value of this field to
automatically populate a Yes/No checkbox field indicating
weather or not the student is under 21. (A funding thing)

Thanks

 

[Ken Snell]

You can do this either via a form or via a query, but not directly in the
table. Can you give a bit more info about when/how you want this to be done,
so that we can make some suggestions?

 

[BJM]

Ken,
I'd be happy to supply more information:

I have a database that is intended to keep student
attendance records for our local self-reliant, continuing
education department. Many of our tables (Teacher,
Student, Course...) are populated weekly from external
data, imported in the form of CSV text files sent from our
board office.

Our administration wishes (as I said, for funding
purposes) to keep track of how many of our students are
under the age of 21. The student's birthdate is included
in the student record information sent from the board
office. My boss wants to include a boolean field in our
student table that, by way of a checkbox, will indicate at
a glance weather or not the student fits the under 21 bill.

Without me having to populate that field for all 900 some-
odd records initially and then any additional records down
the road, I would like to find a way to have that Yes/No
checkbox field populate automatically whenever a new
student record is added to the table via the Imports from
the board office.

I hope this makes sense...

************

Also, if I may, another design-oriented roadblock I have
come up against:

I am storing the attendance information in a table
horizontally. This table contains a field for each week in
our school year in the form of a week number. My boss
wishes to have a weekly report that includes attendance
information from the previous three weeks. (He is looking
for unexplained instances of no attendance for extended
periods of time) I have a parameter query that prompts the
data entry clerk running the report for an opening week
number and then a closing week number.

Is there a way I can have a report control capture that
information and convert it to a calendar date?

Thanks for your help,
BJM

 

[John Vinson]

Our administration wishes (as I said, for funding
purposes) to keep track of how many of our students are
under the age of 21. The student's birthdate is included
in the student record information sent from the board
office. My boss wants to include a boolean field in our
student table that, by way of a checkbox, will indicate at
a glance weather or not the student fits the under 21 bill.

Your boss should either learn some basic principles of relational
databases, or be willing to listen to you as the expert. S/he is
wrong.

The existance of such a field in the table IS BAD DESIGN and IS NOT
NECESSARY. Also, it presumes that the boss will be looking at the
table datasheet directly - bosses should never be allowed to do so
<bg>.

The data seen by the boss, or any other *user* of the data, should be
displayed upon a Form or a Report (for printing). This Form or Report
can be based on a Query with a calculated field:

Under21: [DOB] < DateAdd("yyyy", -21, Date())

This calculated yes/no value can be displayed in a checkbox control on
the Form or Report, and will accurately reflect the student's age
anytime. If the yes/no field is stored in the Table, you'll need to
run an update query EVERY SINGLE DAY in order to ensure that you don't
miss someone's 21st birthday.

 

[Ken Snell]

I agree with John's comments about the "under 21" checkbox.

With regard to converting a week number to a date, yes, this can be done on
a form. What is the conversion equation that needs to be used?