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:

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

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.

[sg2808]

@Rabbit, @NeoPa,

I will have to sit and digest what you have suggested but you guys are awesome. Take a bow.

Many, many......... thanks !

[Mihail]

Hi all !
I think that is a lot easier to manage this in Excel before importing in Access.
I attache the Excel file. You need to paste yours data in it then press START button.

But something sound wrong for me:
I think that Detail process name must be a table itself, isn't it ?
Answer to this question after you carefully read this:
http://bytes.com/topic/access/insigh...ble-structures

I worn you: If I am right and your database is not normalized you will fall in a lot of troubles later.

Also read this:
http://bytes.com/forums/feedback/915...ase-other-work
in order to be able to attach your database (if needed)

[sg2808]

What I have done to understand how it works is ..

I have created a spreadsheet with some dummy data to reflect the actual scenario. The headings in the spreadsheet are only:
High level process name and Details processes names. Remember, in the spreadsheet I know how the Parent-Child relationship.

As suggested by you, I then imported this spreadsheet as [tblImport]

I created a query to extract the High level process names (Unique values only) and created a table called [tblHighLevel]. The fields in this table is only [ID] and [High level process].

Similarly, I have also created a table called [tblDetail] with fields, [ID] and [Detail process].

@NeoPa - I am lost after this and I am not able to follow your point no 3. Is it possible to give me an example based on my tables and headers.

Many thanks,

[NeoPa]

When designing [tblHighLevel] include a Field (called [ID] for example) which is set as AutoNumber. As each record is added in step #2 (Your query will certainly not include adding a value for [ID]) a value for [ID] will be added for you automatically.

It should be straightforward from there.

PS. One small slip-up following the instructions. A good attempt in circumstances which are clearly quite new for you. I'm impressed.

[sg2808]

As mentioned in my previous post, in the [tblHighlevel] I have the two fields, [ID] which is set as auto number and [High level process name] already.

Sorry for being dim headed, I am still feeling lost and do not know how to link the child with the parents :=(.

[NeoPa]

You haven't described [tblDetail] very well as I hope you also have a field in there for the FK link to [tblHighLevel]. I'll assume you have a field called [HighID]. [tblImport] and [tblHighLevel] also need a field to reflect the name. I'll call both these fields [HLName] for simplicity.

Try something like :

INSERT INTO [tblDetail]([HighID],[Detail Process])
SELECT      tHL.ID
          , tI.[Detail Process]
FROM        [tblImport] AS [tI]
            INNER JOIN
            [tblHighLevel] AS [tHL]
  ON        tI.HLName = tHL.HLName

[sg2808]

NeoPa,
Sorry, I do not have the knowledge of writing and understanding SQL. Easier for me if you can explain the solution without the SQL.

Also, please tell me the logic you are using to link the tables. I guess, it will be easier then to understand the technical explanation.

Many thanks,

[NeoPa]

Have a look at Extracting/Updating SQL from a QueryDef for how to use SQL to create a saved Query in Access.

Explaining can get difficult when you don't know where to pitch the explanation. I have already explained at a level that I would consider basic. Without any idea of what you do understand it would be more complicated than you seem to realise. Anyway, review post #15 (Point #3) for an explanation.

[sg2808]

Hi NeoPa,

Thanks for all your help. I just checked the details of the SQL you had suggested on

http://www.w3schools.com/sql/sql_join_inner.as

and I could easily understand what you were trying to suggest.

So, that solves my problem and thanks a ton for all your inputs.

You are a star.

[NeoPa]

Thank you :-)

And that (w3schools) link shows you're looking in the right direction for going forward. Good call.

[sg2808]

Hi NeoPa,

It seems that this is good for one time (I may be wrong) exercise but how do I solve the problem when there are new records which are to be added in future?
Could you please share your thoughts?
Many thanks,
SG

[NeoPa]

As far as I understand what you've explained I see no problem for future runs. Clearly, I only know what I suggested, and your implementation may not be quite right, but I see no problem conceptually in the process as outlined.

[sg2808]

Exmaple - Say a user wants to add a new Detail process name (say using a form) then the user is also reuired to populate (manually) the corresponding FK in the same table to keep it linked with the High Level process names table.
ie, the FK will not populate automatically and it has to be done manually by referring to the PK number in the Parent table.

Kindly let me know if my understanding is correct and if this is the normal way updating the FK?

[NeoPa]

Your example seems to imply that you're talking about entering data using a completely different approach from the import that this thread is dealing with. That's a perfectly valid question (assuming I understand you correctly), but not pertinent to this thread. Heads-up - If asked separately you will need to make the question somewhat clearer too, otherwise it's likely to be deleted. A link into here is perfectly acceptable though, of course.

Obviously, if I'm on the wrong track then please just clarify and we can move on.