Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2014
    Posts
    48

    Unanswered: Unmatched Records Query With Multifield Keys

    I need to create an Umatched Records Query where the two tables being compared have keys consisting of multiple fields. I've done this before but for some reason I'm drawing a blank here. The shared fields are Artist ID, Title ID, Year, MediaType ID and Disk Number. I'm trying to establish the relationships between my Media Locations Table and my Media Table. The record count in the Media Table is accurate and the data looks correct but when I attempt to establish the relationship between the two tables Access tells me that the Media Table contains records that dont have a matching record in the Media Locations Table.
    Last edited by Missinglinq; 10-24-14 at 11:55.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    All an umatched records query is is a LEFT JOIN from the child table to the parent table on whatever columns are relevant, and then reject any rows where the table ont he right hand side of the join is null
    The main problem is that Access uses its own variant of the JOIN Syntax
    http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
    say you wanted to match on album ID and track id. so that would be something like:-

    Code:
    select
      LHS.albumID,
      LHS.TrackID
    from
      ModifiedTable as LHS
    left join
      OriginalData as RHS
    on 
      (LHS.albumID = RHS albumID
       AND
       LHS.trackID = RHS.trackID)
    where
      isnull(RHS.albumID)
    or
      isnull(RHS.trackID)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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