Foreign Key Issue

how do you auto populate an access foreign key field with the correct primary key data when you have thousands of records?

Lookups are great for entering a new record but how can can you apply it, if possible, or what other technique is required, to populate for existing records?
1 person has
this question
+1
Reply
  • Hello Steve,

    Are you referring to a specific book...perhaps FileMaker Pro 11: The Missing Manual? If you will be more specific about where you are in the book, we'll be able to help you better.

    Looking forward to hearing from you

    Nan
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • Sorry about that, I should have provided more info.
    I am using the Access 2007 The Missing Manual.
    Reference chapter 5 Linking Tables with Relationships.
    Lets use this as my scenario. I have a large database with several tables but we will use just two of them for this example.
    This problem will apparently exist for each of my multiple table relationships.
    I am very new at this, like this is my first, so I don't have a clue how it is actually done.

    I am obviously missing and/or not understanding something about how this works. Any help would be appreciated.

    Customers is the parent table. Orders is the child. I want to link Orders to Customers so I created a foreign key field in the Orders table called CustomerID and linked it to the Customers primary key. All great so far. But CustomerID in the Orders Table is empty of the primary key data for a relationship. I have created a Lookup combo box in the CustomerID field per chapter 5.

    Since I am dealing with a large amount of records, it is not practical to go through each and every record and assign the primary key using the combo box for every table and foreign key.

    I am looking for a way to populate CustomerID with the correct value in all existing records so i can run queries and reports.
    The same issue will apparently arise when i add new records with an update query from external data.

    How do you automatically fill the CustomerID field (or any of my other foreign keys) with the correctly associated primary key value without doing it by hand one at a time?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • Steve, thank you for your thorough reply. I will ask the author of this book for help with your specific question.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • I’m frustrated
    So, I guess if this can't be done automatically then it makes Access worthless for utilizing imported customer data or your personal imported data when creating multiple tables... which is a contradiction because isn't that what Access is for? To import data into multiple tables, assign a primary and foreign key in order to create a link for a relationship between tables in order to create queries, reports, and forms by utilizing any or all of the tables?

    If I am dense as a board, I can live with that since this is my first attempt, just point me in the right direction... But dealing with invoice numbers and customer IDs and purchase orders etc... the list of records can be from small to quite large in the related or "child" tables depending on what is stored. The only thing I have found was how to create a query to create a drop down list of primary keys (with associated names depending on the query) to manually assign the values to the foreign keys.

    There must be a way to automate this process. To attempt to do anything other than a new record by hand via a combo list to me is ludicrous, especially if thousands of records already exist from an import or when an append query adds thousand of new ones.

    I need a way to properly assign the correct values to a foreign key when there are 2 to millions of already existing records.

    If anyone knows how to do this, please advise.
    I appreciate any help.

    Thanks in advance.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • Hi Steve,

    I'll try to help but I need a bit more information. Assuming I understand your message correctly, you want to edit the Orders table, filling in the CustomerID field of existing orders with the appropriate customer’s ID. The question I have is how do you know that an order record is related to a particular customer? In other words, what information do you have to tell you which records belong to which customer if you don’t have the foreign key filled in?

    Typically, if you need to import sales records (for example) into Access, you'll have some sort of customer identifier. If that isn't the same as the customer ID primary key (which it quite likely isn't), you would need to look up the corresponding customer record to find out what ID to insert in the Orders table. This is the sort of operation that's typically handled by a bit of custom-written Visual Basic code.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • Hi Mathew,

    Thanks for responding. I am not sure how to explain it further or provide more detail but I will try.

    Let's use a Customer table and an Invoice table this time. It is the same type of situation throughout the database as the original question and has to be addressed for each instance and/or occurrence between the tables.

    As a for instance:

    Customer table... Parent, autonumber Primary key ID, hundreds of records
    Invoice table .... Child, autonumber Primary key ID, thousands of records

    Since i want a 1 to many relationship and not a 1 to 1, a foreign key is required in the child table.

    So, I Created a CustomerID field (number) in the Invoice table which is blank, then created a relationship from that CustomerID field (many) to the Customer table Primary key ID field (1). Obviously no queries or reporting can be accomplished until the empty foreign key is populated to complete the data association.

    How do I automatically or otherwise correctly obtain the Customer Primary key data from the autonumber ID field (1) and assign/associate/enter/populate the foreign key CustomerID field (many) with that data without the necessity of utilizing a lookup list and manually assigning each of those thousands of records.

    I understand the lookup is valuable and the list will be effective and necessary for individual changes or additions after the database exists. But starting out with imported data where hundreds and thousands of associations/assignments need to be made while creating the database is not practical nor desired. Nor will it be when an append query adds additional records.

    I understand that a select query will pull the Customer table ID Primary key data BUT how do you automatically or otherwise associate/assign/enter/populate the CustomerID foreign key with that ID information so the relationship between the Customer table (parent) and Invoices table (child) will work in that 1 to many relationship.

    This database will contain many tables that will act as both parent and child and therefore contain foreign keys and a few junction tables containing only foreign keys and therefore many 1 to many relationships and many foreign keys that must be populated.

    I know database gurus who create and populate databases do not do this manually. There are just too many records and assignments involved for this to be practical.

    Access is supposed to be user friendly so even a noob like me can create a database with foreign keys and populate those foreign keys but for some reason, from what I have read and/or watched in tutorials, this very important tid bit of information does not seem to be provided.

    Therefore, I want to know how database creators do this.... automatically or otherwise... for a start up database that contains a substantial amount of records with multiple tables containing multiple foreign keys all requiring population.

    I hope that helps.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • Hi Steve,

    Yes, you're correct that the lookup field is not important for this scenario (it's an editing convenience when you're adding single records). And yes, a solution that involves manually each record is obviously a bad solution.

    Most likely, you will need to write a VB code routine (or possible a macro) that manages the import or updating. However, I still don't entirely understand you situation. I understand that you need to associate your order records with your customer records by setting a field like Invoice.CustomerID. But how do you currently know what customer is associated with a given invoice? Obviously, the data you're importing must have this information in some form. (It can't just be lists of orders with no information about who ordered them!)
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • YES! Exactly.
    That is my problem.
    I do not know what customer was given that invoice except through the Customer table Primary key. I need to make that association between the Primary key and Foreign key. But how?

    Following the book as a tutorial, when I perform the import and create the tables, that information is split into the separate tables based on fields selected. Now, how to reconnect them through the Primary/Foreign key association?
    That is not described or explained in the book except through a lookup query to create a combo list.

    I am a noob and I am truly sorry if I am not stating it correctly or clearly and am being dense as a board but I believe I did everything correctly and yet my Foreign key field sits empty.

    This is the dilemma. I have a file. I imported it and created tables from selected fields. I created a Foreign key and then created a relationship. I have an empty Foreign key field. Now what?

    I am using the autonumber to assign the Primary key on import for each table and from what i understand, the Primary key IS the unique data required for the Foreign key. Hence, how do you get that Primary key information into the Foreign key?

    Do you have to import some related information from the source file into the Foreign key at the time the Invoice table is created and then connect that info to the primary key? Again, that does not seem to be described in the book so I am lost and confused and also provides a contradiction with the lookups described above.

    But let's see if my conversation with you has struck an epiphany.

    1) Import data from specific fields from the source file to create table 1 Customers.
    2) Import data from specific fields from the source file to create table 2 Invoices BUT also include at least one field associated with data from table 1 as the Foreign key and then use that field to to connect to the Primary key of table 1 Customers.

    But if that works and is the answer, then i will still have the same problem I have now AFTER the database exists and I decide I want to add a new Foreign key to an existing table or create a new Union table with only Foreign keys. How to populate the Foreign key field with the correct Primary key data from existing tables.

    This would also seem to contradict the book that says to use the Primary key and an additional field as a description for the underlying Primary key for the lookup list.

    This has to be so simple that I am just missing it or over thinking.
    However this is done it should not require VBA code otherwise a noob like me who knows nothing about VBA could use it. It has to be possible and simple or Access would not exist as an office package for anyone like me to use.

    Gah! I am confused and feel soooooo stupid. It goes around in circles and always back to the same question. How to populate that Foreign key field correctly either from an import or existing tables.

    This does not solve my problem yet, but you have made me think in a different direction and I thank you for that.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • >> 1) Import data from specific fields from the source file to create table 1
    >> Customers.
    >> 2) Import data from specific fields from the source file to create table 2 Invoices
    >> BUT also include at least one field associated with data from table 1 as the
    >> Foreign key and then use that field to to connect to the Primary key of table 1
    >> Customers.

    If that approach works for you, it is the best choice. However, it's impossible for me to tell you if it will based on the information you've supplied, because it depends on the structure of the data you're importing. If this doesn't work, you will need to do your importing with VBA, which *is* an integral, built-in part of Access (and this is exactly the sort of area where it's commonly employed). Most databases will use VBA at some point, particularly with forms. In fact, Access didn't even have macros, the only real VBA alternative, until Access 2010.

    Or, to put it another way, yes a "noob" can use Access to create tables, make relationships, and edit data, but no maybe a "noob" won't be able to do a custom import operation that maps their data to the structure of their database--at least until they learn the basics of VBA. :)

    >> But if that works and is the answer, then i will still have the same problem I have
    >> now AFTER the database exists and I decide I want to add a new Foreign key to
    >> an existing table or create a new Union table with only Foreign keys. How to
    >> populate the Foreign key field with the correct Primary key data from existing
    >> tables.

    I don't understand what you're getting at here... Do you mean a junction table or a union query? And if you're creating a new junction table that connects tables that already exist, of course you'll need some way to populate that data, which would probably (once again) be a code routine that you'd write in VBA. How else could Access possibly know what you'd want to put in your junction table?

    If you want to talk about the specifics of your data--for example, the exact structure of what you're importing--feel free to email me. My email address is my first name (matthew) followed by @prosetech.com.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • Hello

    I have a similar problem with primary and foreign keys. I also am using the book Access 2007 The Missing Manual by Matthew MacDonald. I set up a one to many relationship between two tables. I want to enter records into each table using a form for each table. I created a drop down list on the foreign key in the dependent table. It displays a list of primary key values and an identification field for each. My customer does not like this approach because they point out that the list contains all the primary key values in the parent table and the user can too easily select the wrong one.

    My question is - are there other ways to transfer the correct primary key value to the foreign key field that guarantees accuracy?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • Hi Leslie,

    There are plenty of ways to fill in a field, but it depends on your exact situation. Namely, how does the form-filler go about choosing the right value?

    You mention that the customer doesn't like the fact that a user can select the wrong value from a lookup list. But surely no matter what way you allow the user to choose, there's a possibility that the user will make a mistake. To minimize this possibility, you need to have a clear idea of what the stumbling block is in your particular form. Is the problem one of:
    * Feedback? Perhaps there should be some indication after the field is selected that confirms the linked record information, and hopefully alerts the user to mistakes.
    * Unfiltered data? In other words, you don't need the list of all the values, because you already know that only some of them apply, and showing a full lookup list is complicating the issue.
    * Data presentation? Maybe the lookup list isn't showing the right information for the user to identify the correct field. Or perhaps the act of scrolling through a drop-down list is awkward for your type of data (which can be the case if you have lots of records or the records are identified by long text fields).

    The strategy you need depends on the type of issues you're facing, and there are solutions to all of these example problems.

    Matthew
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • Thank you for the reply, Matthew. I agree preventing the user from selecting the wrong primary key value is almost impossible but my goal is to try to set up a situation where selecting the correct value is as certain as possible. I have no answers yet but I will investigate the areas you mention.
    Feedback - 1. Possibly display the record from the parent table as a subform on the data entry form for the dependent table. 2. Display an instructional message on the dependent table form explaining the danger etc. 3. I like your idea of a confirmation action but don't yet know how to implement it.
    Unfiltered data - 1. It is difficult to filter the data because it is an automatically generated number. But it is associated with the name field of the project. If the user was required to enter the project name in a select query then the list would contain just the key value. I'll look into that.
    Data presentation - I do not see this as an issue.

    Thanks again for your reply.

    Les
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • Hi Matthew,

    My design solution to the drop down list security issue is to go into the select query generated by the drop down list wizard and add a bracketed variable to the where clause. When a user clicks on the drop down list button for that field, a pop up requests the job title. Upon entry of the job title and clicking OK the query is executed and a single line drop down list containing the correct primary key value is generated. Selecting that entry stores the primary key value in the foreign key field.

    Thanks for the help.

    Les
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly