How to populate foreign key field in MS Access 2007 after import of data?

[sg2808]

I have a question on Access 2007,

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 uploaded the data from spreadsheet in both the Tables. The challenge is how do I populate the ProcessID column in the Table B?

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.



Kindly advise.

[NeoPa]

Your question includes no logic with which to determine an answer.

[sg2808]

I am not sure if you have understood the problem mate.

[sg2808]

i am trying to understand how do i link the parent record with the child records. i am trying to link the parent child records after uploading the data to the tables.

[Rabbit]

NeoPa understood fine. You haven't included your business logic that lets us know how you know which records in A are linked to the ones in B.

[sg2808]

Thanks.

I am new to access and I guess I am missing the point. The data I have is that I have the list of High level process names (Parent) and the list of low level process names (Child) in a spreadsheet. Could you please suggest how do I go from here?

Many thanks in advance.

[NeoPa]

@Rabbit
Thank you. It gets tiresome having to explain that, actually, I know something about the subject. I would have thought that would be a good starting assumption in the circumstances.

@sg2808
From what you do include in your question post it seems clear that the data for [Table B], in its original form in the spreadsheet, has no values in the column for [ProcessID]. To be able to help we'd need to know the logic behind how one could determine which items of [Table B] are associated with which items of [Table A]. This information is still not available to us, and until it is there, in a form that makes sense, we are in no position to give any advice more helpful than we have already.

[sg2808]

Many thanks for your responses - @NewPa, @Rabbit.

As mentioned earlier, all i have is the spreadsheet (which shows the list of detailed process names (child) and high level process names (parent)) and I guess, it is up to me to develop the logic and build that in the database design.

So the question you have asked is what I am missing and trying to think of the logic.

My initial logic was that I would pick up the PK from Table A manually and then put the PK in Table B (as FK). I could do this by referring to the original spreadsheet.
However, this is huge task because I have around 750 child records for 80 parents records.

Any suggestion is hugely appreciated.

[NeoPa]

My turn to suggest that you may have misunderstood my point ;-)

It may actually be easier to explain if you posted some example data of type A and type B. I'm not entirely clear if this is from sparate files or simply separate worksheets in the same file, but seeing the data may enable us to see the logic. We're not looking for the algorithm from you. Simply the statement of the logic. I'm sure it'll become clearer when we see some example data.

[Rabbit]

Maybe this example will help clarify why we can't help without more information.

Consider the following tables

tblMeal
mealID (PK) | mealName
1           | Breakfast
2           | Dinner
 
tblMealItem
itemID (PK) | mealID(FK) | itemName
1           | ?          | Eggs
2           | ?          | Bacon
3           | ?          | Steak - Filet

Fill in mealID (FK) for me. You can't. Because you don't know if I want Eggs and Bacon for Breakfast, or Steak and Eggs, or just Eggs, or just Bacon. And similarly, you don't know if I wan't Bacon wrapped Filet for Dinner or just Steak.

That's the same problem we have with your question. You have X-type records and Y-type records but you don't tell us how we know which Y belongs to which X.

However, if I give you additional information, like I don't eat bacon or steak for breakfast, then you can fill it in.