Populating foreign key fields?

[Susita]

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 -

 

[June7 via AccessMonster.com]

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.

 

[Steve]

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
(e-mail address removed)

 

[June7 via AccessMonster.com]

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.

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 -

 

[Susita]

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.

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.

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 -

 

[Arvin Meyer [MVP]]

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

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.

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.

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 -