If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Table History for Newbie
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 |
#3
|
|||
|
|||
Table History for Newbie
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 |
#4
|
|||
|
|||
Table History for Newbie
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 |
#5
|
|||
|
|||
Table History for Newbie
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 |
#6
|
|||
|
|||
Table History for Newbie
one way is the check the 'isdirty' form property and write an audit table
record. Rich |
#7
|
|||
|
|||
Table History for Newbie
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 |
#8
|
|||
|
|||
Table History for Newbie
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 |
#9
|
|||
|
|||
Table History for Newbie
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 |
#10
|
|||
|
|||
Table History for Newbie
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 |
|
Thread Tools | |
Display Modes | |
|
|