Table History for Newbie

Hey Group,

I have a table with the following Feilds:

MobilePhoneID, PrimaryUser, DepartmentID, CallOptions, MobileNumber, SDC,
GPRSTarrif (Lookup to Another Table), INumber, & ContractStartDate.

I would like when a User Modifys the above table, for example the INumber or
the Primary User for this to have a history, for example the phone is given
to J.Bloggs, but i want to know who previosly had it?

Is this possible?

Anybody got any links or could help?

Many Thanks
MCN

Sure. Here are a couple of links from the MS Knowledgebase which show one
way to do it:

Access 97: How to Create an Audit Trail of Record Changes in a Form
http://support.microsoft.com/default...b;en-us;183792

Access 2000: How to Create an Audit Trail of Record Changes in a Form
http://support.microsoft.com/default...b;en-us;197592

There are many ways to create an audit trail of changes to records,
depending on what kind of info you want in the audit trail, whether you want
to maintain it in the same table or in a separate table, etc. For another
look at this subject, here is a link to an excellent article by MVP Allen
Browne:

http://members.iinet.net.au/~allenbrowne/AppAudit.html



--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


"MadCrazyNewbie" wrote in message
...
Hey Group,

I have a table with the following Feilds:

MobilePhoneID, PrimaryUser, DepartmentID, CallOptions, MobileNumber, SDC,
GPRSTarrif (Lookup to Another Table), INumber, & ContractStartDate.

I would like when a User Modifys the above table, for example the INumber
or
the Primary User for this to have a history, for example the phone is
given
to J.Bloggs, but i want to know who previosly had it?

Is this possible?

Anybody got any links or could help?

Many Thanks
MCN

Cheryl,

Many thanks for you post? im using VB as my front end? should this matter?

Many Thanks
MCN

"Cheryl Fischer" wrote in message
...
Sure. Here are a couple of links from the MS Knowledgebase which show
one
way to do it:

Access 97: How to Create an Audit Trail of Record Changes in a Form
http://support.microsoft.com/default...b;en-us;183792

Access 2000: How to Create an Audit Trail of Record Changes in a Form
http://support.microsoft.com/default...b;en-us;197592

There are many ways to create an audit trail of changes to records,
depending on what kind of info you want in the audit trail, whether you
want
to maintain it in the same table or in a separate table, etc. For
another
look at this subject, here is a link to an excellent article by MVP Allen
Browne:

http://members.iinet.net.au/~allenbrowne/AppAudit.html



--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


"MadCrazyNewbie" wrote in message
...
Hey Group,

I have a table with the following Feilds:

MobilePhoneID, PrimaryUser, DepartmentID, CallOptions, MobileNumber,
SDC,
GPRSTarrif (Lookup to Another Table), INumber, & ContractStartDate.

I would like when a User Modifys the above table, for example the
INumber
or
the Primary User for this to have a history, for example the phone is
given
to J.Bloggs, but i want to know who previosly had it?

Is this possible?

Anybody got any links or could help?

Many Thanks
MCN

im using VB as my front end? should this matter?

A good deal of VB code can be used successfully in Access (and vice versa, I
suppose); however, the Forms and Controls in VB have vastly different
functionality and properties.

You may be able to modify the code provided in the two KB links I provided
or you could post your question to a VisualBasic newsgroup?


--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


"MadCrazyNewbie" wrote in message
...
Cheryl,

Many thanks for you post? im using VB as my front end? should this matter?

Many Thanks
MCN

"Cheryl Fischer" wrote in message
...
Sure. Here are a couple of links from the MS Knowledgebase which show
one
way to do it:

Access 97: How to Create an Audit Trail of Record Changes in a Form
http://support.microsoft.com/default...b;en-us;183792

Access 2000: How to Create an Audit Trail of Record Changes in a Form
http://support.microsoft.com/default...b;en-us;197592

There are many ways to create an audit trail of changes to records,
depending on what kind of info you want in the audit trail, whether you
want
to maintain it in the same table or in a separate table, etc. For
another
look at this subject, here is a link to an excellent article by MVP
Allen
Browne:

http://members.iinet.net.au/~allenbrowne/AppAudit.html



--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


"MadCrazyNewbie" wrote in message
...
Hey Group,

I have a table with the following Feilds:

MobilePhoneID, PrimaryUser, DepartmentID, CallOptions, MobileNumber,
SDC,
GPRSTarrif (Lookup to Another Table), INumber, & ContractStartDate.

I would like when a User Modifys the above table, for example the
INumber
or
the Primary User for this to have a history, for example the phone is
given
to J.Bloggs, but i want to know who previosly had it?

Is this possible?

Anybody got any links or could help?

Many Thanks
MCN

Hi Cheryl,

Do you happen to have links to samples for this idea/problem? Say you're doing a mileage expense report and the rate is .25 a mile, but then on May 5 the rate increases to .30 a mile. However, you know that some expense reports will have mileage for dates prior to May 5 as well as after the change and you want the appropriate rate paid.

Thanks in advance

rpw

----- Cheryl Fischer wrote: -----

Sure. Here are a couple of links from the MS Knowledgebase which show one
way to do it:

Access 97: How to Create an Audit Trail of Record Changes in a Form
http://support.microsoft.com/default...b;en-us;183792

Access 2000: How to Create an Audit Trail of Record Changes in a Form
http://support.microsoft.com/default...b;en-us;197592

There are many ways to create an audit trail of changes to records,
depending on what kind of info you want in the audit trail, whether you want
to maintain it in the same table or in a separate table, etc. For another
look at this subject, here is a link to an excellent article by MVP Allen
Browne:

http://members.iinet.net.au/~allenbrowne/AppAudit.html



--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


"MadCrazyNewbie" wrote in message
...
Hey Group,
I have a table with the following Feilds:
MobilePhoneID, PrimaryUser, DepartmentID, CallOptions, MobileNumber, SDC,
GPRSTarrif (Lookup to Another Table), INumber, & ContractStartDate.
I would like when a User Modifys the above table, for example the INumber
or
the Primary User for this to have a history, for example the phone is
given
to J.Bloggs, but i want to know who previosly had it?
Is this possible?
Anybody got any links or could help?
Many Thanks
MCN

one way is the check the 'isdirty' form property and write an audit table
record.

Rich

hi,

an audit trail isn't what I was thinking of. I'll try to explain using an example of a situation.

you're a salesman and you're paid commission on what you've sold, but the commission is paid only when all of the money has been paid by the customer. let's say that the commission rate in april is 10% and then the rate changes on 5/5/04 to 9.5%. so when those sales from april are finally paid in may, you do not want to be paid at the new rate. however, unlike storing a calculated price on an invoice table, i don't want to store the commission calculation. i want the 'effective' date of the rate change to be stored (an audit trail would time stamp the change to the field, which might have happened in january). with that date stored, the calculations could look up the date of the sale and select the commission rate that was valid at that time.

so, if that is as clear as mud now, do you know of a link to a sample that I could look at?

thanks in advance to anyone who can help

rpw

----- Jessestonecedar wrote: -----

one way is the check the 'isdirty' form property and write an audit table
record.

Rich

I suspect the easiest way to handle this is to simply store the commission
rate in your commissions table. If the commission changes it won't affect
the previously created records and, thus, the salesmen will get paid on the
correct commission rate for all sales.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"rpw" wrote in message
...
hi,

an audit trail isn't what I was thinking of. I'll try to explain using an
example of a situation.

you're a salesman and you're paid commission on what you've sold, but the
commission is paid only when all of the money has been paid by the customer.
let's say that the commission rate in april is 10% and then the rate changes
on 5/5/04 to 9.5%. so when those sales from april are finally paid in may,
you do not want to be paid at the new rate. however, unlike storing a
calculated price on an invoice table, i don't want to store the commission
calculation. i want the 'effective' date of the rate change to be stored
(an audit trail would time stamp the change to the field, which might have
happened in january). with that date stored, the calculations could look up
the date of the sale and select the commission rate that was valid at that
time.

so, if that is as clear as mud now, do you know of a link to a sample that
I could look at?

thanks in advance to anyone who can help

rpw

----- Jessestonecedar wrote: -----

one way is the check the 'isdirty' form property and write an audit
table
record.

Rich

hi Lynn,

thanks for the response. more below...

----- Lynn Trapp wrote: -----

I suspect the easiest way to handle this is to simply store the commission
rate in your commissions table. If the commission changes it won't affect
the previously created records and, thus, the salesmen will get paid on the
correct commission rate for all sales.

if I understand correctly, this would be true if I stored the (duplicate) commission rate in the sales table when the sale was input.

mmm, not exactly what i was looking for... let me try again. (btw, sorry, I guess it should have been a separate post rather than an addendum to MadCrazyNewbie's post)

I want the db user to have access to viewing the current commission rate and it's 'effective' date. and i want the user to be able to input a new rate with a new effective date with those new rates showing as 'current'. but i also want to store all of the past rates and effective dates. I'm undecided if i want a listbox to show the history of changes or not

why would i want to do things this way? let's change scenarios back to the mileage expense

in the case of expense reports / mileage - sometimes the report /expense is not turned in until after the change has occurred. so if the expense/sale/quote happened in april (but was not entered into the db until june) it should get the april rate and if it happened on or after the effective date in may, then it gets the newer rate

the more traditional 'store the rate at the time of the transaction' approach doesn't accomodate the above scenario

i was asking for a link to a sample because when i think about how to go about this my brain goes blank - usually it helps alot to look at a sample

i aprreciate your involvement :-)

(jeez - am i making this more clear or more confusing???)

rpw

Well, I think I understand a bit better now. I think what you need to do is
have a rate table that has the different commission rates and the rate
effective dates. Then use that as a lookup table to get the appropriate
commission rate to be stored in your sales table.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"rpw" wrote in message
...
hi Lynn,

thanks for the response. more below...

----- Lynn Trapp wrote: -----

I suspect the easiest way to handle this is to simply store the
commission
rate in your commissions table. If the commission changes it won't
affect
the previously created records and, thus, the salesmen will get paid
on the
correct commission rate for all sales.

if I understand correctly, this would be true if I stored the (duplicate)
commission rate in the sales table when the sale was input.

mmm, not exactly what i was looking for... let me try again. (btw,
sorry, I guess it should have been a separate post rather than an addendum
to MadCrazyNewbie's post)

I want the db user to have access to viewing the current commission rate
and it's 'effective' date. and i want the user to be able to input a new
rate with a new effective date with those new rates showing as 'current'.
but i also want to store all of the past rates and effective dates. I'm
undecided if i want a listbox to show the history of changes or not

why would i want to do things this way? let's change scenarios back to
the mileage expense

in the case of expense reports / mileage - sometimes the report /expense
is not turned in until after the change has occurred. so if the
expense/sale/quote happened in april (but was not entered into the db until
june) it should get the april rate and if it happened on or after the
effective date in may, then it gets the newer rate

the more traditional 'store the rate at the time of the transaction'
approach doesn't accomodate the above scenario

i was asking for a link to a sample because when i think about how to go
about this my brain goes blank - usually it helps alot to look at a sample

i aprreciate your involvement :-)

(jeez - am i making this more clear or more confusing???)

rpw