db Design

[Ken L]

I am working on a project where multiple people need to keep track of how
many customers they serve on a daily basis. Each customer can be counted
only once and must go into only one of 15 categories.

What I have thought of so far is to have an individual table for each of my
customer service people with the same data rows in all tables. Each custsvc
rep would open the database, select their 'tab' and be able to tabulate their
data. each 'tab' displays the 15 different categories and a button to add
one to that category.

tblCustSvc1(ID, Date, Staff, Student, Vendor)
tblCustSvc2(ID, Date, Staff, Student, Vendor)
etc.

Is there a better way to do this?

 

[Duane Hookom]

Why not put all of your service tracking into a single table with all
customer service people? Add a field for their employee ID or whatever.

 

[Ken L]

so basically tblCustSvc(ID, Date, CustSvcNum, Staff, Student, Vendor)

so when they open the db form, they could choose their number, and then
enter their data. Would it matter if the page was in an open state for
hours, would the data be recorded when say student is incremented by 1?

thanks, Ken

 

[Duane Hookom]

I don't understand what you are trying to do. What do you mean by
incremented?

BTW: Date is the name of a function and should never be used as a field
name. Make this more descriptive by using something like:
SvcDate
ContactDate

 

[Ken L]

that is a good idea for the date field.

each customer service rep currently keeps a paper log. Each time a customer
comes in, a tick mark goes into the appropriate category, ie. student, staff,
vendor. We actually have 15 categories.

we can have multiple customer service reps working at one time, i need to be
able to distinguish between them. What I have managed so far, but is clumsy
is individual tables for each rep. The form has a button for each category
which when clicked increments that category by 1 (a tick).

my way is a very bulky method and I have yet to devise a method of
tabulating data by date for a) 1 rep b) all reps. I'd have to pull the data
from 10 seperate tables (number of reps).

does that help?

 

[Jeff Boyce]

Ken

Pardon my intrusion...

If you are saying you have a separate table for each of your 10 reps, you
have a well-designed ... spreadsheet!

If you are saying your form has one button per category, you have a ... (you
know!). ?!And one table per category?! (eek!)

You'll get to use a lot more of Access' capabilities if you design your data
structure more relationally, i.e., well-normalized. For example, if the
above does describe your current design, what will you do when you add a new
rep? a new category? ?!Modify everything?!

A table that holds reps, and a table that holds categories gives you a way
to add/subtract reps and categories without modifying anything.

Each one of your calls then has a row in a CallLog table that includes a
repID and a categoryID (these point back to the previously-mentioned
tables -- they're called "foreign keys"). Your CallLog table includes those
two (ID) fields, plus whatever other info you are keeping about the caller,
the time/date, the problem, the solution, ...

Now, you ask, how many calls have we gotten for category 9? A simple query
of the CallLog reveals the count of records where CategoryID = 9 (or 2, or
15, or, in the future, 23). And how many calls did rep#3 take on June 30th,
2005? A query by CallDate and RepID gives you that info.

Good luck!

Jeff Boyce
<Access MVP>