need help setting up database

[zaps]

Hi there,

I have 2 tables in my database, imported from excel. I work for a call
centre. One table has a list of all the calls we took in a month.
Information as Customer Name, Date they called, service provided, and the
name of the phone operator. The second table has a list of the complaints
for the month. Information like what the complaint was about, date, the
phone operator's name who the complaint is against etc.

I would like to make a report, showing the percentage of complaints a call
operator gets. How many complaints they get divided by the total number of
calls they took a month.

Any ideas how i should start?
Any help would be great!

Thanks,
Michelle

 

[Norman Yuan]

Based on what you described, the two tables imported from Excel to you so
called database is useless. You need realy to satrt from thoroughly analyse
the data model and design a properly normalized database first. In the case
as you described, two tables are definitely not enough.

For example, each customer may may request one or more type of services, for
each service customer ordered, he may complaim one or more times. Also, a
customer may be served by one or more operators for the same service...and
so on. So, you may need: a customer table, a operator table, a services
table (available services), service order ed table, complaint table...
Proper relationships between these tables should be created with tables
being properly normalized. You only import previous data (in Excel format or
other format ) after the database design is done and you may have to develop
a tool to pull data from Excel sheet into different tables respectively,
'cause data in Excel sheet, as you described, are not normalized.

If you are not able to design database appropriately, find someone who knows
it before you rush to build report on top the data.

 

[John Boatner, MCSD, MCDBA, MCSE, MCSA, MCDST]

Please excuse Norman - his lack of manners is only o'ershadow'd by his
ability to overcomplicate things.

While he has a valid point about database normalization, an argument could
be made that the two tables you described would suffice for your report.

You can make queries that represent certain portions of your data. For
example, you could create a "Group By" query (using the Sigma ?, or Totals
button) to group on Operator, and Count of Operator to get the number of
calls handled for each operator. Then, create another query that does the
same thing from the complaints table. Finally, create a third query that
joins the two previous queries by operator, include the operator name, and
then use an expression (e.g. [CountofComplaints]/[CountofOperator] as
PercentageOfComplaints)

While this advice may make little sense at first, once you start looking at
how the queries work, and the power they hold, you will learn quickly the
techniques to bring data together for reporting.

Be sure to read you help file, and try some of the examples that are there.

--

Regards,

John Boatner
MCSD, MCDBA, MCSE, MCSA, MCDST
J2 Technologies, Inc.
http://www.j2tech.com