auto field update

[Will 2823]

I tring to use the date in one field in a database to update another field in
the same database automaticly (Primary date {field 1} – 280 days
=secondary date {field 2}) Please break it down barny style I’m new at this.
Thanks

 

[Douglas J. Steele]

You don't.

Having a field whose value is completely derivable from values of one or
more other fields in the same row is actually a violation of database
normalization principles. What happens if you update {field 1} and forget to
update {field 2} or vice versa: how will you know which one's correct?

The appropriate thing to do is only store {field 1} in your table, and
create a query that has a computed field to represent {field 2}. You'd then
use the query wherever you would otherwise have used the table.

 

[Pete D.]

Is this something your doing one time or are you planning on storing the
date twice in the same datafile? If it is a onetime update you can just run
an update query. If you plan on making it a normal feature of your datafile
it may not be the best way to do it. To use the update query you will need
something in each of the tables that will relate the records together so
each date in table 2 is related to a specific record in table 1. As you
want it at the Barny level rather than typing it all out check these two
sites for instructions, pictures. They don't include music but I suspect
you'll still get along...
http://www.databasedev.co.uk/update_query.html
http://office.microsoft.com/en-us/access/HA100765271033.aspx

Also, rarely is it a good idea to store the same information in one table in
another table unless it is used to create the relationship in the datafile.
Pete

 

[John W. Vinson]

On Sat, 27 Dec 2008 00:06:01 -0800, Will 2823 <Will

I tring to use the date in one field in a database to update another field in
the same database automaticly (Primary date {field 1} – 280 days
=secondary date {field 2}) Please break it down barny style I’m new at this.
Thanks

As Douglas and Pete correctly say, your secondary date field *should simply
not exist* if it can be calculated from the primary date.

To do the calculation in a Query, use the DateAdd function; create a query
based on your table, include whatever fields you want to see, and in a vacant
Field cell type:

SecondaryDate: DateAdd("d", -280, [PrimaryDate])

Suggestion: don't use the default Field1, Field2 fieldnames, use something
meaningful; don't put blanks or special characters in fieldnames, it's allowed
but can be a hassle later; and don't use reserved words such as Date as
fieldnames.