Setting up a database to record data as at a particular time

  • Thread starter Thread starter David Beamish
  • Start date Start date
D

David Beamish

I want to set up a database that maintains a historical record and
includes information (such as a person's address or surname) that can
change over time but has only one value at any one time. One way to do
it might be to set up a one-to-many relationship with a table that
records the required information and also has a "start date" field. But
then, in any query or report, it would be necessary to find a way to
link the main record to the associated record with the latest start date
not later than the date as at which the information is required. This
sounds messy. Is there an elegant way to design a database to achieve
what I am trying to do? All suggestions gratefully received!
 
Hi
Don't know about elegant, or all the ins and outs from your description.

First I'd set up a general VBA function. ' not detailed code

function find_rec (req_date as date, <other criteria>) as long
' setup the recordset DAO or ADO as required by your database
strSQL = SELECT * FROM tbl WHERE fldDate >= req_date AND <other
criteria> ORDER BY fldDate
rs.Open strSQL, ....
rs.MoveFirst
find_rec = rs.Fields("<name of primary key>").Value
' close up recordset and all
end function

Then where required filter <primary key> = find_rec(<value of
req_date>,<etc>

Marc
 
Many thanks - you reassure me that I have not missed anything! And the
advice on what the VBA code should look like is most helpful.
 
Back
Top