Table History for Newbie

  • Thread starter Thread starter MadCrazyNewbie
  • Start date Start date
M

MadCrazyNewbie

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.aspx?scid=kb;en-us;183792

Access 2000: How to Create an Audit Trail of Record Changes in a Form
http://support.microsoft.com/default.aspx?scid=kb;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,

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

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?
 
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 advanc

rp

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

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

Access 97: How to Create an Audit Trail of Record Changes in a For
http://support.microsoft.com/default.aspx?scid=kb;en-us;18379

Access 2000: How to Create an Audit Trail of Record Changes in a For
http://support.microsoft.com/default.aspx?scid=kb;en-us;19759

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 wan
to maintain it in the same table or in a separate table, etc. For anothe
look at this subject, here is a link to an excellent article by MVP Alle
Browne

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



-

Cheryl Fischer, MVP Microsoft Acces
Law/Sys Associates, Houston, T
 
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 said:
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
Click to expand...
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.
 
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 said:
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)
Click to expand...
commission rate in the sales table when the sale was input.
mmm, not exactly what i was looking for... let me try again. (btw,
Click to expand...
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
Click to expand...
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
Click to expand...
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'
Click to expand...
approach doesn't accomodate the above scenario
i was asking for a link to a sample because when i think about how to go
Click to expand...
about this my brain goes blank - usually it helps alot to look at a sample
 
ahhh, thank you! do you happen to know if there is a link to a sample db or a book w/CD that has this kind of scenario already set-up? i'd like to see how someone else would setup the query and the forms

going to think out loud here about possible setup - (if you have any suggestions, please feel free!

on the drive in to work, i was trying to imagine how i might have things set up. i'm going to stick with this mileage scenario:

tblMileageType
MileageTypeId (PK
TypeDesc (car, boat, plane, semi-truck, etc.

tblMileageRate
RateID (PK
MileageTypeID (FK
Rat
EffDat

the form would have text boxes for TypeDesc, EffDate, and Rate, one list box that would show a list of type descriptions and another that would list the 'history' of the type (selected in the first list) i could have cmd buttons for adding a new type, or adding a new rate change, or modifying a rate

the query for the expenses could then compare date of activity to the effective date and select the correct rate, right? still, if you know of a sample, i'd appreciate it much
ti
rp
----- Lynn Trapp wrote: ----

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

--
Lynn Trap
MS Access MV
www.ltcomputerdesigns.co
Access Security: www.ltcomputerdesigns.com/Security.ht


rpw said:
hi Lynn commissio
rate in your commissions table. If the commission changes it won' affec
the previously created records and, thus, the salesmen will get pai on th
correct commission rate for all sales
Click to expand...
sorry, I guess it should have been a separate post rather than an addendu
to MadCrazyNewbie's postand it's 'effective' date. and i want the user to be able to input a ne
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'
undecided if i want a listbox to show the history of changes or nois not turned in until after the change has occurred. so if th
expense/sale/quote happened in april (but was not entered into the db unti
june) it should get the april rate and if it happened on or after th
effective date in may, then it gets the newer rat
 
I don't know of any samples that cover that specific kind of problem, but
you could easily use the 2 tables you listed below as a way to lookup the
RateID and store that in the main Mileage table.

The query for a combobox on your form might look something like this:

Select A.RateID, B.TypeDesc, A.Rate, A.EffDate
From tblMileageRates AS A Join ON a.MileageTypeID = B.MileageTypeID;

WARNING! That is untested "air" code.


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


rpw said:
ahhh, thank you! do you happen to know if there is a link to a sample db
Click to expand...
or a book w/CD that has this kind of scenario already set-up? i'd like to
see how someone else would setup the query and the forms.
going to think out loud here about possible setup - (if you have any
Click to expand...
suggestions, please feel free!)
tblMileageTypes
MileageTypeId (PK)
TypeDesc (car, boat, plane, semi-truck, etc.)

tblMileageRates
RateID (PK)
MileageTypeID (FK)
Rate
EffDate

the form would have text boxes for TypeDesc, EffDate, and Rate, one list
Click to expand...
box that would show a list of type descriptions and another that would list
the 'history' of the type (selected in the first list) i could have cmd
buttons for adding a new type, or adding a new rate change, or modifying a
rate
the query for the expenses could then compare date of activity to the
Click to expand...
effective date and select the correct rate, right? still, if you know of a
sample, i'd appreciate it much!
Click to expand...
 
hey Lynn, thanks for keeping in touch! thank you for the suggestion, too!

(plz don't take this like i'm looking a gift horse in the mouth)

i'm not sure i understand why to use a combobox - did you suggest that because it would minimize form real estate? (as opposed to two listboxes) if i understand the query correctly, the combo would list ALL rates. if there were 5 types, and 10 changes each, the combo would have 50 choices for the user to select from if i may ask, how are you thinking this would be used?

rp

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

I don't know of any samples that cover that specific kind of problem, bu
you could easily use the 2 tables you listed below as a way to lookup th
RateID and store that in the main Mileage table

The query for a combobox on your form might look something like this

Select A.RateID, B.TypeDesc, A.Rate, A.EffDat
From tblMileageRates AS A Join ON a.MileageTypeID = B.MileageTypeID

WARNING! That is untested "air" code


--
Lynn Trap
MS Access MV
www.ltcomputerdesigns.co
Access Security: www.ltcomputerdesigns.com/Security.ht


rpw said:
ahhh, thank you! do you happen to know if there is a link to a sample d
Click to expand...
or a book w/CD that has this kind of scenario already set-up? i'd like t
see how someone else would setup the query and the forms
MileageTypeId (PK
TypeDesc (car, boat, plane, semi-truck, etc.
RateID (PK
MileageTypeID (FK
Rat
EffDat
Click to expand...
box that would show a list of type descriptions and another that would lis
the 'history' of the type (selected in the first list) i could have cm
buttons for adding a new type, or adding a new rate change, or modifying
rateffective date and select the correct rate, right? still, if you know of
sample, i'd appreciate it much
 
Well,
Mainly because I have a big preference for a combobox over a listbox. I try
to do everything with a combobox rather than a listbox. What I have in mind
is that you put a field in your main Sales table that will store the RateID
for each sales record. You would have to add a RateID field to the table and
then, on your data entry form, use the combobox to select the appropriate
RateID. Once you have that then you can always calculate the sales
commission based on the total sales times the rate identified by the RateID.

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


rpw said:
hey Lynn, thanks for keeping in touch! thank you for the suggestion, too!

(plz don't take this like i'm looking a gift horse in the mouth)

i'm not sure i understand why to use a combobox - did you suggest that
Click to expand...
because it would minimize form real estate? (as opposed to two listboxes)
if i understand the query correctly, the combo would list ALL rates. if
there were 5 types, and 10 changes each, the combo would have 50 choices for
the user to select from if i may ask, how are you thinking this would be
used?
rpw

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

I don't know of any samples that cover that specific kind of problem, but
you could easily use the 2 tables you listed below as a way to lookup the
RateID and store that in the main Mileage table.

The query for a combobox on your form might look something like this:

Select A.RateID, B.TypeDesc, A.Rate, A.EffDate
From tblMileageRates AS A Join ON a.MileageTypeID = B.MileageTypeID;

WARNING! That is untested "air" code.


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


rpw said:
ahhh, thank you! do you happen to know if there is a link to a
Click to expand...
sample db
or a book w/CD that has this kind of scenario already set-up? i'd like to
see how someone else would setup the query and the forms. any
suggestions, please feel free!)
Click to expand...
 
thank you, i understand no

i didn't get it because i was thinking of something different - a rates form where the user could input rate changes listbox1 queried to hold only unique TypeDesc and listbox2 that would hold the list of changes for the selected item in listbox1 with final selection bookmarking the form's RateID record. i've seen alot of requests for comboboxes to behave this way, but i guess i must have a preference for listboxes also, i'm imagining this Rates form to have plenty of available real estate for placing the listboxe

now that you've brought up the main activity table (sales, or mileage expenses), my idea for the activity input form would have the user needing only to use a combo to select a new TypeDesc (different than the default) and input a date for the activity. i'm thinking that with those two items, a query could lookup the appropriate rate if it was needed for calculations on a report

probably more difficult to implement, but less dependant upon the user to make the right selectio

thanks for all of your input, i think that you've help me to start clearing th fog on thi

rp

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

Well
Mainly because I have a big preference for a combobox over a listbox. I tr
to do everything with a combobox rather than a listbox. What I have in min
is that you put a field in your main Sales table that will store the RateI
for each sales record. You would have to add a RateID field to the table an
then, on your data entry form, use the combobox to select the appropriat
RateID. Once you have that then you can always calculate the sale
commission based on the total sales times the rate identified by the RateID

--
Lynn Trap
MS Access MV
www.ltcomputerdesigns.co
Access Security: www.ltcomputerdesigns.com/Security.ht


rpw said:
hey Lynn, thanks for keeping in touch! thank you for the suggestion, too
Click to expand...
because it would minimize form real estate? (as opposed to two listboxes
if i understand the query correctly, the combo would list ALL rates. i
there were 5 types, and 10 changes each, the combo would have 50 choices fo
the user to select from if i may ask, how are you thinking this would b
used
 
hi again

i've put together a small demo db to test out what i've been learning from this thread and i've got tables and forms working the way i'd like but i'm having a bit of trouble with the query for the report. the rate's effective date changes and is recorded in a table (sample simplified

RateID
Rate .2
EffDate 4/1/0

RateID
Rate .3
EffDate 5/1/0

an expense is entered into a form with a MileageDat

MileageExpenseID
MileageDate 4/2/0

MileageExpenseID
MileageDate 5/6/0

the actual SQL for the report i

SELECT tblMileageExpense.MileageExpenseID, tblMileageExpense.MileageDate, tblMileageExpense.StartMiles, tblMileageExpense.EndMiles, tblMileageExpense.MileageTypeID, tblMileageType.TypeDesc, tblMileageRate.Rate, tblMileageRate.EffDate, tblMileageRate.EffDate FROM (tblMileageType INNER JOIN tblMileageExpense ON tblMileageType.MileageTypeID=tblMileageExpense.MileageTypeID) INNER JOIN tblMileageRate ON tblMileageType.MileageTypeID=tblMileageRate.MileageTypeID WHERE (((tblMileageRate.EffDate)<=tblMileageExpense!MileageDate));

I get three lines printed on the report and I want to see only two - (simplified from actual report

MileageDate 4/2/04 Rate .25 EffDate 4/1/04 (MileageExpenseID1
MileageDate 5/6/04 Rate .25 EffDate 4/1/04 (MileageExpenseID2) this listing is the one i don't need??
MileageDate 5/6/04 Rate .30 EffDate 5/1/04 (MileageExpenseID2

I can't figure out how to have the query select the MileageExpenseID only one tim

any suggestions?

tia

rp

PS - sorry, i gotta go now so i won't be able to check for responses till Monday - no internet at home if you wanna help and you've got questions, pls bear w/me until then --- i really appreciate the help everyone here gives
 
Back
Top