none
[HELP] how to count records without duplicate MS ACCESS RRS feed

  • Question

  • hi guys..

    in this proof my project counts the all record with duplicate :/

    i need count reocrd but without duplicate and also when i make sort see the result

    also he count duplicate :/ how can i count without duplicate

    you can download project from this link:

    https://ufile.io/806aw

    Tuesday, May 1, 2018 5:45 AM

Answers

  • Hello Korg.Pa3x,

    What's the result you want to get? The shared picture is a result while filtering the Cname. 

    Could below sql work for you? If not , please share a screenshot to detail what result you want.

    Select [Table].*,T.CountNumber from [Table]
    left join
    (SELECT COUNT(*) as CountNumber,cname FROM [Table] group by Cname) T
    on [Table].[Cname]=T.cname
    order by [Table].[Cname]

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Salar Music Wednesday, May 9, 2018 3:42 PM
    Wednesday, May 2, 2018 10:29 AM

All replies

  • Hello Korg.Pa3x,

    What's the result you want to get? The shared picture is a result while filtering the Cname. 

    Could below sql work for you? If not , please share a screenshot to detail what result you want.

    Select [Table].*,T.CountNumber from [Table]
    left join
    (SELECT COUNT(*) as CountNumber,cname FROM [Table] group by Cname) T
    on [Table].[Cname]=T.cname
    order by [Table].[Cname]

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Salar Music Wednesday, May 9, 2018 3:42 PM
    Wednesday, May 2, 2018 10:29 AM
  • Hi KORG.Pa3x,

    This is expected behavior since the count function count the number of rows. To count the numbers without duplicate value, you may need to write a custom VBA code. 

    For example, when the sub-form loaded, you can loop the record set of it then calculate the number of filed without duplicate values.

    Regards & Fei

     


     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 2, 2018 10:56 AM
    Moderator
  • Hello KORG.Pa3x,

    What's the current state of the thread? Please feel free to let us know if our replies is helpful for you. If not, please share us more detail about your issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 4, 2018 9:03 AM