Results 1 to 2 of 2
  1. #1
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143

    Inner Join query- without unique field


    I have two strange table where I cannot have unique fields, But I need to update daily transactions from temp table to the main table with out any duplicates..

    Code:
    INSERT INTO tblB_main ( FName,FType,Results,FileName, Date_Received, comments )
    SELECT I.FName,I.FType,I.Results,I.FileName, I.Date_Received, I.comments
    FROM B_temp AS I INNER JOIN tblB_main AS o ON (i.Date_Received =o.Date_Received) AND (i.FName= o.FName)
    WHERE ((((i.FName) Is not Null)) AND ((i.Date_Received) is Not Null)) AND ((o.FName) is Null) AND ((o.Date_Received) is Null);
    My temp file have records from 2 to 50, which are different from the records in the main table. Since I do not have unique values in both table to compare and append. I joined my tables with file name and date received fields.
    Some times my inner join query does not work.

    Any thoughts and suggestions?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Tables with no PK field cannot normally be updated using a query.

    Perhaps you should explain why your two tables cannot have unique fields.
    I expect that you will get replies telling you why you are wrong.

    As a starting point I suggest using a composite PK based on two or more fields such as those used to join the two tables.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 3
    Last Post: 09-26-2017, 11:19 PM
  2. Replies: 6
    Last Post: 09-10-2017, 05:59 PM
  3. Replies: 14
    Last Post: 01-29-2014, 06:28 PM
  4. Replies: 2
    Last Post: 11-11-2012, 09:02 PM
  5. Left-Outer Join on Non-Unique ID
    By defaultuser909 in forum Queries
    Replies: 2
    Last Post: 09-06-2012, 10:16 AM

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