how to check the count at the end of each month

[blyssz]

Hello All,

I have library books database in MS Access and people borrow books and submit them. I want to see the count i.e
Total # of books borrowed at the end of each month from the past 6 months and
Total # of books submitted at the end of the month.
How should I write the query to check the # of books at the end date of each month.
I am using the following query:
Select count(status) where status =”Borrowed” and Borrowdate=………
Any suggestion would be appreciated.

Thanks
Blyssz

 

[HarleyQuinn]

If you could show us the layout of your table and the fields in it that would be helpful.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[blyssz]

In the table I have Status field, BorrowedDate, SubmitDate and several other fields like BookName, BookType etc.

I want to count the # of books borrowed at the end of each month for the past 6 month and no. of bools submitted at the end of each month for the past 6 month and I want to do that every month. So In januray I want to see the trend for 07/30/2008 , 08/30, 2008 and so on till 12/30/2008.
In Feburary next monthe, I want to check the count from 08/30/2008 to 01/30/2009.
So I need a function for the date for each month.

Thnaks,
Blyssz

 

[PWise]

How about this

Select Month(BorrowedDate),Sum(IIf(Status="Borrowed",1,0)) As Borrowed,
Sum(IIf(Status="submitted" ,1,0)) As submitted
From TableName
Group by Month(BorrowedDate)
Where Month(BorrowedDate)<>Month(SubmitDate)
And Year(BorrowedDate)<>Year(SubmitDate)
where BorrowedDate between dateadd("m",-7,date()) and dateadd("m",-1,date())

 

[blyssz]

Thanks a lot!!

Blyssz