Prev: Rank and row number in Access?
Next: Can't get my head around this query..help!
From: AJ on 13 Apr 2010 00:51
I have data to report on by area, priority and days past due (positive and
negative)
eg 0-6 7-14 15-28 etc

Newbee to Access, trying to create query by the above critera.
I can get the query to sort by 0-6 etc but how to count as well?

Your wisdom is much appreciated.
AJ
From: John W. Vinson on 13 Apr 2010 01:03
On Mon, 12 Apr 2010 21:51:01 -0700, AJ <AJ(a)discussions.microsoft.com> wrote:

>I have data to report on by area, priority and days past due (positive and
>negative)
>eg 0-6 7-14 15-28 etc
>
>Newbee to Access, trying to create query by the above critera.
>I can get the query to sort by 0-6 etc but how to count as well?
>
>Your wisdom is much appreciated.
>AJ

Could you explain the actual structure and contents of your table? Do you have
a *text string* such as "7-14"? If so it will sort after "15-28" because the
text string "7" comes after the text string "1".

Perhaps you could post the SQL view of your current query, and an example of
(at least the date part) of the data.
--

John W. Vinson [MVP]
From: KARL DEWEY on 13 Apr 2010 01:32
Use the Partition function.
Partition(number, start, stop, interval)

number Required. Whole number that you want to evaluate against the ranges.
start Required. Whole number that is the start of the overall range of
numbers. The number can't be less than 0.

stop Required. Whole number that is the end of the overall range of numbers.
The number can't be equal to or less than start.

interval Required. Whole number that specifies the size of the partitions
within the overall range of numbers (between start and stop).

--
Build a little, test a little.


"AJ" wrote:

> I have data to report on by area, priority and days past due (positive and
> negative)
> eg 0-6 7-14 15-28 etc
>
> Newbee to Access, trying to create query by the above critera.
> I can get the query to sort by 0-6 etc but how to count as well?
>
> Your wisdom is much appreciated.
> AJ
From: John Spencer on 13 Apr 2010 08:46
The Partition function may be a good solution, but only if you have regular
intervals. The OP had two 7 day intervals and one 14 day interval
specifically listed.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

KARL DEWEY wrote:
> Use the Partition function.
> Partition(number, start, stop, interval)
>
> number Required. Whole number that you want to evaluate against the ranges.
> start Required. Whole number that is the start of the overall range of
> numbers. The number can't be less than 0.
>
> stop Required. Whole number that is the end of the overall range of numbers.
> The number can't be equal to or less than start.
>
> interval Required. Whole number that specifies the size of the partitions
> within the overall range of numbers (between start and stop).
>
From: KenSheridan via AccessMonster.com on 13 Apr 2010 13:53
Create another table:

OverdueRanges
….DaysFrom (Integer Number)
….DaysTo (Integer Number)
….OverdueRange (Text)


and fill it with rows like this:

-15 -28 -15 to -28
-7 -14 -7 to-14
-1 -6 -1 to -6
0 6 0 to 6
7 14 7 to14
15 28 15 to 28

Then join this table to your main table like so:

SELECT Area, Priority, OverdueRange,
COUNT(*) AS OverdueCount
FROM MainTable, OverDueRanges
WHERE DATE()-MainTable.DueDate
BETWEEN OverdueRanges.DaysFrom
AND OverdueRanges.DaysTo
GROUP BY Area, Priority, OverdueRange;

Ken Sheridan
Stafford, England

AJ wrote:
>I have data to report on by area, priority and days past due (positive and
>negative)
>eg 0-6 7-14 15-28 etc
>
>Newbee to Access, trying to create query by the above critera.
>I can get the query to sort by 0-6 etc but how to count as well?
>
>Your wisdom is much appreciated.
>AJ

--
Message posted via http://www.accessmonster.com

 |  Next  |  Last
Pages: 1 2
Prev: Rank and row number in Access?
Next: Can't get my head around this query..help!