Automatic data entry based on other data

[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.

 

[Steve Schapel]

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.

 

[Kirkat98]

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

 

[Piet Linden]

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

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...

 

[Steve Schapel]

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.

 

[Tony Toews [MVP]]

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.

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/