Utilizing data from one table and tranfering it to another

[Guest]

Ok, here's my issue: I want to create a customer service call screen that
logs complaints. I am using an autonumber as my unique number and that is my
primary key number. I want a portion of this information to transfer to
another table so that I do not have to type it in again.

I two main tables - Customer Service and Corrective action
I have several smaller tables that support the Customer service table:
Parts table
Customer info table
Employee Table
Etc...
I have a one to many relationship from my smaller support tables to my main
Customer Service Table - This setup seems to work fine. Where I run into
trouble is..
Customer service table contains several fields that I would like to transfer
over to the Corretive Action Table.
I linked my Corrective action table and my Customer Service table using my
two primary (autonumber) keyed fields. I created a form based upon both
tables and the information on my Customer Service Table did not trasnfer over
to my Corretive action table.

If there is a way I could upload my database to anyone to look at I and
perhaps rip to shreds I would appreciate it.

Thanks

 

[John Vinson]

Customer service table contains several fields that I would like to transfer
over to the Corretive Action Table.

Why?

Storing data redundantly is almost NEVER a good idea.

If you have (say) the CustomerName in the Customer table, and the
CustomerName in the CorrectiveAction table, there would be nothing to
stop you or anyone else from editing *one* of the tables. You would
then have the same CustomerID with two different names, and no good
way to tell which was correct.

Could you explain *why* you need to copy data from one table to
another? It might be a valid reason, but I'd hate to suggest a way for
you to mess up your database!

I linked my Corrective action table and my Customer Service table using my
two primary (autonumber) keyed fields.

That WILL NOT WORK.

An Autonumber is a COMPLETELY ARBITRARY value. It has one purpose, and
one purpose only: to provide a guaranteed unique key.

You have no control over the value of the autonumber; in particular, a
new record in the Corrective Action table will get an arbitrary
autonumber value, which might or might not match an existing value in
Customer Service; it's most unlikely to match the record that you
want, though!

If there is a relationship between Customer Service records and
Corrective Action records, it should be from the Customer Service
Primary Key (which might be an autonumber, or some other type of
field) to a Foreign Key field (NOT the primary key, usually, and
*certainly* not an Autonumber) in Corrective Action.


John W. Vinson[MVP]

 

[Guest]

Thank you for your advice, that does make sense. the only information in the
fields I wanted to transfer over were fields that related the corrective
action response to the customer complaint, so that when various people adding
info they would know which complaint they were responding to. Is there a way
to automatically generate a unique number that is not an autonumber?

Can I assume that a Foreign Key field is simply a field that has not been
desginated a primary field? Thanks for your help.

 

[John Vinson]

Thank you for your advice, that does make sense. the only information in the
fields I wanted to transfer over were fields that related the corrective
action response to the customer complaint, so that when various people adding
info they would know which complaint they were responding to.

The complaint information should be stored in one table.

The corrective action information should be stored in a DIFFERENT
table. They're different Entities.

Each information should be stored once, and once only. It is NOT
necessary or beneficial to copy the information around! Instead, you
could use a Form displaying the complaints information, with a Subform
on which to enter the corrective action data (and there might be more
than one corrective action... might there not? a One to Many
relationship)?

The Subform will automatically link the ComplaintID in the mainform to
the ComplaintID in the Corrective Action table. This should be a field
of the same datatype as the parent table's Primary Key (Long Integer
if the ComplaintID is an autonumber) in the CorrectiveAction table;
that's how you can link a corrective action to its complaint.

Is there a way
to automatically generate a unique number that is not an autonumber?

Yes, but I don't see how this is relevant to the issue you're trying
to solve.

Can I assume that a Foreign Key field is simply a field that has not been
desginated a primary field? Thanks for your help.

You might want to look at the tutorial information in Jeff Conrad's
website:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Tutorial

Access is a complex product; you'll do well to study up on it before
diving in. And the first step is to come up with a properly normalized
set of related tables - which I fear you have not done!

John W. Vinson[MVP]

 

[Guest]

Thank you again for advice I have found out the hard way that Access is both
powerful and complex. I will redeisgn and try your idea. Part of the
problem I'm not thinking in database structure terms.

Thanks again.

 

[John Vinson]

Thank you again for advice I have found out the hard way that Access is both
powerful and complex. I will redeisgn and try your idea. Part of the
problem I'm not thinking in database structure terms.

Don't hesitate to post some more information about your application.
Think in terms of "Entities" - real-life persons, things, or events
which will be stored in Tables; Attributes of those entities, which
will be in fields in the Tables; and Relationships, how one entity
relates to other entities.

John W. Vinson[MVP]