If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Populate secondary table from table with foreign key
Hi,
I'll try to phrase this question correctly. I need to Have a link to a table that contains store numbers and addresses. I want this table to be able to be populated by adding data from the first table containing the foreign key. Whenever I setup a link, it operates as follows. I cannot add data to the Store# field, and it claims I have to add it to the referenced Store table. How do I set this up? Thanks, Tom -- When eveythings coming at you at once, you're in the wrong lane. |
#2
|
|||
|
|||
Populate secondary table from table with foreign key
=?Utf-8?B?VG9t?= wrote in
: I want this table to be able to be populated by adding data from the first table containing the foreign key. Whenever I setup a link, it operates as follows. I cannot add data to the Store# field, and it claims I have to add it to the referenced Store table. How do I set this up? You don't. You store information about Stores in the Stores table, and you store Address stuff in the Addresses table. If the Addresses table has a field called BelongsTo that references the StoreNumber field in the Stores table, then that is what is called a foreign key. Addresses ========= Stores *AddressID ====== BelongsTo ------ *StoreNumber TopLine CommercialName SecondLine PrimaryContact ThirdLine etc... PostCode PhoneNumber It's then easy to create a query that places the Stores.CommercialName above the Addresses.TopLine, or whatever you want to do. But you don't want to put address stuff in the Stores table, nor stores stuff in the Addresses table. Ever. Hope that helps Tim F |
#3
|
|||
|
|||
Populate secondary table from table with foreign key
Tom,
It appears that you have two tables between which there is a one-to-many relationship. For example you have a Stores table, where the StoreNumber (note that it is not a good idea to use a # as part of a field name) field is the Primary Key, and the other table has the StoreNumber as a foreign key field. There can be more than one entry in the second table relating to the same Store. Am I right? In these sorts of scenarios, it generally works best if your record is already entered and saved in the table on the "one" side of the relationship (Stores) before you try to add the related record to the table on the "many" side. Can you please post back with more details and examples if you need more specific help. Thanks. -- Steve Schapel, Microsoft Access MVP Tom wrote: Hi, I'll try to phrase this question correctly. I need to Have a link to a table that contains store numbers and addresses. I want this table to be able to be populated by adding data from the first table containing the foreign key. Whenever I setup a link, it operates as follows. I cannot add data to the Store# field, and it claims I have to add it to the referenced Store table. How do I set this up? Thanks, Tom |
#4
|
|||
|
|||
Populate secondary table from table with foreign key
Hi Steve,
I think you've got how I set it up exactly. What I want to do is to have a form, with a "StoreNumber" field that users can add a new store number to. Currently, I've done away with ther link between the "Stores" table and the "Transactions" table, so new store numbers can be added in the "StoreNumber" field. Is there any way to do that? so that when a new store number is added to that field, it populates the related tabel with just a store number? or even the Store number, address, etc. ? Let me know if I'm not being clear enough on what I want. Thanks, Tom "Steve Schapel" wrote: Tom, It appears that you have two tables between which there is a one-to-many relationship. For example you have a Stores table, where the StoreNumber (note that it is not a good idea to use a # as part of a field name) field is the Primary Key, and the other table has the StoreNumber as a foreign key field. There can be more than one entry in the second table relating to the same Store. Am I right? In these sorts of scenarios, it generally works best if your record is already entered and saved in the table on the "one" side of the relationship (Stores) before you try to add the related record to the table on the "many" side. Can you please post back with more details and examples if you need more specific help. Thanks. -- Steve Schapel, Microsoft Access MVP Tom wrote: Hi, I'll try to phrase this question correctly. I need to Have a link to a table that contains store numbers and addresses. I want this table to be able to be populated by adding data from the first table containing the foreign key. Whenever I setup a link, it operates as follows. I cannot add data to the Store# field, and it claims I have to add it to the referenced Store table. How do I set this up? Thanks, Tom |
#5
|
|||
|
|||
Populate secondary table from table with foreign key
Hi Tim,
Can I name the "BelongsTo" field "StoreNumber" instead? So the one to many relationship would be 1(primary key) to many(foreign key)? Is that correct? Thanks, Tom "Tim Ferguson" wrote: =?Utf-8?B?VG9t?= wrote in : I want this table to be able to be populated by adding data from the first table containing the foreign key. Whenever I setup a link, it operates as follows. I cannot add data to the Store# field, and it claims I have to add it to the referenced Store table. How do I set this up? You don't. You store information about Stores in the Stores table, and you store Address stuff in the Addresses table. If the Addresses table has a field called BelongsTo that references the StoreNumber field in the Stores table, then that is what is called a foreign key. Addresses ========= Stores *AddressID ====== BelongsTo ------ *StoreNumber TopLine CommercialName SecondLine PrimaryContact ThirdLine etc... PostCode PhoneNumber It's then easy to create a query that places the Stores.CommercialName above the Addresses.TopLine, or whatever you want to do. But you don't want to put address stuff in the Stores table, nor stores stuff in the Addresses table. Ever. Hope that helps Tim F |
#6
|
|||
|
|||
Populate secondary table from table with foreign key
You can call it whatever you want.
What you need to do though is separate your entry form into two parts. The main form where the user enters the store number (linked to the stores table) and a subform where they enter whatever details you need (linked to the details table). Then when the user enters a store number that is not in the stores table, pop up an entry form for details that you need to keep for the new store. On this new form you can have a button which they click to "add record" and in the onclick event for that button, type in If Me.Dirty then Me.Dirty = false endif DoCmd acCmdClose which will save the new record and close the popup (I think). Alternatively, pop up a message box stating that the store number does not exist and have an "add record" button on your current form for the above purpose. Either way, you need to get details for the new store record separate from whatever you are entering. -----Original Message----- Hi Tim, Can I name the "BelongsTo" field "StoreNumber" instead? So the one to many relationship would be 1(primary key) to many (foreign key)? Is that correct? Thanks, Tom "Tim Ferguson" wrote: =?Utf-8?B?VG9t?= wrote in news:B99C6B98-C081-4D0E-87BF- : I want this table to be able to be populated by adding data from the first table containing the foreign key. Whenever I setup a link, it operates as follows. I cannot add data to the Store# field, and it claims I have to add it to the referenced Store table. How do I set this up? You don't. You store information about Stores in the Stores table, and you store Address stuff in the Addresses table. If the Addresses table has a field called BelongsTo that references the StoreNumber field in the Stores table, then that is what is called a foreign key. Addresses ========= Stores *AddressID ====== BelongsTo ------ *StoreNumber TopLine CommercialName SecondLine PrimaryContact ThirdLine etc... PostCode PhoneNumber It's then easy to create a query that places the Stores.CommercialName above the Addresses.TopLine, or whatever you want to do. But you don't want to put address stuff in the Stores table, nor stores stuff in the Addresses table. Ever. Hope that helps Tim F . |
#7
|
|||
|
|||
Populate secondary table from table with foreign key
Tom,
There are a number of approaches to this type of situation, depending on the real-life data management processes. From what you have said so far, it sounds like a good approach would be like this... On your Transactions form, have your StoreNumber field represented by a combobox, whose Row Source is the Stores table. And put a little command button on the form, possibly in the form header section. Then, when you need to enter a Transaction for a Store which so far does not exist in the Stores table, click the Command Button, and it will open a separate form bound to the Stores table, at a new record, where you can enter the new Store. The only thing you will need then is to Requery the StoreNumber combobox on the Transacations form, so the drop-down list includes the newly added Store. This could be done on the Close event of the Stores form, the Activate event of the Transactions form, or the Enter event of the StoreNumber combobox. -- Steve Schapel, Microsoft Access MVP Tom wrote: Hi Steve, I think you've got how I set it up exactly. What I want to do is to have a form, with a "StoreNumber" field that users can add a new store number to. Currently, I've done away with ther link between the "Stores" table and the "Transactions" table, so new store numbers can be added in the "StoreNumber" field. Is there any way to do that? so that when a new store number is added to that field, it populates the related tabel with just a store number? or even the Store number, address, etc. ? Let me know if I'm not being clear enough on what I want. Thanks, Tom "Steve Schapel" wrote: Tom, It appears that you have two tables between which there is a one-to-many relationship. For example you have a Stores table, where the StoreNumber (note that it is not a good idea to use a # as part of a field name) field is the Primary Key, and the other table has the StoreNumber as a foreign key field. There can be more than one entry in the second table relating to the same Store. Am I right? In these sorts of scenarios, it generally works best if your record is already entered and saved in the table on the "one" side of the relationship (Stores) before you try to add the related record to the table on the "many" side. Can you please post back with more details and examples if you need more specific help. Thanks. -- Steve Schapel, Microsoft Access MVP Tom wrote: Hi, I'll try to phrase this question correctly. I need to Have a link to a table that contains store numbers and addresses. I want this table to be able to be populated by adding data from the first table containing the foreign key. Whenever I setup a link, it operates as follows. I cannot add data to the Store# field, and it claims I have to add it to the referenced Store table. How do I set this up? Thanks, Tom |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Table Analyser and more... | Mary Ann | Database Design | 2 | June 29th, 2004 06:36 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |
Populate Form and Table with Related Fields | Kevin Sullivan | Using Forms | 1 | May 26th, 2004 03:56 PM |