Updating other fields based on another calculated field

[Yarra518]

I am doing project work for a department of a hospital which treats children. When they reach the age of 18, they need to be referred to an alternative service provider. So, I need to keep tabs on who is too old to be seen by our clinic, on an ongoing basis that needs weekly, if not daily, updates.

The fields/controls I’m referring to in the following are enclosed in inverted commas (except in string expressions), to make it easy to identify them.

“PresentAge” - calculation in Form/Query

=DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

This gives the correct current age, making allowance for whether or not the birthday has been reached in the current year or not.

“Over 18 ?” – Yes/No field, with a check box on the form and in the table. Those born in 1986 are now starting to turn 18.

Default is not set, but can be changed to “No” if required

“Occupation” – usually recorded as Adult or Child (Child being the default), but also includes other occupations where we have this information. A pain, but useful where the nature of their work/studies will have an impact on the service we provide to them, and the eventual service provider to whom they are referred. It also has some relevance to the fields relating to funding sources, such as WorkCover, Transport Accident Commission etc.

Question:-

How do I set up a calculation so if “PresentAge” is >17 (or greater than and equal to 18), the “Yes/No” box shows Yes, and the “Occupation” field shows Adult?

I presume I use the “PresentAge” calculation with some additional criteria, but I have no idea how to do this in Access.

At present, I’m manually selecting this in a table, by sorting on the “DOB” field and ticking the “Over 18 ?” box and changing Child to Adult where applicable, so it shows up on the form correctly. Hardly ideal and it leads to mistakes being made, or the job not being done unless a new report is wanted.

I know calculated fields aren’t supposed to be stored in a Table, but there are times when this would be more efficient than revising queries and forms etc. And it can be done in Excel, so I can’t for the life of me see why it shouldn’t be possible in a database too. (I’ve yet to encounter a good explanation). Future editions of Access may see to this for us! Fingers crossed…

Any advice on creating an auto update in the Table and/or the Form will be much appreciated.

Cheers,

Heather in Melbourne, Vic, Aus.

 

[Mile-O]

“Over 18 ?” – Yes/No field, with a check box on the form and in the table. Those born in 1986 are now starting to turn 18.

You can dispense with this field as it is unnecessary and, in effect, a calculable value.

“Occupation” – usually recorded as Adult or Child (Child being the default), but also includes other occupations where we have this information. A pain, but useful where the nature of their work/studies will have an impact on the service we provide to them, and the eventual service provider to whom they are referred. It also has some relevance to the fields relating to funding sources, such as WorkCover, Transport Accident Commission etc.

You need a new table for occupations and the primary key of that new table relates this field which is turned into a number data type and called OccupationID.

How do I set up a calculation so if “PresentAge” is >17 (or greater than and equal to 18), the “Yes/No” box shows Yes, and the “Occupation” field shows Adult?

I presume I use the “PresentAge” calculation with some additional criteria, but I have no idea how to do this in Access.

At present, I’m manually selecting this in a table, by sorting on the “DOB” field and ticking the “Over 18 ?” box and changing Child to Adult where applicable, so it shows up on the form correctly. Hardly ideal and it leads to mistakes being made, or the job not being done unless a new report is wanted.

As I've said already, ditch it.

You shouldn't be binding a table directly to a form either. Use a query to gather the information you want.

Therefore, in your query you can calculate the Age and then set the criteria to be over or under 18 as you like. The query is then bound to the form.

I know calculated fields aren’t supposed to be stored in a Table, but there are times when this would be more efficient than revising queries and forms etc.

Oh? Please cite some...

And it can be done in Excel, so I can’t for the life of me see why it shouldn’t be possible in a database too.

...because Excel is a spreadsheet application used for data analysis and Access is a database application used for data management.

(I’ve yet to encounter a good explanation). Future editions of Access may see to this for us! Fingers crossed…

Tables are just raw data waiting to presented. Excel offers manipulable data; Access stores data.

I think you are very misguided in the difference between Access and Excel - don't make the assumption that because a database table looks like a spreadsheet that it can do the same things as a spreadsheet.

For further reference look up these terms: Normalisation, First Normal Form, Second Normal Form, and Third Normal Form.