Results 1 to 5 of 5
  1. #1
    venomshot is offline Novice
    Windows Vista Access 2000
    Join Date
    Feb 2010
    Posts
    4

    Validating Field Data Across Tables?

    Hello all,

    I am new to Access and am trying to create my first test database, based around inventory..


    I have 3 tables: Inventory, ResponsibleParty and SpecSheet.

    What i would like is that when someone is filling out a form to add something into inventory, the Inventory table with check with the ResponsibleParty table to verify that the user has already been entered first, and check in the SpecSheet to verify that the computer model (for example) has been entered in.

    In other words, I want to make sure the ResponsibleParty and Computer Model has already been entered into the database before the user is allowed to enter that information. Then, if they try to add a "Dell" to inventory, belonging to "Bob", both of those entries will have to already exist in the other two related tables.

    Is there any way to require such data has been entered into the other tables first, and for Inventory table to check, and if data does not match (maybe misspelling of name) an error will pop up to say enter name in ResponsibleParty table first?

    I have tried to make the 3 tables relational, but it is saying all need primary keys, which I do not understand if I am just trying to basically datacheck across tables?

    Thanks in advance



    - Chris

  2. #2
    NassauBob's Avatar
    NassauBob is offline Not THAT Green
    Windows XP Access 2003
    Join Date
    Feb 2010
    Location
    Augusta, GA
    Posts
    61
    What you are referring to here is called Referential Integrity, and is discussed in detail here:http://support.microsoft.com/kb/304466

    You set these constraints in the Relationships window. To get there, click on Tools, then Relationships.

    And yes, every table will be required to have a primary key, a foreign key, or both.

  3. #3
    venomshot is offline Novice
    Windows Vista Access 2000
    Join Date
    Feb 2010
    Posts
    4
    Quote Originally Posted by NassauBob View Post
    What you are referring to here is called Referential Integrity, and is discussed in detail here:http://support.microsoft.com/kb/304466

    You set these constraints in the Relationships window. To get there, click on Tools, then Relationships.

    And yes, every table will be required to have a primary key, a foreign key, or both.
    Great, that was exactly what I needed; thank you!

    One more thing, now that I know how to make relational tables...

    Lets say I have 3 tables: PersonTable, LocationTable, and InventoryTable. If I have a PersonID from the PersonTable linked to a LocationID from the LocationTable, can I have an InventoryTable form automatically fill out the LocationID once I enter a PersonID?

    For example, if in PersonTable I say PersonID "John" has a LocationID of "California", when I am filling out an InventoryTable form (which is relationally linked to both LocationID and personID) can I type "John" in the personID and have it automatically fill in that he has a LocationID of California, since I had already entered it previously in PersonTable?


  4. #4
    NassauBob's Avatar
    NassauBob is offline Not THAT Green
    Windows XP Access 2003
    Join Date
    Feb 2010
    Location
    Augusta, GA
    Posts
    61
    You say "automatically"...well, you can make it seem automatic, with the AfterUpdate property of the personID text/combo/listBox. What you do is go into the property sheet of the personID box, and in the event property, have a query run, with the value you set in personID as the parameter to the query. Then, set the data source of the locationID object to that query. Oh, this will require the query to return only one value, the location of the person in mind. If all is right, you should see the locationID object display "California" ONCE IT HAS THE FOCUS, if the personID object is set to "John".

    Try that!

  5. #5
    venomshot is offline Novice
    Windows Vista Access 2000
    Join Date
    Feb 2010
    Posts
    4
    Thank you again, I will give it a try!

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

Similar Threads

  1. Data Sharing within Tables?
    By clai in forum Database Design
    Replies: 1
    Last Post: 11-06-2009, 06:32 PM
  2. Need to replicate data in 2 tables
    By magister011 in forum Access
    Replies: 5
    Last Post: 11-02-2009, 04:55 AM
  3. Replies: 0
    Last Post: 06-17-2009, 09:13 PM
  4. Replies: 1
    Last Post: 03-31-2009, 09:03 AM
  5. Validating tables
    By JVagenheart in forum Database Design
    Replies: 1
    Last Post: 06-10-2006, 09:03 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