Counting Records by Date Range

[ccieszyn]

Hi there,

I'm trying to create a sort of 'reports' view for my database showing counts of certain records based on various modification dates. Right now I'm recording dates and times opened, canceled, and completed. Where I'm getting stumped is in figuring out how to count, say, the records containing open dates within the past 7 days, 30 days, and six months? If someone could help me figure out one, I'm sure I could do the rest.

I've looked at the posts in this forum, but don't see one that seems to solve the same question. If that's not the case, though, please point me in the right direction.

Thanks so much for any help!

[LaRetta]

Enter Find Mode [ uncheck pause ]

Set Field [ OpeningDate ; Get ( CurrentDate) - 7 & ".." & Get (CurrentDate) ]

Perform Find []

The found set count is your result. You can display the result by creating an unstored calculation (number) with: Get ( FoundCount ).

If you need the counts placed on a layout as fields, then creating another table occurrence will work also. You can use a global and filter your relationship based upon the range between this date and the current date (or use calculations). It would help to know more about your process and display needs and whether this display will be used within the same table (or another related table). There are many ways to achieve the results you wish.

You will also need to be clear on what 30 days and 6 months represents. Do you want exactly 30 days prior to whatever is the current date or do you want full-month increments, ie, the complete month which just passed (according to the current date)? Here is an example of using table occurrences to filter your ranges. There is actually a better method using repetitions but I would need to understand more of your needs. Do you require a columnar display of your counts? We can't even direct you to a thread if we don't know what you need.

LaRetta :wink2:

dateCount.zip

Edited July 23, 2006 by Guest
Added attachment