Copying data from one table to another

[sexton75 via AccessMonster.com]

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?

 

[Jeff Boyce]

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]

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.

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?

 

[Jeff Boyce]

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

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.

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?