row-level versioning of data -- how to do this???

[tlyczko]

Hello,

I wonder if someone would be willing to help me out??

I could not think of a better subject line.

I am designing an Access db which maintains data about internal agency
audits.

Audits are about locations providing services, and about people
receiving services.

Each audit has one program, one program location, and multiple people
receiving program services at the program location.

Audits - Location is 1-1.
Audits - People is 1-M.

The questions for each audit are very similar for locations and people,
so are the question topics.
Location and people questions are related to the program and to topics
within the program.

Audits - Location Question Answers is 1-1.
Audits - People Question Answers is 1-M.

I have a junction table that holds references to question text,
question sorting, question topic, question program, all to know which
questions are to be asked for which kind of audit. I plan to review
again if I need this (I'm returning to this project after a long
hiatus), but it's quite likely that I do need the questions set up this
way.

My question is, what is the best way for me to VERSION the questions??

That is, if a question is changed or removed, what is the best way to
represent this in the database so that previous audits which asked this
question are not affected, but present and future audits reflect the
changed question or do not ask the removed question from the point in
time that the question's status changes??

Conversely, new questions can easily be added, they obviously won't be
represented in past audits but I need to make sure they are represented
in present and future audits.

Thank you,
Tom

If this is not enough information, please tell me.

Off-list replies are fine.

Thank you, Tom

 

[Dennis Snelgrove]

Off the top of my head, the first thing that occurred to me was to add
two Date fields to the Questions table; "StartDate" and "EndingDate".
The first tells the database when this question is "on", i.e. it's to
be asked. The second would tell the program what date the question is
no longer to be asked.

Hope this helps...

 

[tlyczko]

Mmmmmmmm...after posting my qq I am thinking I need a boolean that
denotes active/inactive every time a question is changed or removed or
added, sigh...Questions are always asked except when inactive...

 

[George Nicholson]

Or maybe an Audit version....?

Audit v.1 contained questions 1-30
Audit v 2 contained questions 1-25, 31-25
(a junction table with a unique index on the combination of AuditID and
QuestionID)

and an Audit could be marked Active/Inactive... It sounds like only the most
recent would be active, but the rest of the data would still be there for
any historical analysis.

HTH,