complicated query question

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

Ok all here's a complicated one for you all (i think)!!!

I have a table as follows:-

Date
Skill - three digit numbers
Time - time in half hour intervals
Calls - the number of calls received for each skill at
each time interval on a particular date.

I need to get call totals for 3 ranges of skills:-

ie for each date + time interval i need
1. the sum of calls for skills 103-110
2. the sum of calls for skills 113-120
3. the sum of calls for skills 123-133

This info all needs to be in one query (i think) as i need
to base a report on the info.

The report needs to be based on a particular date showing
1. time interval down left hand side of report
2. 3 columns showing sum of calls as above for each time
interval.

I hope this makes sense and would be grateful for any help.

REgards
Michelle
 
Michelle,

This can be done with a Crosstab Query. In the query, you can add a
calculated field as a grouping field, like this...
SkillGroup: Switch([Skill] Between 103 And 110,1,[Skill] Between 113
And 120,2,[Skill] Between 123 And 133,3)

You need to Group By the time interval, Group By the SkillGroup, and
Sum the Calls.

The SQL view of the Query will look something like this...
TRANSFORM Sum(Calls) AS TotalCalls
SELECT TimeInterval
FROM NameOfYourTable
GROUP BY TimeInterval
PIVOT Switch([Skill] Between 103 And 110,1,[Skill] Between 113 And
120,2,[Skill] Between 123 And 133,3)

I am not sure of the nature of the data in the TimeInterval field
(note: it is not a good idea to name a field Time - this is a Reserved
Word (i.e. has a special meaning) in Access, and therefore should not
be used as the name of a field or control or object). Also, I have
assumed the Skill data is number data type.

- Steve Schapel, Microsoft Access MVP
 
Ok all here's a complicated one for you all (i think)!!!

I have a table as follows:-

Date
Skill - three digit numbers
Time - time in half hour intervals
Calls - the number of calls received for each skill at
each time interval on a particular date.

I need to get call totals for 3 ranges of skills:-

ie for each date + time interval i need
1. the sum of calls for skills 103-110
2. the sum of calls for skills 113-120
3. the sum of calls for skills 123-133
Click to expand...

Put three calculated fields in the Query defined as:

SkillSet1: IIF([Skills] >= 103 AND [Skills] <= 110, [Calls], 0)
SkillSet2: IIF([Skills] >= 113 AND [Skills] <= 120, [Calls], 0)
SkillSet3: IIF([Skills] >= 123 AND [Skills] <= 133, [Calls], 0)
This info all needs to be in one query (i think) as i need
to base a report on the info.

The report needs to be based on a particular date showing
1. time interval down left hand side of report
2. 3 columns showing sum of calls as above for each time
interval.
Click to expand...

Then create a Totals query Grouping By the date and time, summing the
appropriate SkillSetn values.
 
Back
Top