• Status: Solved
• Priority: Medium
• Security: Public
• Views: 363

# Counting dates

I have a database where I dump dates.  I need to get a count of how many dates that was dump that fall between date’s ranges.  Please note! I will need to be able to enter the date range
If I enter start date at 8/15/2012 through  8/31/2012 and the dates I dumped is through 9/5/12, I need the count of days that are less than 0-3 days, 4-7 days, 8-14 days, 15-28 days, 28+Days, that only falls within the date range 8/15 – 8/31/2012.
0
Delores_C
• 6
• 4
• 2
• +1
1 Solution

Commented:
"less than 0-3 days, 4-7 days, 8-14 days, 15-28 days"

which two dates are being used to get the difference?
0

Commented:
Without knowing a little more about you data structure and exactly what you are trying to do, this is just a stab in the dark.  I usually do this by creating a Range table (tbl_Ranges)

GTE     LT     Desc
0          4      Less than 4 days
4          8      4-7 days
8         15     8-14 days
15       29     15-28 days
29    NULL   Greater than 28 days

Then I create my query with a syntax that looks something like:

SELECT tbl_Ranges.Desc
, SUM(IIF(DateDiff("d", yourTable.DateField, Date()) >= tbl_Ranges.GTE AND
DateDiff("d", yourTable.DateField, Date()) < NZ(tblRanges.LT, 1000), 1, 0)) as Records
FROM tbl_Ranges, yourTable
GROUP BY tbl_Ranges.Desc
ORDER BY tbl_Ranges.GTE
0

Author Commented:
Clarification:  This is the existing fields

1.  Location = SCR
2.  DumpDates = 8/12

There are multiple locations, however, there are multiple dates for a location, in many cases, you may have the same date multiple times..   I need to take a location and determine based on a DumpDates range, how many of each of criteria there are for a location.

I need a prompt ask the date, then use the date I enter to determine the count of each criteria
0

Commented:
Can you provide some sample data?

1.  Create a new database
2.  Import this table into the new database
3.  Delete any proprietary or personal data from the table.
4.  Delete all but about 30-40 records from the table
5.  Compact and Repair
6.  For those remaining records, figure out what the results you expect to see should look like.
7.  It would be helpful if you could put these records in a spreadsheet and annotate in one of the columns which category the record should fall into and why.
8.  Attach the sample data and spreadsheet files to the EE post.
0

Author Commented:
I got it to ask for date, however, I need it to sum the DataCount fields that meet the criteria of 0-3, 7-14, etc. File attached
Cynthia.accdb
0

Commented:
Something like this?
Cynthia.accdb
0

Author Commented:
I needed it to ask for a date to count from, It prompts for a date, however, when I enter a date (lets say 9/10/12), I am getting a negative number count, and it is not counting in the group.  I need all numbers to be counting in the group at some level.  I am attaching what I have done up to this point.
Cynthia.accdb
0

Commented:
I don't see any negative numbers when I use 9/10/12.  Now if I enter 8/10/12, there are negative numbers but where did you want the negative numbers to be grouped with?  In SCR, for example, 8/1 to 8/10 gives you 9 days, which goes in the 8-14 day group.  On the other and, 8/15 to 8/10 gives you -5 days.  Did you want to ignore the negative number and stick it with the 4 to 7 day group?
0

Author Commented:
Yes, I need to count the number regardless if negative.
0

Author Commented:
I meant even if the number is a negatve 31 (-31) it would be count in the 28+ Days.  All of the numbers need to fall in a group, and the Count needs to be added in a group.  Thanks in advance.
0

Commented:
But you still want it to display as negative, right?  It just needs to be added to the groups as if they were positive numbers...
0

Commented:
See if this works for you.
Cynthia--1-.accdb
0

Author Commented:
Just what I needed.  Thanks
0

## Featured Post

• 6
• 4
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.