Appending tables

[analea]

I have very little experience with Access - I've been working with Excel and
believe that if I move the data I deal with daily into Access, that I will be
able to save myself a lot of time.
I have a customer list, it includes the customer number, company name, when
the account was opened, when the first deposit was made and the account
manager name. We add new accounts daily, older accounts have a first deposit
made and the name of the account manager may change.
I set up the original table by importing the data from excel. I can easily
import the new customers, but I need to update the date of the first deposit
or change account manager names (not a one for one swap, but nearly a
complete reassignment of names) and I cannot figure out how to append the
table.
I've then taken the data to be updated and imported it from Excel into a new
table - both tables have the exact same fields, the primary key in both
tables is the customer number.
I've tried to do several append queries - one where I wanted the entire
table appended and others picking specific fields to be appended, but get an
error message giving several suggestions as to why the append cannot be done:
type conversion failure, key violations, lock violations and validation rule
violations.
I hope I've provided enough information. Any help will be greatly appreciated.

 

[PC Datasheet]

Analea,

I am in business to provide customers with a resource for help with
Microsoft Access, Excel and Word applications. I can help you get your
database up and running in a short time. If you would like my help, contact
me at my email address below.

 

[John Vinson]

I have very little experience with Access - I've been working with Excel and
believe that if I move the data I deal with daily into Access, that I will be
able to save myself a lot of time.

You probably will... but you'll have to invest a fair bit of learning
time up front! Access is NOT a "bigger faster spreadsheet"; relational
databases are NOT spreadsheets, appearances to the contrary
notwithstanding, and there's a good bit of mental gearshifting needed
to go from one to the other. A well designed spreadsheet would be a
VERY BAD relational database, most likely!

I have a customer list, it includes the customer number, company name, when
the account was opened, when the first deposit was made and the account
manager name. We add new accounts daily, older accounts have a first deposit
made and the name of the account manager may change.

Excellent example of my design concern. The date the first deposit was
made should NOT exist in this table! You should instead have a
Customers table, related one-to-many to a Deposits table; when you
want to know the date the first deposit was made, you'ld run a Totals
query searching for the Min() of the deposit date in the Deposits
table. The Date of first deposit is what's called "Derived Data" - as
such, it shouldn't be stored ANYWHERE, but should be calculated on the
fly as needed.

I set up the original table by importing the data from excel. I can easily
import the new customers, but I need to update the date of the first deposit
or change account manager names (not a one for one swap, but nearly a
complete reassignment of names) and I cannot figure out how to append the
table.

You'll have to decide where you want to maintain the data. Since the
logical structure of the data is different in the two programs, it'll
eventually be be more than a bit difficult to do it in both programs.
If you are firm on using Excel as the primary data storage medium,
you'll need to either zap your tables and reimport, or run some
possibly rather complex Update queries.

But to directly answer - if all you want to do is to replace all
instances of Account Manager "Rachel Smith" with "Phil Robinson", an
Update query updating the field would be the ticket. This does NOT
involve "appending a table" since you're changing the values in
existing records, not appending new records. If you do want to append
a table, there is an Append query which *will* add new records to a
table. That's useful too, but it's a different operation.

I've then taken the data to be updated and imported it from Excel into a new
table - both tables have the exact same fields, the primary key in both
tables is the customer number.
I've tried to do several append queries - one where I wanted the entire
table appended and others picking specific fields to be appended, but get an
error message giving several suggestions as to why the append cannot be done:
type conversion failure, key violations, lock violations and validation rule
violations.

Exactly. An Append query WILL ADD NEW RECORDS to the table, leaving
the old records there. That's what it's designed to do. And if you try
to add two records to the table with the same CustomerNumber, you will
indeed get a Key Violation error because (quite properly!) Access is
saying "You can't have two distinct records with the same
CustomerNumber because CustomerNumber is the unique Primary Key".

Instead, create a new Query joining the Customer table to the imported
table, joining by CustomerID. Change the query to an Update query, and
update [real-table-name].[Account Manager] to
[imported-table-name].[Account Manager] - be sure to include the
square brackets or it will think you're just typing a text string.

Hope this makes sense - post back if you can't get it working!

John W. Vinson[MVP]