Date Stamps and creating a new record

[EJ]

I have a table that I am creating that will be for each country. Each
country has several variables, like size, population, vaccination rates,
education rates, etc. I have a form that will be the data entry point for
this table.

On the form, however, I would like for there to be a date field. If someone
goes into the form to change the data, I would like a date and time stamp to
be created and a new record added to the master table, but I would like the
form to show the most current data. In other words, I would like for access
to keep a record of all changes made and when the changes were made.
However, I would like the form or reports generated to have the most current
data. Does anyone know how to do this? I would appreciate any help that you
could give me. Thank you!

 

[Allen Browne]

3 tables, like this:

Attrib table (one record for each kind of attribute you wish to record),
with an AttribID primary key

Country table (one record for each country), with a CountryID primary key.

CountryAttrib table, with fields like this:
- CountryAttribID AutoNumber Primary key
- CountryID relates to Country.CountryID
- AttribID relates to Attrib.AttribID
- MeasureDate Date/Time when this measurement was made.
- Measure number the value of this attribute for this
country

So, the Attrib table will have a *record* for population, another record for
education rates, and so on. Over the years as you think of more things to
measure, you can add more records to this table.

Then build a form bound to the Country table, with a subform bound to the
CountryAttrib table. The subform will be in Continuous Form view, so it
shows one attrib per row, as many rows as you have values for.

In the subform, use a combo box to select the AttribID.
If you wish you can set the Default Value of MeasureDate to:
=Date()

Using this structure, you can record an attribute (such as population) many
times over the years (several rows, with different dates), and so chart
trends. The most recent date is the current measure.

In the CountryAttrib table, place a unique index on the combination of
CountryID + AttribID + MeasureDate, so you can always know for sure which is
the current measure.

 

[EJ]

Thank you for your prompt reply! Let me see if I can set this up, but a few
questions before I give it a shot:

1) Some of my attributes have multiple layers, for example, population
values coming from published reports, country reports, or expert opinion.
Each of those would have to be listed as a separate row, correct?

2) Will the =Date() pick up the current date?

3) where do you indicate the "unique index?" would this be another variable?

Thank you so much Allen.

 

[EJ]

Sorry question 4 would be, the records in the attribute table would be in the
rows, correct? not column? sorry, i know that that is a stupid question,
but i just wanted to clarify.

 

[Allen Browne]

Re #1
Yes: you could have an extra field in the CountryAttrib table to indicate
the source of the measurement. If one source provides several measure, you
would probably want to create a little table with one record for each source
you draw from, and a SourceID primary key. Then in CountryAttrib table, you
would have a SourceID field to indicate which source provided this
measurement.

Re #2
The Default Value is assigned at the moment the record is created. Setting
the value like that will record the date the entry was made. If in fact the
data was a few days old before you entered it, you could type in the actual
date instead.

Re #3
In table design view, open the Indexes dialog (from the ribbon/toolbar.)
In the first available row, enter a name for the index and the first field.
On the next row, leave the name blank (indicating this is part of the same
index as the previous row), and choose the 2nd field. Same for the 3rd
field. The dialog will now look something like this:
Index Name Field Sorting
======== ==== =====
Primary key CountryAttribID Ascending
CountryAttribDate CountryID Ascending
AttribID Ascending
MeasureDate Ascending
Select the index name, and in the lower pane of the Indexes dialog, set
Unique to Yes.

Re #4
Yes: each attribute is a *row*, not a column. That's an absolutely crucial
aspect of the design. It means it's really easy to add other rows in the
future, without having to modify you columns. So any forms, queries, reports
etc all work in future without needing to redesign the database schema.

 

[EJ]

Allen,
Thank you again! I hadn't replied because i was busy doing what you had
suggested. i got as far as trying to create the form, but i think that my
attributes (all 50 of them) are too long to make the subform usable. i have
a form in which i have created, that shows the information quite clearly, but
i think with it showing the continuous form, it isnt allowing me that
flexibility. is there any way to create the time stamp and everytime the
file is updated, a backup is made?

 

[EJ]

Hi allen,
me again, I just spoke with someone who said that it is possible to create a
"change log" where it was programmed into access that if a field in the form
was changed, it would trigger a secondary table to mark the old field and
value, the new value, and what time it was changed. It has to be done
programmatically. Do you know how to do this? Or what codes might prompt the
trigger?
Ah, Access fun.
Thanks again for all your help!
Emily

 

[Allen Browne]

You can use the form events to create a log of the previous values.

Here's an example of how it's done:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html
The next version of Access (2010) will have data macros on the tables (not
just the forms), so it will be easier.

Having said that, I'd still be considering the schema we discussed, and then
try to make the interface fit it. For example, you might consider placing a
tab control on the main form, with 2 pages. First page contains the current
values, and the 2nd tab takes you do previous values. Since you only have
current values on the first page, this reduces the visual overload, but the
older values are still there to work with when needed.

The RecordSource for these subforms would involve a subquery in the WHERE
clause. If that's new territory, see:
http://allenbrowne.com/subquery-01.html