Greetings,
I have worked on this for a couple of weeks without much success.
I am trying to detect duplication in three fields (for simplification, I will refer to these as "Title", "Chapter", and "Section") within many hundreds of Records.
Each Record is identified by a "LawNumber". Typically, each "LawNumber" will have multiple "LawSections" (not the same as "Section") associated with it.
The same "Title", "Chapter", and "Section" may be listed several times in connection with the same "LawNumber".
The goal is to pop out all duplicate entries of "Title", and "Chapter", and "Section", but only where "LawNumber" is different. In other words, I am trying to detect overlapping entries of "Title" and "Chapter" and "Section" across different "LawNumbers".
I have attached a Word doc. to demonstrate the output I am getting, and the output I would like to see. In the attachment, rows shaded in gray reflect what I do not want to see. Rows appearing in red font depict what I would like the output to show.
Here is the SQL that I have been working with:
Code:
SELECT tblRawData.LawNumber, tblRawData.Title, tblRawData.Chapter, qryDetectDuplicateCount.Section, tblRawData.EffectiveDate, qryDetectDuplicateCount.CountOfLawNumber
FROM tblRawData INNER JOIN qryDetectDuplicateCount ON (tblRawData.Section = qryDetectDuplicateCount.Section) AND (tblRawData.Chapter = qryDetectDuplicateCount.Chapter) AND (tblRawData.Title = qryDetectDuplicateCount.Title)
GROUP BY tblRawData.LawNumber, tblRawData.Title, tblRawData.Chapter, qryDetectDuplicateCount.Section, tblRawData.EffectiveDate, qryDetectDuplicateCount.CountOfLawNumber
HAVING (((tblRawData.Title)<>0) AND ((tblRawData.Chapter)<>29))
ORDER BY tblRawData.Title, tblRawData.Chapter, qryDetectDuplicateCount.Section;
I tried the DISTINCT route through Access query properties, but found the output to be no different with or without it, and regardless of my use or non-use of query criteria. Similarly, I gather that, e.g., FIRST is of no help here.
I could not locate (or think of) another means or method to make this work.
Your helpful suggestions are most welcome. Thanks.
q.