10-24-14, 02:59 #1Registered User
- Join Date
- Sep 2014
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.
10-24-14, 05:00 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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
say you wanted to match on album ID and track id. so that would be something like:-
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