473,899 Members | 2,942 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

91 New Member
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.
Mar 8 '12
28 6260
NeoPa
32,584 Recognized Expert Moderator MVP
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 :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [tblDetail]([HighID],[Detail Process])
  2. SELECT      tHL.ID
  3.           , tI.[Detail Process]
  4. FROM        [tblImport] AS [tI]
  5.             INNER JOIN
  6.             [tblHighLevel] AS [tHL]
  7.   ON        tI.HLName = tHL.HLName
Mar 10 '12 #21
sg2808
91 New Member
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,
Mar 10 '12 #22
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Mar 10 '12 #23
sg2808
91 New Member
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.
Mar 11 '12 #24
NeoPa
32,584 Recognized Expert Moderator MVP
Thank you :-)

And that (w3schools) link shows you're looking in the right direction for going forward. Good call.
Mar 11 '12 #25
sg2808
91 New Member
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
Mar 13 '12 #26
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Mar 13 '12 #27
sg2808
91 New Member
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?
Mar 13 '12 #28
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Mar 13 '12 #29

Sign in to post your reply or Sign up for a free account.

Similar topics

4
4239
by: lupo666 | last post by:
Hi everybody, this time I have three problems driving me nuts :-((( (1) I have a report with 20 or so Yes/No "squares". Is there a way to either hide/show the "square" or change the yes/no relative textbox, depending on value? (2)
2
15647
nico5038
by: nico5038 | last post by:
Access 2007 Linkedtable manager refuses to relink tables having a field with the "Attachment" datatype. Problem: When placing a split database in another folder, the Linked table manager should be used to relink the tables. The Linkedtable manager does however refuse to relink tables having an "Attachment" datatype field. Symptoms: Open the Linkedtable manager and select a linked table with an "Attachment" datatype field. Now try to...
4
2553
by: Milan Mehta | last post by:
I am new to Access. I have developed an application in Access 2007. I have kept all the tables in a MDB file and all the forms in a seperate accdb file. All the tables of MDF file is link in the accdb file. As long as I was working in the local computer, everything worked fine. But when I put the backend MDB file in the server, and front end ACCDB file in the client computer, quite frequently the database (MDB) file is getting corrupt. Has...
0
1879
by: =?Utf-8?B?S3VydCBvZiBTYW4gSm9zZQ==?= | last post by:
All- Please advise as to what's the best way to display data in a column in a grid view (and I'm using a dataset) in a table (say TABLE2) which: - is non-zero only if a boolian field in the same row is true, and - when the boolean field is true, gets its value FROM ANOTHER TABLE? Say we have these two tables: TABLE1
2
4131
by: Mimi | last post by:
In Access 2007, I have successfully imported a text file with fixed width fields. However the import did not correctly interpret a date. In the text file the date field had no delimiter, was just MMDDYYYY. This column appears empty in my table. It doesn't seem like this is a hard thing to do. Any suggestions? Marianne
10
5091
by: kujito | last post by:
Ok, here it goes. I got my queries straightened out and they return the data I want in the format I want(finally). The data are sorted alpha. descending by ProjectName. Each project has a unique ProjectID, which is the left-most field. Sorting by ProjectName means that the ProjectID field is not in numerical order. ProjectID 109156 105428 2812 117973 The boss wants to have an ordinal number field called something like 'ItemNumber'...
0
2746
by: bkberg05 | last post by:
Hi - I currently use Access 2003 and have documents stored in an OLE data type field. I want be able to write a query that contains various other data elements from the table plus the OLE field and export these to an excel file. The query itself shows the document in the OLE field, but when I export the spreadsheet to Excel, the field holding the document shows up blank. Doing research, I saw Access 2007 had a different data type called...
0
1862
by: newsco | last post by:
Dear all, I use VB6 ADO connect to Access 2007 accdb database, everything is fine except I cannot retrieve image file stored in the Access 2007 attachment data type. Can anyone show me some vb code on how to do that? Thanks. Patrick
6
6070
TheSmileyCoder
by: TheSmileyCoder | last post by:
I need to create a table, where one of the memo fields shall be a Rich Text field. Can anyone help me with that? This is the code I currently have. Public Sub createConTable() 'Create the table for consequences Dim tblDef As TableDef Set tblDef = CurrentDb.CreateTableDef(strConTable, , , CurrentProject.Connection) With tblDef .Fields.Append .CreateField("KEY_Consequence", dbLong)
0
1907
by: prashantdixit | last post by:
Hi, I have beent trying importing Excel data with one column containing PDF/JPEG file name to access 2007. I have a Excel file with few columns One of the columns named as "Reference". The data in this is column is in following format. 2009_08_07_16_29_02_Part5.pdf 2009_08_07_16_29_02_Part1.pdf ....
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10866
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10497
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9671
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8043
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5891
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6082
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4301
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3320
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.