 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2007Appending info to another table
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...  SignatureMaurice Ausum > Hello, > [quoted text clipped - 10 lines] > > I hope this makes sense! 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. > 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 [quoted text clipped - 14 lines] > > > > I hope this makes sense! >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. > >Hello, > > [quoted text clipped - 23 lines] > > John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.