Prev: Rank and row number in Access?
Next: Can't get my head around this query..help!
From: AJ on 13 Apr 2010 23:19
Thanks everyone for your ideas, I will go and try them now.

My table is imported from Excel it contains alot of data, I am only
interested in the 3 columns for Area, Inspections and Priority

- Area which contains 4 locations plus a combined summary for the region.
- Inspections of assets are cycled and fall into two main areas of overdue
by X number of days or coming due in X number of days. Both are in sets of
0-6 or 7-14 or 15-28, 29-84 and 85 days & over

The Priority for the inspections is Critical, Significant and Other.
I need totals for each category for each area by the inspections over due
and coming due.

What I am aiming for looks sort of like this below when I have create it in
Excel but its such a cumbersome process extracting data and putting
conditional formating on to identify the inspection time periods, I was
hoping Access would streamline it for me as I have to pull this report every
month.

Area combined Priority1 0-6 or 7-14 or 15-28, 29-84 and 85 days & over
Area combined Priority 2 0-6 or 7-14 or 15-28, 29-84 and 85 days & over
Area combined Priority 3 0-6 or 7-14 or 15-28, 29-84 and 85 days & over
Total 0-6 or 7-14 or 15-28, 29-84 and 85
days & over

Area 1 as above
Area 1
Area 1
Total

Area 2 as above
Area 2
Area 2
Total

Area 3 as above
Area 3
Area 3
Total

Area 4 as above
Area 4
Area 4
Total



"John W. Vinson" wrote:

> 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: AJ on 13 Apr 2010 23:44
I dont think I have done this partition thing right, its spitting the dummy...

SELECT DISTINCTROW Data.[Provisioning Centre - Managed By], Data.[Orig
Priority], Data.[Days Past Compliant], Count(Data.[Days Past Compliant]) AS
[CountOfDays Past Compliant], Count(Data.[Days Past Compliant]) AS
[CountOfDays Past Compliant1], Count(Data.[Days Past Compliant]) AS
[CountOfDays Past Compliant2], Count(Data.[Days Past Compliant]) AS
[CountOfDays Past Compliant3], Count(Data.[Days Past Compliant]) AS
[CountOfDays Past Compliant4]
FROM Data
GROUP BY Data.[Provisioning Centre - Managed By], Data.[Orig Priority],
Data.[Days Past Compliant]
HAVING (((Data.[Provisioning Centre - Managed By])="Cootamundra Provisioning
Centre") AND ((Count(Data.[Days Past Compliant]))="0: 6") AND
((Count(Data.[Days Past Compliant]))="7: 14") AND ((Count(Data.[Days Past
Compliant]))="15: 28") AND ((Count(Data.[Days Past Compliant]))="29: 84") AND
((Count(Data.[Days Past Compliant]))="85: 2000"))
ORDER BY Data.[Provisioning Centre - Managed By], Data.[Orig Priority];


"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).
>
> --
> 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
First  |  Prev  | 
Pages: 1 2
Prev: Rank and row number in Access?
Next: Can't get my head around this query..help!