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
|
|||
|
|||
Populating foreign key fields?
I am attempting to learn Access via the demos - but still need help - I am
setting up relationship between a Sales, Supplier & Customer tables. When I create a foreign field in these tables - do I need to populate this new field manually? If the Access issued ID numbers for each customer and supplier are present in those tables - how can I get those ID numbers to be recognized in the foreign fields? There are quite a few customers - with many Sales. . . Please help - |
#2
|
|||
|
|||
Populating foreign key fields?
If I understand your question, you enter the foreign key info when you create
a new record. Example: You enter a new invoice, the foreign key will be the customerID that you will have to select. Susita wrote: I am attempting to learn Access via the demos - but still need help - I am setting up relationship between a Sales, Supplier & Customer tables. When I create a foreign field in these tables - do I need to populate this new field manually? If the Access issued ID numbers for each customer and supplier are present in those tables - how can I get those ID numbers to be recognized in the foreign fields? There are quite a few customers - with many Sales. . . Please help - -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200908/1 |
#3
|
|||
|
|||
Populating foreign key fields?
Your Customer and Sales tables should look like:
TblCustomer CustomerID other customer fields TblSale SaleID CustomerID Other Sale fields TblSaleDetail SaleDetailID SaleID Line item fields Your data entry form should be a form/subform. The main form should be based on TblSale and your subform should be based on TblSaleDetail. The Linkmaster and Linkchild property should be SaleID. In the main form you will enter CustomerID using a combobox. The rowsource of the combobox would be TblCustomer. In the subform, when you enter data in the line item fields in a record, Access will automatically enter the SaleID in the main form for SaleID because you have SaleID as the Linkmaster/Linkchild property. Steve "Susita" wrote in message ... I am attempting to learn Access via the demos - but still need help - I am setting up relationship between a Sales, Supplier & Customer tables. When I create a foreign field in these tables - do I need to populate this new field manually? If the Access issued ID numbers for each customer and supplier are present in those tables - how can I get those ID numbers to be recognized in the foreign fields? There are quite a few customers - with many Sales. . . Please help - |
#4
|
|||
|
|||
Populating foreign key fields?
Yes, Steve (see later post) got it right. I should have thought of the
form/subform relationship. So, correction to my previous post is that with this structure the foreign key will fill in. June7 wrote: If I understand your question, you enter the foreign key info when you create a new record. Example: You enter a new invoice, the foreign key will be the customerID that you will have to select. I am attempting to learn Access via the demos - but still need help - I am setting up relationship between a Sales, Supplier & Customer tables. When I [quoted text clipped - 3 lines] the foreign fields? There are quite a few customers - with many Sales. . . Please help - -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Populating foreign key fields?
Thank you both for your response - but I have created this database by
importing all the data from one large Excel file. I then created the three tables by copying the excel spreadsheet into each table and then deleting columns were not specific to the individual table. I want to begin to query and analyze the data but realized I needed the relationship links. So these tables are full of rows & columns of data. In my Sales table - I have the Supplier Name (which corresponds to a column in the Supplier Table). So I already have all this data and trying to link it all together. Hoping there I was a way to do it without entering a corresponding ID # for each and every row. "June7 via AccessMonster.com" wrote: Yes, Steve (see later post) got it right. I should have thought of the form/subform relationship. So, correction to my previous post is that with this structure the foreign key will fill in. June7 wrote: If I understand your question, you enter the foreign key info when you create a new record. Example: You enter a new invoice, the foreign key will be the customerID that you will have to select. I am attempting to learn Access via the demos - but still need help - I am setting up relationship between a Sales, Supplier & Customer tables. When I [quoted text clipped - 3 lines] the foreign fields? There are quite a few customers - with many Sales. . . Please help - -- Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
Populating foreign key fields?
Try reimporting the Excel spreadsheet, and add an Autonumber column. Repeat
the copy/delete column technique, but make sure that you save the Autonumber column in each table. That is your link field. You will still need to consolidate your data. The Customer table will be the table that you make the autonumber the Primary Key. In the other 2 tables, change the datatype from autonumber to Number (Long Integer). These will be your Foreign Keys. In the relationships window link the primary key to each of the other table's foreign key. Consolidate and clean up the data. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Susita" wrote in message ... Thank you both for your response - but I have created this database by importing all the data from one large Excel file. I then created the three tables by copying the excel spreadsheet into each table and then deleting columns were not specific to the individual table. I want to begin to query and analyze the data but realized I needed the relationship links. So these tables are full of rows & columns of data. In my Sales table - I have the Supplier Name (which corresponds to a column in the Supplier Table). So I already have all this data and trying to link it all together. Hoping there I was a way to do it without entering a corresponding ID # for each and every row. "June7 via AccessMonster.com" wrote: Yes, Steve (see later post) got it right. I should have thought of the form/subform relationship. So, correction to my previous post is that with this structure the foreign key will fill in. June7 wrote: If I understand your question, you enter the foreign key info when you create a new record. Example: You enter a new invoice, the foreign key will be the customerID that you will have to select. I am attempting to learn Access via the demos - but still need help - I am setting up relationship between a Sales, Supplier & Customer tables. When I [quoted text clipped - 3 lines] the foreign fields? There are quite a few customers - with many Sales. . . Please help - -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|