How to delete rows which counted smaller

Posted on 2011-05-04
Medium Priority
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
  • 2
  • 2
LVL 41

Expert Comment

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

Accepted Solution

peter57r earned 2000 total points
ID: 35696193
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

ID: 35696520
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

ID: 35696870
Datepart  will not give you year and month, only month.

Author Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 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