Solved

# Counting dates

Posted on 2012-09-10
352 Views
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
Question by:Delores_C

LVL 77

Expert Comment

"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

LVL 47

Expert Comment

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 Comment

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

LVL 47

Expert Comment

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 Comment

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

LVL 29

Expert Comment

Something like this?
Cynthia.accdb
0

Author Comment

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

LVL 29

Expert Comment

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 Comment

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

Author Comment

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

LVL 29

Expert Comment

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

LVL 29

Accepted Solution

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

Author Closing Comment

Just what I needed.  Thanks
0

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

### Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

#### 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.

#### Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!