Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2002
    Posts
    21

    Unanswered: Data Integrity: Records unconsistent between tables

    I have an old DOS FoxPro database that I am trying to port to access.

    There is an inventory table that contains all the part numbers, and I made the part number the primary key, and I am trying to link it to a recieved table, which contains all the dates when additional quantities of parts were added.
    Over time in the old database, people changed part numbers, and the recieved table is inconsistent, and has most part numbers that the inventory table does, but some that it doesn't.

    How can I easily find which records are in the recieved table that aren't in the inventory table?

    Thanks!
    John

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select * from recieved
    where partnumber not in
    (select partnumber from inventory)

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Aug 2002
    Posts
    21
    I don't know much about SQL, but my tables are called INVN and RECV. so would the SQL statement be:

    SELECT * FROM RECV WHERE PART NOT IN (SELECT PART FROM INVN);

    If so, do I just enter this into the SQL part of a select query?

  4. #4
    Join Date
    Aug 2002
    Posts
    21
    That code actually made Access lock up. I guess I really don't know what I'm doing!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    lock up? whoa

    how big are those tables?

    i should not have used "select star" syntax (lazy, as usual)

    perhaps try

    SELECT PART FROM RECV
    WHERE PART NOT IN (SELECT PART FROM INVN);

    rudy

  6. #6
    Join Date
    Aug 2002
    Posts
    21
    That took awhile and the query came up, but won't scroll down through the records or display how many records.
    There are about 55,000 records in the RECV table and 18,000 in the INVN table.
    This is on a 1GHz AMD with 512MB DDR RAM running Windows 2000 Server with Office XP Pro.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can get a count of unmatched records with

    SELECT count(*) FROM RECV
    WHERE PART NOT IN (SELECT PART FROM INVN);

    what were you planning to do with them, delete them?

  8. #8
    Join Date
    Aug 2002
    Posts
    21
    Try and figure out what new part number they should be and change them. Yikes! there's like 4,000 changed parts that haven't been updated!

Posting Permissions

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