 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / May 2005Tables Relationsship
I have two table; TableA and Table B. Both have a key field named Item. When I add a record in TableA, I would like it to add to TableB automatically. How do I do it? Not possible directly from table. You need to use a form to add data and then the form can be programmed to add data to another table. Of course, I must ask... why do you wish to store apparently redundant data? Or are there more details about this setup that are not obvioius to us?  SignatureKen Snell <MS ACCESS MVP> >I have two table; TableA and Table B. Both have a key field named Item. > When I add a record in TableA, I would like it to add to TableB > automatically. How do I do it? Here is the details. The master table TableA is a huge table that contains product information; the column name for product name is Item and it is a key field. TableB contains serial numbers for each product and are incremented as a product label is printed from within the application. When a product label is printed, I want to write serial number for that product to TableB which is not availabel to the users. The user will add new records into TableA and at that point I want to automatically add that Item number in TableB. I hope this explains what I am trying to accomplish. > Not possible directly from table. You need to use a form to add data and > then the form can be programmed to add data to another table. [quoted text clipped - 5 lines] > > When I add a record in TableA, I would like it to add to TableB > > automatically. How do I do it? Is there a special use for TableB that precludes your use of the data in TableA to know which serial numbers have been issued? TableA will already contain that information. You haven't commented about when you are doing this? Are you using a form? SignatureKen Snell <MS ACCESS MVP> > Here is the details. > The master table TableA is a huge table that contains product information; [quoted text clipped - 17 lines] >> > When I add a record in TableA, I would like it to add to TableB >> > automatically. How do I do it? TableA does not have Serial Numbers information in it, it is in TableB for each product. The VB.net application uses TableA for data management and user interface. When print option is selected for the selected product, the application reads Serial Number from TableB and prints ou the label. The Serial Numbers are managed in TableB for each product and the default value is '00001'. The label uses TableA for product information and TableB for Serial Numbers. There are many good reasons to have tables setup this way to fit within our needs. The VB.net application uses API calls to a 3rd party's labeling product to product labels. > Is there a special use for TableB that precludes your use of the data in > TableA to know which serial numbers have been issued? TableA will already [quoted text clipped - 22 lines] > >> > When I add a record in TableA, I would like it to add to TableB > >> > automatically. How do I do it? OK. But please tell us how you're running the application. It's not possible to give a good suggestion on how to do what you seek to accomplish without knowing the circumstances and process that you use to generate the serial number, etc. In other words, what is the application doing when you need it to write a record to TableB? Generically, using an append query probably is the best way to accomplish your needs. SignatureKen Snell <MS ACCESS MVP> > TableA does not have Serial Numbers information in it, it is in TableB for > each product. The VB.net application uses TableA for data management and [quoted text clipped - 44 lines] >> >> > When I add a record in TableA, I would like it to add to TableB >> >> > automatically. How do I do it? Ken, I should have mentioned this earlier; I am adding new records in TableA using Access database directly into the table. My question has nothing to do how the application is run or processes the Serial Numbers or reads/writes the Serial Numbers. All I need is to setup relationship in Access database for two table in a way that when I add a new product in TableA, TableB woud be updated to add the product number in it. Currently, if I delete a product from TableA, that product number (Item) would automatically be deleted from TableB (uses MS Access Table Relationship option). > OK. But please tell us how you're running the application. It's not possible > to give a good suggestion on how to do what you seek to accomplish without [quoted text clipped - 53 lines] > >> >> > When I add a record in TableA, I would like it to add to TableB > >> >> > automatically. How do I do it? A relationship by itself will not put "empty" records into a related table. You will have to add a record to that TableB via an append query that is run when you finish entering data into TableA. That is why I asked about how you're entering data. SignatureKen Snell <MS ACCESS MVP> > Ken, I should have mentioned this earlier; I am adding new records in > TableA [quoted text clipped - 83 lines] >> >> >> > When I add a record in TableA, I would like it to add to TableB >> >> >> > automatically. How do I do it? How do I create the append query? > A relationship by itself will not put "empty" records into a related table. > You will have to add a record to that TableB via an append query that is run [quoted text clipped - 88 lines] > >> >> >> > When I add a record in TableA, I would like it to add to TableB > >> >> >> > automatically. How do I do it? It completely depends upon what you're doing in ACCESS at the time that you need it. If you're working in a form when you want to run the append query, you can have the form's programming create an SQL string and then execute it. Or you can run a stored query. Forgive me, but as I've said a few times already, if you don't tell us what is happening at the time that you need this to run, we cannot provide specific suggestions. SignatureKen Snell <MS ACCESS MVP> > How do I create the append query? > [quoted text clipped - 109 lines] >> >> >> >> > TableB >> >> >> >> > automatically. How do I do it? >I have two table; TableA and Table B. Both have a key field named Item. >When I add a record in TableA, I would like it to add to TableB >automatically. How do I do it? Well, I'd ask - should you ever do it? I'd be inclined to say No. First off, one to one relationships are quite rare. If you're not Subclassing or doing Table-Driven Field Level Security (or don't recognize those terms), you probably don't want two tables with the same primary key. Secondly, it is almost never appropriate - even with a valid one to one relationship - to create an empty "placeholder" record in TableB with the intentof filling it in later. What real-life Entities do these two tables represent? What are you trying to accomplish with this new record? 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
|
©2008 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.