Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    1

    Unanswered: MS Access Count Question

    The values showed below are not duplicate. What I'm trying to see if possible is counting the first instance of a value in a report. For example, my query returns seven lines, so of the values are the same. Is there a way where to create an additional column can be added to shown if multiple instances that same value exists?

    Trailer Ladder Month PO Vendor YesNO
    04050 SEP 293530 STUDIO RAY Yes
    18196 OCT 315902 KELLWOOD CO. Yes
    279852 SEP 300456 VAN HEUSEN Yes
    300159 SEP 342079 CASTLE HILL Yes
    300159 SEP 342087 DANNY & NICOLE No
    300159 SEP 348699 KATHERINE BISHOP No


    In Excel the same can be accomplished by using formula =IF(COUNTIF(A$1:A1,A1)>1,"No","Yes")

    Example

    123 Yes
    123 No
    123 No

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    In a query you can create a concatenated string on all of the fields you want to look for duplicates on and then create another query which looks for duplicates on the query with the concatenated string field (i.e. Find Duplicates query wizard...do the wizard against the query which has your concatenated string.) Then in the Find Duplicates query, add an expression (i.e. CR:1) - CR short for CountRecord but you can name it whatever. You may need to make this Find Duplicates query unique with just the concatenated string and CR expression in it (depending on whether you want to count the duplicated records as 1 record or as 2 records.) Then make another query which has this query in it and sum the CR column.

    So you'll have 3 queries total:
    1. The query with the concatenated string value
    2. The Find Duplicates query against the query with the concatenated string. Adding the CR:1 expression to this query and making the query unique.
    3. The query which has the Find Duplicates query in it and sums the CR column.
    Last edited by pkstormy; 09-15-07 at 01:58.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    SQL statement to show duplicate rows only.
    Code:
    SELECT Field1, Field2, ... , FieldN, Count(*)
    FROM   MyTable
    GROUP
        BY Field1, Field2, ... , FieldN
    HAVING Count(*) > 1
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are using this in a report then you coudl consider doing the count as part of the a report sub group using a bit of VBA...

    Trailer Ladder Month PO Vendor YesNO
    04050 SEP 293530 STUDIO RAY Yes
    (1) items for 04050 SEP
    18196 OCT 315902 KELLWOOD CO. Yes
    (1) items for ......
    279852 SEP 300456 VAN HEUSEN Yes
    (1) items for ......
    300159 SEP 342079 CASTLE HILL Yes
    300159 SEP 342087 DANNY & NICOLE No
    300159 SEP 348699 KATHERINE BISHOP No
    (3) items for 300159 SEP

    optionally you could suppress the group footer if the count is 1

    its pretty simple to do
    set up a group footer & header based on what ever criteria you require
    declare a variables in the report which is visible to all members of that report.. (say Dim intItemCount as integer), immediately after the 'option explicit' statement
    define a text box, right aligned sized for the maximum number of items you expect in the group footer and align a label to the right that box
    in the group header event set your count to zero intItemCount=0
    in the detail format event intItemCount=intItemCount+1
    in the group footer format event
    if intItemCount<=1 then
    cancel=true ' should stop printing the group footer if its 1 or less
    else
    <mytextbox>.text = format (intItemCount,"#,##0")
    <mylabel>.caption = "items for category:" & [<mysourcecountrolfor this group>]
    endif


    ...just athought, although if yoiu are working your way to the treehuggers standard ISO 14000...14003 then you'd be advised to do it as a query. cant waste paper now can we.......
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •