Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2008
    Posts
    4

    Exclamation Linking Records

    Hi Guys,



    Here's the long story shortened. I created a database with all sorts of tables and forms. My company makes marking machines and i wanted a database to manage the inventory of components for each of the machines. Here is what I want to be able to do.

    Some of the machines have matching components so we order a number of them from our vendors for stock and they're used in 3 or 4 different assemblies. What I want to be able to do is, if someone updates a record in one of the machine component tables (lets say an electric motor) i'd like that same electric motor to be updated in the other tables as well.

    So if someone tries to check stock on this motor they can see that we have the right amount of parts in stock and the correct value is displayed in all of the tables that contain this component. Currently, i just have this component (record) listed in all of my machine component tables that use this motor. 4 tables in all. And if I changed the QTY on 1 of the tables, the others would still stay the same.

    SO is there anyway to link these records from all of these 4 tables to change whenever I change them in any of the tables?

    I am fairly versed in VB and have a good amount of experience in Access with macros and other Build Events. Anyone got any ideas?? perhaps do I need to restructure the DB somehow? I would like to keep it so that if i open up the form which is bound to the same table to show all of the components for this machine and still have them linked somehow as i've explained earlier... It's a toughy I think.

    Thanks for the help.

    Tim

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    I would think you would have a table with all of the parts and quantities. Then another table with all of the machine description records with part numbers. Then the form that displays the parts in each machine simply joins the two tables and the quantity only exists on one table, as it should be in a properly normalized datanase.

  3. #3
    Join Date
    Sep 2008
    Posts
    4
    So I should restructure the database so that the specific record should have a field in it to categorize it according to the Machine it belongs to. Then the form should filter the results displayed for that field upon loading?

    Does this sound right?

    OK I can do that.

    But how can I setup that field and make it so that this Motor component displays in all the 4 machines...

    Say I create a field called "machine" and then i put the name of the machine in that field which the component belongs to. So then i set a filter on the form for the specific machine to ONLY display the records in the dataset that have the specific value in the "machine" field.
    Using this as my method, how would I allow that particular record to show up in more than one filter? IE Machine Should the record show.....

    machine1, machine2, machine3....
    and then my filter would have the ability to see those are in there?

    thanks

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    To begin with, only the PrimaryKey (PK) should be added to other tables as a ForeignKey (FK). Hopefully you are using AutoNumbers as the PK so a LongInteger field will be the FK. Then you will have a table that contains the parts that go in each machine. Each record in this table will have a FK of the particular machine along with a FK of the part involved. There will me many records with the same machine FK, each record having a different part FK. The FK for the part can show up in records for every machine if necessary.

  5. #5
    Join Date
    Sep 2008
    Posts
    4
    Yeah that doesn't make much sense to me at all, I haven't heard of a Foreign Key before nor can I find an option to set a field as one.

    I have done this so far, i have created a new table and I've copied all the existing data from all of the other machine tables into it and I've deleted the duplicates so that there only exists one of the Part Numbers for the particular components. Then I added a field onto the database for the "Machine_Type" field. In here I listed the machines which this components is used in... so the field in this record looks like this.

    Machine1, Machine2, Machine3
    Machine1
    Machine1
    Machine1
    Machine3
    Machine2, Machine 3

    SO that's an example of how the table looks showing only the last field in 7 records. So now how can I make a filter which will search for only the specific data in this "Machine_Type" field and allow those records to be displayed in the correct form. Better Explanation: I want to have a form with a filter that will (onLoad) filter the results being displayed so that they can only be displayed if they contain "Machine1" in the Machine_Type field.

    Even though in some cases i have a comma separating a few values in the record as seen above in the example. Or am I doing this the wrong way again......... Access 2007 I should note.

  6. #6
    Join Date
    Sep 2008
    Posts
    4
    Nevermind,

    I think I got it, I just load the data of the form through a query and I have the query only allow results with the criteria of Like"*Machine1*" and that works perfectly

    Thanks for your help though!

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Glad you got it working. ForeignKeys are simply fields that contain a PrimaryKey field of another table. FK is simply what we call then for short. It is then simple to join two tables in a query by the FK field.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Linking Tables?
    By briancb2004 in forum Access
    Replies: 0
    Last Post: 09-29-2008, 01:14 PM
  2. Resource for using linking tables with forms?
    By narayanis in forum Forms
    Replies: 5
    Last Post: 05-18-2008, 04:11 PM
  3. Linking Multiple records
    By rricci@marcct.org in forum Programming
    Replies: 0
    Last Post: 02-14-2008, 09:18 AM
  4. Linking tables and forms
    By vgatell in forum Access
    Replies: 0
    Last Post: 02-10-2007, 01:37 PM
  5. Replies: 2
    Last Post: 09-14-2006, 04:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums