1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: MS Access 2003: Cannot Compare & Delete Records

Discussion in 'Business Applications' started by RogueSpear00, Jun 12, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. RogueSpear00

    RogueSpear00 Thread Starter

    Joined:
    Jan 18, 2008
    Messages:
    472
    I'm pretty good with databases, but this one has me stumped as I can't find a solution anywhere, nor do I really know what the deal is - but I'm sure it'll be something so simple that I overlooked that I'll feel retarded.

    I have an access database that has a table with the following structure:

    company, address, state, zip, state, phone (all with data populated)

    I then created another table named "new" and added a field called phone.

    I put in XXX amount of phone numbers into the new table named "new".

    I basically want to compare the records from Table 1 Phone to the Phone in "new" table, and remove them from the original table.

    Everytime i do that, I get a "Recordset not updateable" error, I've tried all sorts of queries, etc. - no avail.

    /squirm - help!
     
  2. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    What about linking the two tables together in a query. When you link them it will create an inner join and only show you the records that are common in both.
     
  3. RogueSpear00

    RogueSpear00 Thread Starter

    Joined:
    Jan 18, 2008
    Messages:
    472
    No matter what I do, if I attempt to run a Delete Query - I get a "Could not delete from specified tables". There are no permissions set or anything.
     
  4. Gregor1234

    Gregor1234

    Joined:
    Jun 3, 2004
    Messages:
    237
    I cna't help but respond here because I've this exact same problem many times before. I just use a work-around...

    I'd use one query to flag entries in Table 1 where the Phone matches an entry in New Table, then use a second query to delete entries in Table 1 where the flag is "set"

    For convenience, you can "flag" Table 1 entries by placing a nonsense string in place of the existing value for Phone:

    Query 1 (udate query flags entries)
    UPDATE [Table 1] INNER JOIN [New Table] ON [Table 1].Phone = [New Table].Phone SET [Table 1].Phone = "000 000 0000";

    Query 2 (delete query deletes flagged entries)
    DELETE [Table 1].*, [Table 1].Phone
    FROM [Table 1]
    WHERE ((([Table 1].Phone)="000 000 0000"));

    I know this is this isn't as neat as doing it in one query would be but it works.
     
  5. RogueSpear00

    RogueSpear00 Thread Starter

    Joined:
    Jan 18, 2008
    Messages:
    472
    Gregor - Thanks

    I was getting frustrated with just not being able to do a Delete Query that I didn't even think of other data change then delete.

    Works :)
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    It can be done in one query using this setup
     

    Attached Files:

  7. Gregor1234

    Gregor1234

    Joined:
    Jun 3, 2004
    Messages:
    237
    This is an interesting situation and I can't say I understand why it sometimes works and sometimes doesn't.

    I would be interested in any ideas that OBP or anyone else might have might have on why this is the case...

    Taking OPB's posting above, I've tried a few things and found that it works only when:

    A) The ID field in OPB's example is present in both tables and linked with a join (rather than a simple WHERE criterion)

    AND

    B) The ID field and only the ID field is KEY in Table 2 (the "new" table... status of "ID" in Table1 doesn't matter)

    This means that any record in Table 1 will be joined with a maximum of one record from Table 2 but why should this matter in principal?

    Any notions?
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    I only looked at the Example presented by the OP to get it working, so I haven't explored any other combinations.
     
  9. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    If you would link the two tables in a query via the phone number it should only show the overlapping or inner join data like I was stating before. You could then convert this to a update query to update whatever values you like. I would ignore the primary key and utilize the phone number if it is formatted in the same manor.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/720848

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice