Results 1 to 6 of 6
  1. #1
    joel17 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Sep 2017
    Posts
    4

    Return records when certain fields are not repeated in same table.


    I'm sure this has been done before, but I can't find it in a post.

    I need to modify this query

    Code:
    SELECT LabInventory.Lab, LabInventory.Type, LabInventory.Drawer, LabInventory.ItemDescription, LabInventory.Label, LabInventory.LabelDetail
    FROM Category INNER JOIN LabInventory ON Category.[Category] = LabInventory.[Label]
    WHERE (((Category.Size)=Yes));
    I want to return every record in my table with a repeated Lab and Label field combination (i.e Lab 1, Label Beakers) that is the same for each record, only if the Type and Drawer fields are not the same in every occurrence of that combination. For instance:

    [ID] [Lab] [Type] [Drawer] [Label] [ItemDescription]
    1---- 1----- A------- 1------ Beakers---10mL
    2---- 1----- A------- 1------ Beakers--- 50mL
    3---- 1 -----B------- 1------ Beakers--- 100mL
    4---- 2----- A------- 1------ Beakers--- 50mL
    5---- 2----- A------- 2------ Flasks----- 25mL
    6---- 2----- A------- 2------ Flasks----- 10mL

    So the first three records would be returned, but since the 4th only occurs one time, it's not returned and the fifth and sixth are not returned because every instance of those field in question match.



    I hope this post makes since.

    Thank You.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you need 2 queries:
    Q1 to count the records / lab. ( lab1=3 beakers , lab2 = 1 beaker ),
    select Lab, Count(Lab) from tTable where [Label]='Beakers'

    Q2 using tTAble and Q1,
    join Q1 to tTable on Lab
    set criteria for CountOfLAb>1 , to pull item that repeat.

  3. #3
    joel17 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Sep 2017
    Posts
    4
    That will only count the number of records where label is "Beakers" I have over 200 unique labels, I cannot create 200 queries. I need it to return records for every combination of [Lab] and [Label] that have different combinations of [Type] and [Drawer].

    So for every Lab X entry that has Label Y, if all Type is A and Drawer is B, I don't get a result. But if I have 5 entries of XYAB and 1 XYAC I get all six results returned.

    I hope this clarifies. Thanks.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You will still need two queries. The first (call it qry1) will count the number of times each Lab-Label occurs, BUT it will only list those where the count is > 1.

    Select [lab], [label], count([lab]) from Table1 group by [lab], [label] where count([lab] > 1

    Then you could have:

    Select table1.* from table1, qry1 where table1.lab = qry1.lab and table1.label = qry1.label

    OR

    Select table1.* from table1 inner join qry1 on table1.lab = qry1.lab and table1.label = qry1.label

    (not sure I have the syntax exactly right, but I think so)

  5. #5
    joel17 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Sep 2017
    Posts
    4
    John_G Thank you for your reply. That's get's me very close. The 2nd query removes all the instances where the [Lab] and [Label] field match only one time. Now from that second query I need to return records where the [Type] and [Drawer] pairs don't match on every instance. I'm going to need at least one more query I know, I'm just not seeing the logic.

    So in the 2nd query if every [Lab] 1 record has a Beaker [Label] and a [Type] A and [Drawer] 5 then I don't want to show no matter if I have 1 or 10 records, but if 9 records are [Lab] 1, [Label] Beaker, [Type] A and Drawer 5, and one is [Lab] 1, [Label] Beaker, [Type] A and Drawer 4 then I need to return all ten records.

    I hope this clarifies it more. Thank you for your help.

  6. #6
    joel17 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Sep 2017
    Posts
    4
    So Close

    My orginal query


    SELECT qryLabelDetail.lab, qryLabelDetail.label, Count(qryLabelDetail.lab) AS CountOflab
    FROM qryLabelDetail
    GROUP BY qryLabelDetail.lab, qryLabelDetail.label
    HAVING (((Count(qryLabelDetail.lab))>1));


    from that I count the number of times Lab and Labels >1

    SELECT qryLabelDetail.lab, qryLabelDetail.label, Count(qryLabelDetail.lab) AS CountOflab
    FROM qryLabelDetail
    GROUP BY qryLabelDetail.lab, qryLabelDetail.label
    HAVING (((Count(qryLabelDetail.lab))>1));



    Then join those queries


    SELECT qryLabelDetail.Lab, qryLabelDetail.Type, qryLabelDetail.Drawer, qryLabelDetail.ItemDescription, qryLabelDetail.Label, qryLabelDetail.LabelDetail
    FROM qryLabelDetail INNER JOIN Q1 ON (qryLabelDetail.[Label] = Q1.label) AND (qryLabelDetail.[Lab] = Q1.[lab]);



    The a I made a third query cound

    SELECT Q2.Lab, Q2.Type, Q2.Drawer, Q2.Label, Count(Q2.Drawer) AS CountOfDrawer
    FROM Q2
    GROUP BY Q2.Lab, Q2.Type, Q2.Drawer, Q2.Label;



    now I need a way to join the queries such that I only see the records from Q2 when the count column in Q1 <> Q3

    Sorry for the bolding, I couldn't get the HTML to work.

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

Similar Threads

  1. To query repeated records
    By kentck86 in forum Queries
    Replies: 2
    Last Post: 01-10-2016, 12:35 PM
  2. Replies: 2
    Last Post: 10-01-2015, 10:10 AM
  3. Replies: 9
    Last Post: 08-19-2014, 12:41 PM
  4. Replies: 1
    Last Post: 06-27-2013, 09:33 AM
  5. Replies: 0
    Last Post: 03-06-2011, 04:10 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