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
|
|||
|
|||
Saving Original Record when Revising
I have created an invoice table (t_PCO) with numerous related tables
(t_PCOEquipment), (t_PCOLaborHours), (t_PCOMaterial). Often these invoices are submitted to customers only to be returned with revisions. I am trying to make it possible that one can choose to revise these invoices rather than overwrite them and in addition I would like to be able to track the changes made. Currently, I am using the invoice number as the primary key for the main table and I believe that I could add a RevisionNum field which would be used with the invoice number as the primary field, thus allowing me to group these later. I am wondering if there is a way which I can: (1) make a copy of all of the records from the various tables that is related to a particular invoice and then (2) have all of these copies of the original records have their RevisionNum field adjusted by 1. I believe that this approach would work, but suggestions are very welcome. Please be forewarned; I am an extreme novice. Thank you in advance. Brian |
#2
|
|||
|
|||
Saving Original Record when Revising
Access does not provide an easy way to do this, Brian.
It is possible to use the Form events to create a copy of the record in a logging table. Here's how: Audit Trail - Log changes at the record level at: http://allenbrowne.com/AppAudit.html But it's not as simple as a revision number field. Say you have an invoice made out to Fred Nerks, with 2 rows (line items) in the related table. Possibilities include: a) User adds another line item. b) User deletes a line item. c) User edits a line item (including reassigning it to a different invoice?) d) User edits the header record (including reassigning it to a different client, which affects all line items.) e) User deletes header record (which deletes the associated line item(s) as well.) I'm not sure which of these constitute a revision number in your thinking. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Brian Carlson" wrote in message ... I have created an invoice table (t_PCO) with numerous related tables (t_PCOEquipment), (t_PCOLaborHours), (t_PCOMaterial). Often these invoices are submitted to customers only to be returned with revisions. I am trying to make it possible that one can choose to revise these invoices rather than overwrite them and in addition I would like to be able to track the changes made. Currently, I am using the invoice number as the primary key for the main table and I believe that I could add a RevisionNum field which would be used with the invoice number as the primary field, thus allowing me to group these later. I am wondering if there is a way which I can: (1) make a copy of all of the records from the various tables that is related to a particular invoice and then (2) have all of these copies of the original records have their RevisionNum field adjusted by 1. I believe that this approach would work, but suggestions are very welcome. Please be forewarned; I am an extreme novice. Thank you in advance. Brian |
#3
|
|||
|
|||
Saving Original Record when Revising
Allen:
In regards to this log, none of the records associated with one invoice would ever be reassigned to a different invoice. I may delete, edit or add records to the tables related to the main invoice table. After reading your response and the attached link, I began to parse down the actual information I am interested in tracking across invoices. I really do not need to track the individual items contained in each related table, but rather only the totals from each of the subforms. That is, I am not interested in what has changed but rather only the numerical difference. Would it work to put fields on the main form that store the totals received from each subform and then any revisions to an invoice would simply have to be treated as completely new records with the same invoice number and different revision number. It seems that I would then be able to compare each invoice and its revisions? This one may be a bit over my head at this point in time. Thanks for your help. Brian "Allen Browne" wrote: Access does not provide an easy way to do this, Brian. It is possible to use the Form events to create a copy of the record in a logging table. Here's how: Audit Trail - Log changes at the record level at: http://allenbrowne.com/AppAudit.html But it's not as simple as a revision number field. Say you have an invoice made out to Fred Nerks, with 2 rows (line items) in the related table. Possibilities include: a) User adds another line item. b) User deletes a line item. c) User edits a line item (including reassigning it to a different invoice?) d) User edits the header record (including reassigning it to a different client, which affects all line items.) e) User deletes header record (which deletes the associated line item(s) as well.) I'm not sure which of these constitute a revision number in your thinking. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Brian Carlson" wrote in message ... I have created an invoice table (t_PCO) with numerous related tables (t_PCOEquipment), (t_PCOLaborHours), (t_PCOMaterial). Often these invoices are submitted to customers only to be returned with revisions. I am trying to make it possible that one can choose to revise these invoices rather than overwrite them and in addition I would like to be able to track the changes made. Currently, I am using the invoice number as the primary key for the main table and I believe that I could add a RevisionNum field which would be used with the invoice number as the primary field, thus allowing me to group these later. I am wondering if there is a way which I can: (1) make a copy of all of the records from the various tables that is related to a particular invoice and then (2) have all of these copies of the original records have their RevisionNum field adjusted by 1. I believe that this approach would work, but suggestions are very welcome. Please be forewarned; I am an extreme novice. Thank you in advance. Brian |
#4
|
|||
|
|||
Saving Original Record when Revising
I think it would be better to audit both (main table and related table)
rather than store a total. If you just have a total, you have no way of knowing what that number means, or how it was made up, or even if it is correct. If you log the related records as well, you have a verifiable and meaningful log. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Brian Carlson" wrote in message ... Allen: In regards to this log, none of the records associated with one invoice would ever be reassigned to a different invoice. I may delete, edit or add records to the tables related to the main invoice table. After reading your response and the attached link, I began to parse down the actual information I am interested in tracking across invoices. I really do not need to track the individual items contained in each related table, but rather only the totals from each of the subforms. That is, I am not interested in what has changed but rather only the numerical difference. Would it work to put fields on the main form that store the totals received from each subform and then any revisions to an invoice would simply have to be treated as completely new records with the same invoice number and different revision number. It seems that I would then be able to compare each invoice and its revisions? This one may be a bit over my head at this point in time. Thanks for your help. Brian "Allen Browne" wrote: Access does not provide an easy way to do this, Brian. It is possible to use the Form events to create a copy of the record in a logging table. Here's how: Audit Trail - Log changes at the record level at: http://allenbrowne.com/AppAudit.html But it's not as simple as a revision number field. Say you have an invoice made out to Fred Nerks, with 2 rows (line items) in the related table. Possibilities include: a) User adds another line item. b) User deletes a line item. c) User edits a line item (including reassigning it to a different invoice?) d) User edits the header record (including reassigning it to a different client, which affects all line items.) e) User deletes header record (which deletes the associated line item(s) as well.) I'm not sure which of these constitute a revision number in your thinking. "Brian Carlson" wrote in message ... I have created an invoice table (t_PCO) with numerous related tables (t_PCOEquipment), (t_PCOLaborHours), (t_PCOMaterial). Often these invoices are submitted to customers only to be returned with revisions. I am trying to make it possible that one can choose to revise these invoices rather than overwrite them and in addition I would like to be able to track the changes made. Currently, I am using the invoice number as the primary key for the main table and I believe that I could add a RevisionNum field which would be used with the invoice number as the primary field, thus allowing me to group these later. I am wondering if there is a way which I can: (1) make a copy of all of the records from the various tables that is related to a particular invoice and then (2) have all of these copies of the original records have their RevisionNum field adjusted by 1. I believe that this approach would work, but suggestions are very welcome. Please be forewarned; I am an extreme novice. Thank you in advance. |
#5
|
|||
|
|||
Saving Original Record when Revising
Allen:
Thank you for your assistance. I will read your article more throughly and try to mess around with this on a copy of my database to see if I can master it. Thanks for the help. Brian "Allen Browne" wrote: I think it would be better to audit both (main table and related table) rather than store a total. If you just have a total, you have no way of knowing what that number means, or how it was made up, or even if it is correct. If you log the related records as well, you have a verifiable and meaningful log. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Brian Carlson" wrote in message ... Allen: In regards to this log, none of the records associated with one invoice would ever be reassigned to a different invoice. I may delete, edit or add records to the tables related to the main invoice table. After reading your response and the attached link, I began to parse down the actual information I am interested in tracking across invoices. I really do not need to track the individual items contained in each related table, but rather only the totals from each of the subforms. That is, I am not interested in what has changed but rather only the numerical difference. Would it work to put fields on the main form that store the totals received from each subform and then any revisions to an invoice would simply have to be treated as completely new records with the same invoice number and different revision number. It seems that I would then be able to compare each invoice and its revisions? This one may be a bit over my head at this point in time. Thanks for your help. Brian "Allen Browne" wrote: Access does not provide an easy way to do this, Brian. It is possible to use the Form events to create a copy of the record in a logging table. Here's how: Audit Trail - Log changes at the record level at: http://allenbrowne.com/AppAudit.html But it's not as simple as a revision number field. Say you have an invoice made out to Fred Nerks, with 2 rows (line items) in the related table. Possibilities include: a) User adds another line item. b) User deletes a line item. c) User edits a line item (including reassigning it to a different invoice?) d) User edits the header record (including reassigning it to a different client, which affects all line items.) e) User deletes header record (which deletes the associated line item(s) as well.) I'm not sure which of these constitute a revision number in your thinking. "Brian Carlson" wrote in message ... I have created an invoice table (t_PCO) with numerous related tables (t_PCOEquipment), (t_PCOLaborHours), (t_PCOMaterial). Often these invoices are submitted to customers only to be returned with revisions. I am trying to make it possible that one can choose to revise these invoices rather than overwrite them and in addition I would like to be able to track the changes made. Currently, I am using the invoice number as the primary key for the main table and I believe that I could add a RevisionNum field which would be used with the invoice number as the primary field, thus allowing me to group these later. I am wondering if there is a way which I can: (1) make a copy of all of the records from the various tables that is related to a particular invoice and then (2) have all of these copies of the original records have their RevisionNum field adjusted by 1. I believe that this approach would work, but suggestions are very welcome. Please be forewarned; I am an extreme novice. Thank you in advance. |
#6
|
|||
|
|||
Saving Original Record when Revising
I know only 1% of what Allen does on Access.
But I do run companies, and where I live what you are trying to do is very unusual (some companies may even consider it improper) and may be making your life overly complicated. Normally you would consider an invoice to be a one-time event, "frozen" thereafter. If you send it again, it is a COPY. And make changes via a second debit/credit "invoice". The net result is reflected in STATEMENTS of the account which combine the above. |
#7
|
|||
|
|||
Saving Original Record when Revising
Fred:
I should probably clarify. These are not invoices proper, but rather billing proposals. They are invoices, given that the work was already performed, but the owner has the option of how they are billed for the charge and all billings are negotiable. This is because we have a contract with these owners to do work (these invoices are always paid as billed), but sometimes extra work comes up that must be done to continue our contract work. The owner does not want our contract work to be held up by these items, so we proceed with them and us documenting what was done. We then send them these invoices, which they can approve as is, or they ask us to revise if their is a discrepancy with their records. I do not know if any of this information will assist in addressing my question, but in either case, thank you for the input. Brian "Fred" wrote: I know only 1% of what Allen does on Access. But I do run companies, and where I live what you are trying to do is very unusual (some companies may even consider it improper) and may be making your life overly complicated. Normally you would consider an invoice to be a one-time event, "frozen" thereafter. If you send it again, it is a COPY. And make changes via a second debit/credit "invoice". The net result is reflected in STATEMENTS of the account which combine the above. |
#8
|
|||
|
|||
Saving Original Record when Revising
Hello Brian,
Your post establishes that your current practice is fine with your customers. So, the rules/ practices that I described are not needed in your business scenerio. But, in my opinion, they still might be the easiest / best way to accomplish what you are trying to do, and also provide a solid foundation for dealing different future issues. |
#9
|
|||
|
|||
Saving Original Record when Revising
Fred:
I wish it was easy to implement tne changes you suggested, in either case, thank you for your assistance. Brian "Fred" wrote: Hello Brian, Your post establishes that your current practice is fine with your customers. So, the rules/ practices that I described are not needed in your business scenerio. But, in my opinion, they still might be the easiest / best way to accomplish what you are trying to do, and also provide a solid foundation for dealing different future issues. |
Thread Tools | |
Display Modes | |
|
|