Automatic data entry based on other data

  • Thread starter Thread starter Kirkat98
  • Start date Start date
K

Kirkat98

I have two date fields in one of my tables "appointment date" and "follow-up
survey date" and I want the follow-up date to automatically get entered as 6
months following the appointment date. Do you know how I can do that?
I am using Access 2003.
 
Kirkat,

There would normally be no need to store the "follow-up survey date" in your
table. You can probably simply remove this field. When you actually *need*
this information, which would be on your forms or reports, you can easily
calculate it "on the fly" based on appointment date plus 6 months. For this
you can use a DateAdd() function. You would use it either in a calculated
field in the query that your form or report is based on, or else in the
Control Source of an unbound textbox on your form or report itself.
 
Thanks Steve,

Why did you determine that the follow-up survey date is not needed in a
table? I am really new to access and trying to design a database for a large
research project. Is there a book you would suggest I buy to that end?

Kira
 
Thanks Steve,

Why did you determine that the follow-up survey date is not needed in a
table? I am really new to access and trying to design a database for a large
research project. Is there a book you would suggest I buy to that end?

Kira
Click to expand...

If the date is consistent (6 months away from some stored date), you
can just write a query:

SELECT...DateAdd("m", 6, [Appointment Date]) As FollowupDate
FROM...
 
Kira,

There are a lot of good books about Access out there, and I certainly would
recommend that you get yourself one if you are going to pursue working with
Access.

Regarding this specific question, it's a general principle of database
design. There is no need to store data that can be calculated or derived
from existing data. In a sense it's redundant. If you have the appointment
date, and if the follow-up date is always 6 months after the appointment
date, then... if you know the appointment date, you automatically know the
follow-up date. Therefore, to store the follow-up date in the table is
equivalent to storing the same information twice.

Hope you can understand what I mean. It's a style of thinking you develop
when working with databases.
 
Kirkat98 said:
I have two date fields in one of my tables "appointment date" and "follow-up
survey date" and I want the follow-up date to automatically get entered as 6
months following the appointment date. Do you know how I can do that?
I am using Access 2003.
Click to expand...

Please ignore Aaron's posting as Aaron's answer to just about every question is SQL
Server and ADPs. No matter how appropriate his answer.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Back
Top