Searching

[Guest]

I have a database with two tables:

Table (table nameata) contains 183 fields. (Field names: Name, 1, 2, 3, 4
...... 182) . These 182 fields will contain number of troubleshooting done on
each day by a technician from 1st September 2004 till date. Name field
contains names of the technicians. 1, 2, 3.... 183 contains number of
customer visits for troubleshooting (minimum visits = 0 maximum = no milit).

I have another table (table nameroductivity) with 10 fields (Field
names:Name, 25, 50, 75, 100, 125, 150, 175, 200 & Consistency).

A technician who is capable of doing 6 or more troubleshooting in a day are
the best performers. The names that I have in Data table are similar to what
I have in the Productivity table.

Now, I want to check if the first technician has done 6 or more
troubleshooting in any of the first 25 days (from 1 to 25; check this in the
Data table). If yes, put the count in field name 25 of the Productivity
table.Similar task should be done for the next 7 fields (till 200) of the
productivity table. After that check if that Technician has a value more than
ZERO in all the 8 fields of the Productivity table. If yes, put YES in the
consistency field. Once finished move to the next technician till EOF.

Shekhar

 

[David C. Holley]

First and foremost, what SPECIFIC information is being contained in each
of the 183 fields? If each field represents a single caller, your
database design needs to be changed.

Second, while there are instances where a summary table is helpful which
is what the PRODUCTIVITY table is essentially, it sounds as if that
isn't the case for your DB. The information that you're looking for can
be derived by running queries on the data. Its basically translates to
count the number of resolved tickets in the given space of time. What is
the exact defintion of 'the first 25 days', etc? Is it the tech's first
25 days with the company? Are you looking for tickets resolved within 25
days of being opened? Are you looking at tickets resolved in the last 25
days?

David H

 

[David C. Holley]

First and foremost, what SPECIFIC information is being contained in each
of the 183 fields? If the data is more or less homogenous(sp?), the data
should probably be located in a separate table. Doing so will GREATLY
ease working with the data and queries on the data (see below)...

Second, while there are instances where a summary table is helpful which
is what the PRODUCTIVITY table is essentially, it sounds as if that
isn't the case for your DB. The information that you're looking for can
be derived by running queries on the data. Its basically translates to
count the number of resolved tickets in the given space of time. What is
the exact defintion of 'the first 25 days', etc? Is it the tech's first
25 days with the company? Are you looking for tickets resolved within 25
days of being opened? Are you looking at tickets resolved in the last 25
days?

David H

 

[Guest]

First and foremost, what SPECIFIC information is being contained in each

of the 183 fields?

It contains the number of visits to the customer's place.

Eg. Rocky visited 6 customers on 1st Sep, 1 on 2nd, 3 on 3rd and so on.

Name 1 2 3 4 5 6 7
Rocky 6 1 3 3 0 0 5
Vicky 1 2 2 5 5 5 4
John 6 8 5 7 7 6 3

What is
the exact defintion of 'the first 25 days', etc? Is it the tech's first
25 days with the company? Are you looking for tickets resolved within 25
days of being opened? Are you looking at tickets resolved in the last 25
days?

The incentive structure is based on 25 days. If a ticket is opened on lets
say 1st Sep, it is resolved in that day itself. There are no pending tickets.
I want to check how many days in the first 25 days did Rocky resolved a
ticket (in the above example just once, John has resolved 6 or more tickets
in 5 days in the first 25 days slot).

Once first 25 days slot is over, the same thing starts again for the next 25
days. (26-50) and so on till 182nd day.

Shekhar

 

[Guest]

I think my reply overlapped yours. See the above post.

 

[David C. Holley]

Then you will want to change the database scheme to something to the
effect of...

tblTechs Info on the Tech's names, phones, etc.
tblCustomers Info on the Customer's names, address, phones,

etc.
tblTicketHeaders GENERAL info on the tickets - date opened, date
closed, description, assigned to which tech,
opened by which customer, description of
problem
tblTicketDetail Specific info on activity related to the ticket
performed
Date Action Tech
2/5/2005 Ticket Opened David H
2/5/2005 Called Customer David H
2/6/2005 Ordered Part David H
2/10/2005 Part Received David H
2/11/2005 Part Installed David H
2/12/2005 Ticket Closed David H

tblAction Specific actions performed

This will put the data into a scheme that will allow to better identify
who did what when and to do performance analysis. For example, if you
wanted to know performance in terms of ticket resolution you would do a
query on tblTicketHeader where you subtract DateOpened from DateClosed
to get the life span of the ticket and thus performance. Then by sorting
that query by tech and then by value you'd be able to see performance.

David H
(The Ticket Opened & Ticket Closed records in tblTicketDetail is would
be just for convience and not neccessary since the same information is
in the ticket header.)