Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete records (WIN XP/Access 2003)

    I have 2 tables.
    AES DOWNLOAD
    G32 MEMBER COUNT

    I want to delete records from table AES DOWNLOAD that have a matching record in G32 MEMBER COUNT

    I am using this code but it is obviously missing something - it says I must specify the table I am deleting the records from.
    I want the records deleted from table AES DOWNLOAD


    DELETE DISTINCTROW [G32 MEMBER COUNT].MEMNUM, [AES DOWNLOAD].MEMNUM
    FROM [G32 MEMBER COUNT] LEFT JOIN [AES DOWNLOAD] ON [G32 MEMBER COUNT].MEMNUM = [AES DOWNLOAD].MEMNUM
    WHERE ((([AES DOWNLOAD].MEMNUM) Is Not Null));

    Thank you in advance for you help.

    Sincerely,
    Michael

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

    Re: Delete records (WIN XP/Access 2003)

    If you want to remove records from AES DOWNLOAD that have a matching record in G32 MEMBER COUNT, your query is the wrong way round: the join should be a RIGHT JOIN (arrow pointing in the other direction), and the criteria IS NOT NULL should be under the MEMNUM field from G32 MEMBER COUNT, not AES DOWNLOAD. But it's not going to work as a delete query anyway. Try this:

    DELETE [AES DOWNLOAD].MEMBERNUM
    FROM [AES DOWNLOAD]
    WHERE [AES DOWNLOAD].MEMBERNUM In (SELECT [G32 MEMBER COUNT].MEMBERNUM FROM [G32 MEMBER COUNT])

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete records (WIN XP/Access 2003)

    I can humbly say Thank you once again Hans.

Posting Permissions

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