How to delete rows which counted smaller

Posted on 2011-05-04
Last Modified: 2012-05-11
Hi Guys,

I have a table with Date field included two month. My question is how can I create a query to count the rows of each month and delete the one has counted to a smaller value.

Thank you for your support.
Question by:DienDaiCa
    LVL 40

    Expert Comment

    Can you post some sample data with expected result?
    LVL 77

    Accepted Solution

    You will need more than one query to do that.

    To get the month with the lowest rowcount..

    Select top 1 myMonth from
    (Select format(datefield, "yyyymm") as mymonth, Count(datefield) as Kount
    From tablename
    Group By  format(datefield, "yyyymm")
    Order By Count(datefield) Asc

    Save as qryTop

    Then create your delete query..

    Delete * from tablename where format(datefield, "yyyymm") in (Select mymonth from qrytop)

    Author Comment

    Because the Attendance table has counted through the beginning of the next month (the working time extend to new day of next month) so I need to delete the data of new month (details in Dated field)
    @Sharath_123: Please check the following attached
    @peter57r: I just follow your instruction but I would like to replace the format by Datepart command. Please show me this way. And one thing that I concerned is what happen with the last month of the year time range ?

    LVL 77

    Expert Comment

    Datepart  will not give you year and month, only month.

    Author Comment

    That is all that I need. Cause' I want to count the date of the month only.
    Thank you for your support.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now