Appending info to another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a form for entering query information. Two of the fields are the
customer name and first line of address. When I transfer all the fields from
one table to another, I also transfer these two particular fields to a third
customer table.

The two fields are based on combo boxes so if I choose from the list it will
get re-appended to the table creating an extra row that I don't need. If I
type in a new customer and address that is ok.

Is there a way to make sure that existing information doesn't get re-appended.

I hope this makes sense!
 
Make keyfields in the third customers table (same as the source). When
appending Access will give you a violation error and add only the new unique
values and discard the ones that are already in there...
 
Hello,

I have a form for entering query information. Two of the fields are the
customer name and first line of address. When I transfer all the fields from
one table to another, I also transfer these two particular fields to a third
customer table.

Umm...

If you're storing customer information in two tables (much less three!)
you'ver violating the basic principles of relational database design! The
customer name and address should exist in the Customer table, and ONLY in the
Customer table. Where else are you storing them, and why? Any other tables
should have only the CustomerID!
The two fields are based on combo boxes so if I choose from the list it will
get re-appended to the table creating an extra row that I don't need. If I
type in a new customer and address that is ok.

Is there a way to make sure that existing information doesn't get re-appended.

Don't append it in the first place. Instead, use a Combo Box to *select*
existing customer records; if it's necessary to add a new customer, use the
combo's NotInList event to pop up the customer data entry form to add the data
to the customer table. Just store the CustomerID in the main form's table.

John W. Vinson [MVP]
 
Hello,

you're first paragraph makes perfect sense. It is the way that one of the
managers wants the database to be designed but I wasn't too clear. Enquiry
information gets entered and then it gets appended to another table AND
deleted from the original table at the same time.

Although each customer can have many branches the nature of the information
tells me it wouldn't be worthwhile since each customer/branch combination is
likely to only come up once.
 
I thought of that only this morning. I wondered whether I could put an
expression into the append query that would concatenate the two fields and
send it to the primary key as an ID. I have tried it and it works. Duplicates
aren't appended.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top