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.

[sg2808]

If you have a email id, i can send you the sample spreadsheet.

I have just one spreadsheet with lot of columns, some of the headers are as below:

Business Unit | High level process name | Process owner | Detail process name | Activity name | Processing Location | Risks | Controls

From above, I am building Tables for High level process and Detail process.
Example is as below:

[B][U]High level process name[/U] | [U]Detail process name[/U][/B]
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

I have a long list as above... but hope it should give you a better picture.

So for the first three detailed process, I know that the parent is Customer operation and so on and so forth.
The relation is already there in my big spreadsheet. So I know which child process is linked to which Parent.

[Rabbit]

You didn't say that everything was in one big table in the spreadsheet. That is key information that would have been useful to have up front.

You need to import the whole thing into one table in Access and then split them out into their own tables.

1) Import the excel table
2) Select the distinct high levels and insert them into their own table.
3) Join the newly created table back to the imported excel table on the high level description, that will give you the high level id.
4) Insert the detail records into their own table.

[sg2808]

Many thanks @Rabbit for your response and your patience.

I am not very clear with your solution as it is not clear how will i link the "High level process" Table with the "Detail process Table".

If you have now understood what and how the information I have and if you now refer to my original first, could you kindly clarify how your below solution answers my query?

Again, thanks in advance for your support.

-+-+-+-+-+-+-+-+-+-+-+-

[Rabbit]

By join, I mean a join in a SQL query. If you do not know what that is, you will want to learn the basics of SQL before proceeding as it is one of the foundations on which most databases are built.

[NeoPa]

As you'll see from Rabbit's response, that information was very helpful in filling in the gaps you left in your question. Now it's quite clear the sort of thing you're after.

I find Rabbit's explanation quite clear too, so I'm not sure I can help, but I'll go through it anyway and we'll see if you can understand it better :

1. Import your spreadsheet data into a table in Access. For simplicity of explanation we'll call this [tblImport].
2. With the data now in [tblImport] you can design a query in your database that appends data to another table ([tblHighLevel] for simplicity) from [tblImport]. This query should make use of the DISTINCT predicate or set the UniqueValues property of the QueryDef (Saved Query object) to Yes. Include all fields which contain data which is relative to the High Level item. These should all be the same for each reference to that item (or your data is not as you've implied).
3. Assuming [tblHighLevel] has an AutoNumber [ID] field, design another query that links [tblHighLevel] and [tblImport] together on the name value and put the data, including all Detail fields as well as the [ID] field from [tblHighLevel], into another table ([tblDetail] for simplicity).

Hopefully this clarifies the steps for you somewhat.