Results 1 to 2 of 2
  1. #1
    qbee is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    2

    Query to Isolate Duplication in Some Fields But Not Others . . . .

    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.
    Attached Files Attached Files

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    would be better if you provided your db (remove tables/queries etc not pertinent to your question, compact then zip and upload the zipped file in the advanced editor) - responders can then see for themselves what the data actually looks like and what is already filtered out. For example, what is the justification/logic for excluding all but the last entry for lawnumber 570 but then excluding the last entry for 539, 253 and 555? And the logic for including 228 instead of 570?

    would also need to see the qryDetectduplicatecount query - presumably built with the find duplicates wizard?

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

Similar Threads

  1. Replies: 1
    Last Post: 11-13-2017, 04:34 PM
  2. Replies: 5
    Last Post: 08-30-2017, 09:24 PM
  3. Query Duplication
    By dancoe2004 in forum Queries
    Replies: 2
    Last Post: 08-24-2017, 06:44 PM
  4. Query Duplication
    By Lois in forum Queries
    Replies: 1
    Last Post: 11-22-2011, 08:47 AM
  5. Isolate Column in Combo Box
    By jgelpi16 in forum Forms
    Replies: 2
    Last Post: 05-06-2010, 12:52 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