select and count

  • Thread starter Thread starter AJ
  • Start date Start date
A

AJ

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
 
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
Click to expand...

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.
 
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).
 
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
 
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
 
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];
 
Back
Top