473,856 Members | 1,720 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 #1
28 6257
NeoPa
32,584 Recognized Expert Moderator MVP
Your question includes no logic with which to determine an answer.
Mar 8 '12 #2
sg2808
91 New Member
I am not sure if you have understood the problem mate.
Mar 8 '12 #3
sg2808
91 New Member
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.
Mar 8 '12 #4
Rabbit
12,516 Recognized Expert Moderator MVP
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.
Mar 8 '12 #5
sg2808
91 New Member
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.
Mar 8 '12 #6
NeoPa
32,584 Recognized Expert Moderator MVP
@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.
Mar 8 '12 #7
sg2808
91 New Member
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.
Mar 8 '12 #8
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Mar 8 '12 #9
Rabbit
12,516 Recognized Expert Moderator MVP
Maybe this example will help clarify why we can't help without more information.

Consider the following tables
Expand|Select|Wrap|Line Numbers
  1. tblMeal
  2. mealID (PK) | mealName
  3. 1           | Breakfast
  4. 2           | Dinner
  5.  
  6. tblMealItem
  7. itemID (PK) | mealID(FK) | itemName
  8. 1           | ?          | Eggs
  9. 2           | ?          | Bacon
  10. 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.
Mar 8 '12 #10

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
15644
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
2552
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
4130
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
5085
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
2744
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
1860
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
6061
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
1906
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
9908
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
9760
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10695
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
10379
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
9530
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
7930
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
7090
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
2
4172
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3198
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.