none
Count Duplicate Records RRS feed

  • Question

  • I have a report in Access 2003 that displays movement of inventory. Some of the items have been moved more than once. The report is running fine. What I want to do is to count the number of items which have moved more than once. I created a summary query which lists the duplicated records once. The quantity in this query is correct. It contains 136 Records. What I tried to do is a Dcount function on this query at the bottom of my report. However it gives me #Error. When I look up the Dcount function it says that it doesn't work when you have parameters included. Is there another count function whereby I will only be counting the number of records that are duplicated (moved more than 1 time in the warehouse)? Thanks.
    Tuesday, January 18, 2011 9:00 PM

Answers

  • use your sql that creates the 136 records as the "from" statement wrapped in () and count () for one of the fields in that query the result will be 136

    here is an example of an query that will do that

     

    SELECT Count(signoff) AS CountOfsignoff1
    FROM (SELECT Prod.signoff, Count(Prod.signoff) AS CountOfsignoff
    FROM Prod
    GROUP BY Prod.signoff)

     


    • Proposed as answer by Bruce Song Thursday, January 20, 2011 8:05 AM
    • Marked as answer by Bruce Song Wednesday, January 26, 2011 10:12 AM
    Tuesday, January 18, 2011 9:53 PM

All replies

  • use your sql that creates the 136 records as the "from" statement wrapped in () and count () for one of the fields in that query the result will be 136

    here is an example of an query that will do that

     

    SELECT Count(signoff) AS CountOfsignoff1
    FROM (SELECT Prod.signoff, Count(Prod.signoff) AS CountOfsignoff
    FROM Prod
    GROUP BY Prod.signoff)

     


    • Proposed as answer by Bruce Song Thursday, January 20, 2011 8:05 AM
    • Marked as answer by Bruce Song Wednesday, January 26, 2011 10:12 AM
    Tuesday, January 18, 2011 9:53 PM
  • Hello WoodyBZippin.

    Have you tried to save your summary query in your database and run the DCount function  against the saved summary query (not just the SQL)?

    --
    Kind regards,
    Wolfgang.

    Wednesday, January 19, 2011 1:10 AM