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
|
|||
|
|||
Copying data from one table to another
I have a Sales Branch Audit database in development. I have all sales branch
information contained in a separate table (i.e. address, city, state, etc.). The Audit Form (which populates Tbl_Audit) uses a combo box allowing the user to select the branch being audited. That value is placed in the SBID field in the audit table. Once that is done, it pulls up all of the sales branch information in an embedded subform which is based on a query. When the "Complete Audit" button is pressed, I would like it to copy the address, city, state, and zip from the Tbl_Salesbranch and put it into the corresponding fields in Tbl_Audit. I need the current location to be recorded in the audit, because branch addresses change over time. I initially had the address, city, state, and zip populate on the main form when the branch was selected, but prefered the look of the subform better. Is it possible to do what I am looking for, or do I need to go back to the way I had it? -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Copying data from one table to another
I'm not sure you need to "move" data at all.
Would it be sufficient for your business needs to add a single field to the table that stores an indication of the audit? For example, [DateAudited]? That would only require a simple update query to add in that audit date to specific records. Or perhaps I don't understand your business need... Regards Jeff Boyce Microsoft Office/Access MVP "sexton75 via AccessMonster.com" u42906@uwe wrote in message news:8641c17855ffc@uwe... I have a Sales Branch Audit database in development. I have all sales branch information contained in a separate table (i.e. address, city, state, etc.). The Audit Form (which populates Tbl_Audit) uses a combo box allowing the user to select the branch being audited. That value is placed in the SBID field in the audit table. Once that is done, it pulls up all of the sales branch information in an embedded subform which is based on a query. When the "Complete Audit" button is pressed, I would like it to copy the address, city, state, and zip from the Tbl_Salesbranch and put it into the corresponding fields in Tbl_Audit. I need the current location to be recorded in the audit, because branch addresses change over time. I initially had the address, city, state, and zip populate on the main form when the branch was selected, but prefered the look of the subform better. Is it possible to do what I am looking for, or do I need to go back to the way I had it? -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Copying data from one table to another
The sales department maintains the contact database which holds office
details such as address city state etc. The compliance dept maintains the audit records. Right now, the audit table only holds the branch ID number. While the branch id will always reference to that specific branch, over time the address may change. They want a static address, city, state, etc for the specific location that was audited inside the audit table. That way if an audit was done at 101 main street in 7/2005 and the location changed in 2008 to 500 South street, the address in the audit table would still be 101 main street even though the address changed in the contact database. Hope that makes sense. Jeff Boyce wrote: I'm not sure you need to "move" data at all. Would it be sufficient for your business needs to add a single field to the table that stores an indication of the audit? For example, [DateAudited]? That would only require a simple update query to add in that audit date to specific records. Or perhaps I don't understand your business need... Regards Jeff Boyce Microsoft Office/Access MVP I have a Sales Branch Audit database in development. I have all sales branch [quoted text clipped - 19 lines] Is it possible to do what I am looking for, or do I need to go back to the way I had it? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200806/1 |
#4
|
|||
|
|||
Copying data from one table to another
It sounds like you are saying the sales department could end up with a
record that shows a "101 Main" address while your audit department has a different address. This could appear to someone unversed in the idiosynchrasies of your operations as a data integrity issue. Another possible approach would be to 'end-date' the sales department's "101 Main" record (use a date/time field to show when it was no longer valid) and add a new address record to reflect the new address. NOTE: were this mine, I'd probably keep contact name/ID in one table, along with a CurrentAddressID field, then create a new tblAddress to hold address info. Both your sales department and audit department could refer to the address-that-applies by using ONLY the AddressID, without having to duplicate fields. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "sexton75 via AccessMonster.com" u42906@uwe wrote in message news:864603673a6b0@uwe... The sales department maintains the contact database which holds office details such as address city state etc. The compliance dept maintains the audit records. Right now, the audit table only holds the branch ID number. While the branch id will always reference to that specific branch, over time the address may change. They want a static address, city, state, etc for the specific location that was audited inside the audit table. That way if an audit was done at 101 main street in 7/2005 and the location changed in 2008 to 500 South street, the address in the audit table would still be 101 main street even though the address changed in the contact database. Hope that makes sense. Jeff Boyce wrote: I'm not sure you need to "move" data at all. Would it be sufficient for your business needs to add a single field to the table that stores an indication of the audit? For example, [DateAudited]? That would only require a simple update query to add in that audit date to specific records. Or perhaps I don't understand your business need... Regards Jeff Boyce Microsoft Office/Access MVP I have a Sales Branch Audit database in development. I have all sales branch [quoted text clipped - 19 lines] Is it possible to do what I am looking for, or do I need to go back to the way I had it? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200806/1 |
Thread Tools | |
Display Modes | |
|
|