A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Populating foreign key fields?



 
 
Thread Tools Display Modes
  #1  
Old August 31st, 2009, 12:19 AM posted to microsoft.public.access.gettingstarted
Susita
external usenet poster
 
Posts: 2
Default 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  
Old August 31st, 2009, 12:31 AM posted to microsoft.public.access.gettingstarted
June7 via AccessMonster.com
external usenet poster
 
Posts: 173
Default 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  
Old August 31st, 2009, 12:39 AM posted to microsoft.public.access.gettingstarted
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default 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  
Old August 31st, 2009, 01:19 AM posted to microsoft.public.access.gettingstarted
June7 via AccessMonster.com
external usenet poster
 
Posts: 173
Default 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  
Old August 31st, 2009, 02:45 AM posted to microsoft.public.access.gettingstarted
Susita
external usenet poster
 
Posts: 2
Default 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  
Old August 31st, 2009, 04:18 AM posted to microsoft.public.access.gettingstarted
Arvin Meyer [MVP][_2_]
external usenet poster
 
Posts: 2,310
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.