I am new to Access.... The challenge I am facing is populating the Foreign Key (in the Many side) number in a One to Many relationship scenario.

Example. Table A has say 50 entries and Table B has 500 entries. Table A and Table B are linked as 1 to Many. The primary key of Table a is a Foreign key in Table B.

I have to now link A with B and have to populate the Table B with the Unique id of Foreign Key (of Table A). It is painstaking to look back, search and populate.

I will have to do this 500 times. Is there a easier way to do it?

PLEASE HELP !

Thanks,
SG

how does the 500 item table GET populated, if you odn;t have a link

is there another link other than the ID?

if so, join the items on the other link, and use that to update the foreign key value. You have to do this sort of thing if you analyse say, a single spreadsheet into multiple tables

The 500 row table is created by uploading (importing) a spreadsheet. There is no link (if you mean some sort of hyperlink).

What I'm finding that I do not understand right now is that I have no idea how the secondary key column actually gets populated with the data that is entered into the primary key that it is linked to.

What is there IN the uploaded secondary table which lets you identify which parent table record it should be connected to? If you're doing it manually... how? What can you look at in the two tables to decide?

I will give you an example..

Table A has the folowing fields:

ProcessID - PK
High level process names
Process owner name
------
-----
etc


Table B fields
DetProcessID - PK
ProcessID - FK
Detail process names-
Activitiy
-----------
----------etc

I have linked ProcessID fields in both tables as 1 - to Many relationship. There are around 50 High level process names in Table A and almost 500 Detail process names in Table B.

I have uploaded the data from spreadsheet in both the Tables. The challenge is how do I populate the ProcessID column in the Table B?

Kindly advise.

Please provide actual examples of the data in both tables. The real table and field names would help. Possible upload a copy of the database file would help us to help you.

Hi Jerry, I have taken this from the actual example. I think I have explained the table and the problem. Problem is I cannot upload the access table from my office machine and my personal machine doesnt have access !

Foreign keys are not magical. They cannot just appear out of nowhere!

Again:

If you were looking at two sheets of paper, one with a dozen records from TableA, and the other with fifty records from TableB, how would you - as a human, not a computer - decide which record goes with which?

Access can only do this if there is some content in the data of the two tables that can be used to establish a match. You're talking about table structure, and it looks fine as is... but nobody here can see your data, nor can we suggest a way to create a link if you don't give us some more information!

Thanks John,

I have only one spreadsheet and the data is defined in the spreadsheet. What I mean is that the spreadsheet tells me what are the child processes for a parent process.
eg., the headers and the data looks like below:

High level process name | Detail process name

Customer operation | Customer request process
Customer operation | Complaint process
Customer operation | Benefit calculation process
IOM Claims | Recovery process
IOM Claims | Claims handling process
Financial reporting | Management reporting process
Financial reporting | Payment process
Financial reporting | Tax payment process

From the above you can see that the first 3 child processes belongs to the parent process called Customer operation.

I hope this gives you a better picture about the information I have and also clarifies the problem I am facing.

I look forward to your advice.

Thanks,

I can't think of any easy way to do this. Excel and Access are very different beasts, and this example is showing that bigtime!

Access (and all relational) tables are "homogenous" - all records have the same "shape". There are no "header" and "detail" records in a Table; there is no defined order to the rows of records; you can't have some records with two fields and others with six. It's not even clear to me from your example what kind of information is stored in the "Customer request process" cell - is it a single text field, a calculation, twenty columns of process steps, or what?

I fear you may need to write some nontrivial VBA code, with some of the business rules about the structure of this spreadsheet, to move the data into your normalized tables. On the other hand, I may be misinterpreting the structure completely, because of the semi-abstract description you've posted. Would it be possible to post a (trimmed down and sanitized of proprietary information) sample of the actual data?