Count Within a Range

[Guest]

I have a query where I have counted the number of records which have appeard
on a report, i.e, 490 records have been on the report 2 days, 34 records on
the report 3 days, etc. How can I sum the count of records so that I have
number of records which have been on the report between 2 and 11 days,
between 12 and 20 days, etc.

Thank you for your help.

 

[Guest]

Danu,

It would help if we knew your data structure (table and field names) and how
you determine whether a record has been "on the report" for a particular
number of days. I'll assume you have a table that contains some date
(ReportDate) and the way you determine whether it is "on the report" for a
period of time is to determine the number of days difference between todays
date and the [ReportDate] field. I'll assume that there is some other field
[CompletionDate] that you use to take the record "off the report".

You need to create another table (tbl_ReportRanges) to do what you want to
do in a query. This table should contain fields: (RangeName - text,
Start-integer, End-Integer) which might look like:

RangeName Start End
"Less than 2" 0 2
"2 - 11" 2 12
"12- 20" 12 21

Then you would write your query something like:

SELECT RangeName, Count(YourTable.ID) as RecordsInRange
FROM tbl_ReportRanges, yourTable
WHERE yourTable.CompletionDate IS NULL
AND DateDiff("d", [ReportDate], Date()) >= tbl_ReportRanges.Start
AND DateDiff("d", [ReportDate], Date()) < tbl_ReportRanges.End

HTH
Dale

 

[Guest]

Hi, Dale -

The table is nothing as sophisticated as you wrote. Each day the same data
is gathered from different sources and then compared to the same report ran
the day before. If the account number appears on yesterday's report then the
"Age" field, which is a number, is updated by one. No date fields are used.

Could I still use your idea of a different table to pull the Age field in
and then group by start and end numbers?

Danu,

It would help if we knew your data structure (table and field names) and how
you determine whether a record has been "on the report" for a particular
number of days. I'll assume you have a table that contains some date
(ReportDate) and the way you determine whether it is "on the report" for a
period of time is to determine the number of days difference between todays
date and the [ReportDate] field. I'll assume that there is some other field
[CompletionDate] that you use to take the record "off the report".

You need to create another table (tbl_ReportRanges) to do what you want to
do in a query. This table should contain fields: (RangeName - text,
Start-integer, End-Integer) which might look like:

RangeName Start End
"Less than 2" 0 2
"2 - 11" 2 12
"12- 20" 12 21

Then you would write your query something like:

SELECT RangeName, Count(YourTable.ID) as RecordsInRange
FROM tbl_ReportRanges, yourTable
WHERE yourTable.CompletionDate IS NULL
AND DateDiff("d", [ReportDate], Date()) >= tbl_ReportRanges.Start
AND DateDiff("d", [ReportDate], Date()) < tbl_ReportRanges.End

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

I have a query where I have counted the number of records which have appeard
on a report, i.e, 490 records have been on the report 2 days, 34 records on
the report 3 days, etc. How can I sum the count of records so that I have
number of records which have been on the report between 2 and 11 days,
between 12 and 20 days, etc.

Thank you for your help.