Results 1 to 3 of 3
  1. #1
    suverman is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    15

    Delete Table intersect.

    I have two tables with common fields.



    One table has all the data I need, say PAYIN and JAN has few records with common fields. I need to query and delete the records from Table A where records have common field in table B.

    Eg.

    Table: Payin
    ID Value Date che_no Amount
    5 04/01/2011 205033 2306
    6 04/01/2011 216811 1239
    7 04/01/2011 335447 1122
    8 04/01/2011 216810 630
    9 04/01/2011 71918 437

    Table: View
    ID Value Date che_no cr_amt
    5 04/01/2011 205033 2306
    8 04/01/2011 216810 630



    Required query result
    ID Value Date che_no cr_amt
    6 04/01/2011 216811 1239
    7 04/01/2011 335447 1122
    9 04/01/2011 71918 437



    Basically, deletes the records that come from the below query from PAYIN.

    SELECT PAYIN.CHE_DT, PAYIN.CHE_NO, PAYIN.AMOUNT
    FROM PAYIN LEFT JOIN Jan ON (payin.amount=Jan.AMOUNT) AND (PAYIN.CHE_NO=Jan.CHE_NO)
    WHERE Jan.CHE_NO IS Null and Jan.AMOUNT is null;


    Thank you so much. I am new to sql.

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,465
    Look at this video on deletes.

    http://www.datapigtechnologies.com/f...teproblem.html

    Alan

  3. #3
    suverman is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    15
    Hey, thanks a lot for that, it worked. Just one more thing, if I want to delete common records from both table, how do I do that?

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

Similar Threads

  1. Replies: 11
    Last Post: 03-30-2011, 01:08 PM
  2. Delete Table on Report_Close
    By nhabegger in forum Programming
    Replies: 6
    Last Post: 07-20-2010, 09:32 PM
  3. Replies: 1
    Last Post: 04-29-2010, 05:22 PM
  4. Delete duplicates within one table
    By zbreima in forum Access
    Replies: 1
    Last Post: 01-28-2010, 06:49 PM
  5. Delete all records in a table?
    By bob646 in forum Access
    Replies: 1
    Last Post: 05-20-2007, 11:41 PM

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 - Senior Forums