Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    REferential Integrity (2003)

    I have two tables: Orders and Conditions. For each condition, there must be an order number. I am trying to set referential integrity in my relationships and am getting validaiton errors. There are Order numbers in the Conditions table that do not exist in the Orders table. Each Condition must have an order number in the orders table.
    I know there is a way to use queries to determine what order numbers in the conditions table are missing in the orders table. Can you help? I tried to attach a stripped down, zipped database, but I keep getting the message it is too big.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: REferential Integrity (2003)

    You can use the Find Unmatched Query Wizard:
    - Activate the Queries section of the database window.
    - Click New in the bar at the top of the database window.
    - Select Find Unmatched Query Wizard and click OK.
    - Select the Conditions table and click Next>.
    - Select the Orders table and click Next>.
    - Access will probably already have selected the Order number field on both sides. If not, do so now, then click the <=> button between the lists. Then click Next.
    - Add the fields you want to see to the list on the right. > adds a single field, >> all fields. Then click Next.
    - Specify a name for the query, then click Finish.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REferential Integrity (2003)

    Too Cool! Now that you mention it... I do seem to recall somewhere in my travels using this. I'd forgotten all about it. Thank you so much! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REferential Integrity (2003)

    Next step is getting these 541 Conditions Order Number in the Order number table? Would the proper way be to then use a Make Table query with these unmatched numbers and then append them to the Orders table?

  5. #5
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REferential Integrity (2003)

    Almost - you need an Append query rather than a Make Table query. (Make Table would put them in a new, separate table; Append will add them to an existing table).
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REferential Integrity (2003)

    I did an extra step. I made a new table, then appended them. However, I did find that some of the numbers didn't append because they already existed. Did I do something wrong?

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: REferential Integrity (2003)

    If you can have more than one condition per order, there may have been duplicated order numbers in the new table you created?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REferential Integrity (2003)

    It does seem a little strange - the Find Unmatched query should have only given numbers that weren't in your table already. My advice is to run the Find Unmatched query again now that you've appended the data - if everything's worked well enough, the query should return no records.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  9. #9
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REferential Integrity (2003)

    YES... there certainly IS more than one condition per Order. I will re-run the unmatched query and see if it comes up "clean".

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: REferential Integrity (2003)

    Set the Unique Values property of your append query to Yes. This will suppress duplicates.

  11. #11
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REferential Integrity (2003)

    OK

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •