Storing updates?

[dd_bdlm]

Hi all

I have a database that stores customer records and their associated
insurance details. I need to be able to track any changes made to that
record within a set time period ie if details are changed fine it is
reported next time report is run. But if details are changed more than
once in between reports then first detail change is lost.

I would like to work out a way that changes can be written to a
??seperate version of the table?? and stored in there to be reported
seperately and not just the final change as is the snapshot nature of a
report? Is this possible? Could I possible make another version of the
main table appended onto it? I feel I 'know' what I need to do but not
how to even begin it! Any help would be appreciated.

DDiver

[Alan Webb]

One of Ralph Kimball's books, The Data Warehouse Toolkit, has a reference
design for a data warehouse for an insurance company. The schema given in
this book will do exactly what you want. The book is $30.00 US or so and
well worth the money. The schema may have been published by Kimball in an
article on the web but I don't have a URL for you.

--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS
<dd*****@yahoo.co.uk> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi all

I have a database that stores customer records and their associated
insurance details. I need to be able to track any changes made to that
record within a set time period ie if details are changed fine it is
reported next time report is run. But if details are changed more than
once in between reports then first detail change is lost.

I would like to work out a way that changes can be written to a
??seperate version of the table?? and stored in there to be reported
seperately and not just the final change as is the snapshot nature of a
report? Is this possible? Could I possible make another version of the
main table appended onto it? I feel I 'know' what I need to do but not
how to even begin it! Any help would be appreciated.

DDiver

[pietlinden]

There's even a new version out... I have the old one... trudging my way
through it... was supposed to take a class in it for my duhgree, but
I'm told the class was "probably the worst one I've had here" by one of
my classmates. (And why do we pay tuition again?)

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How is your db set up? Do you have more than one table? It sounds like
you'd, at least, need a Personnel table to hold the demographic info
about customers (name, address, DOB, etc.), and a table of their
insurance contracts. E.g.:

CREATE TABLE Contracts (
person_id int not null references Personnel ,
contract_type int not null references ContractTypes ,
start_date datetime not null ,
end_date datetime null , -- null means still active
-- other columns
CONSTRAINT PK_Contracts
PRIMARY KEY (person_id, contract_type, start_date)
)

The Contracts table has a start_date & end_date, this will allow you to
make a history of the person's contracts. If you need to track
something else about a specific contract you can create another table
that has that info w/ start/end dates. That table would be a history of
changes to contracts.

Obviously, you can keep on creating tables as your specifications become
more particular. Read a good book on database design. I usually
recommend _Database Design for Mere Mortals_, by Hernandez.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkwxioechKqOuFEgEQJ8NACfT5nai8lUYVoy/czk90lzJPB3KmYAnik1
M+1WmyhRgMGSxO6Hq7xcBBvH
=vHEx
-----END PGP SIGNATURE-----

dd*****@yahoo.co.uk wrote:

Hi all

I have a database that stores customer records and their associated
insurance details. I need to be able to track any changes made to that
record within a set time period ie if details are changed fine it is
reported next time report is run. But if details are changed more than
once in between reports then first detail change is lost.

I would like to work out a way that changes can be written to a
??seperate version of the table?? and stored in there to be reported
seperately and not just the final change as is the snapshot nature of a
report? Is this possible? Could I possible make another version of the
main table appended onto it? I feel I 'know' what I need to do but not
how to even begin it! Any help would be appreciated.

[Jerry Porter]

Here's what I did in one of my applications:

1. I created a copy of each table that I wanted to track, with an extra
date/time field called DateModified. I prefixed these tables' names
with hst, so the archive table for tblClients would be hstClients. You
do need a system to make sure the table definitions say up-to-date.

2. I created a procedure like the following to archive the new version
of a changed record in a form:

Public Sub ArchiveRecord(fForm As Form, HistTable as string)
Dim rForm As Recordset, rHist As DAO.Recordset
Dim fld As Field
On Error GoTo ErrHandler

' Copy new data to history table
Set rForm = fForm.RecordsetClone
If rForm.RecordCount <> 0 Then
rForm.Bookmark = fForm.Bookmark 'find record shown in form
Set rHist = AdminDB.OpenRecordset(HistTable)

rHist.AddNew
' Use history table for reference of which fields to use,
' since form might be based on query
For Each fld In rHist.Fields
fld = rForm.Fields(fld.Name)
Next
rHist.Update
End If
ExitLabel:
On Error Resume Next
rHist.Close
rForm.Close
Set rHist = Nothing
Set rForm = Nothing

Exit Sub

ErrHandler:
' <error handling code here>
Resume ExitLabel
End Sub

3. In the after update of the form (e.g. for the table tblClients):
ArchiveRecord Me, "hstRatesGeneral"

If I were to write this today, I might generate and execute a SQL
statement, instead of using the AddNew method. But this works fine.

Jerry

[Alan Webb]

Peit Linden,
We pay tuition so we can get the books and hear from the professors the
orthodox way to do things. The demands of our clients and the realities of
business requirements often mean that the orthodox way isn't what we end up
doing. But it's a lot worse to flounder through a project thinking our
problems are new and unique than to study what is already known and decide
to flounder through anyway.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS

<pi********@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

(And why do we pay tuition again?)