Advice on Capturing Dates

[databasedonr]

Greetings,

I am building a database to track/log calls, and I need to collect daily statistics. I am looking/hoping for some insightful advice on how best to capture the daily stats.

I am collecting information on three categories (calls, correspondence and on-call duty) for 5 days of the week. I have conceived of a table structure:

Calls
CallID (PK)
MemberID (FK to person taking calls)
WeekID (FK to date table)
DayID(FK to Day Table - lookup MTWThF)
CallsIn (number)
CallsOut(number)

and the same essential structure for the other two tables (correspondence and on-call duty)

Member table is "operator" taking the call and includes their tombstone data.
Week table includes week start date and end date, so I can do weekly stats, roll up to monthly and annually.

To the dozens of you more skilled than I, does this make sense?

I appreciate your input

 

[Vassago]

We could help with any specific problems you may have, but I don't think too many of us understand what you are trying to ask. How do you wish to determine the statistics?

 

[Cosmos75]

Calls
CallID (PK)
MemberID (FK to person taking calls)
WeekID (FK to date table)
DayID(FK to Day Table - lookup MTWThF)
CallsIn (number)
CallsOut(number)

...

Week table includes week start date and end date, so I can do weekly stats, roll up to monthly and annually.

How are
CallsIn (number)
CallsOut(number)
being populated?

Are your "weekly stats, roll up to monthly and annually" = Stats of CallsIn and CallsOut??

 

[databasedonr]

Clarifying

Sorry, I was trying to be clear ... failed.

I am collecting statistics on calls. The statistics are collected daily, but reported weekly. That is, I may receive 7 calls on Monday, 15 Tuesday and so on, and on Friday afternoon I turn in my numbers to an admin person who updates the database.

In the database, I want to see how many calls we had on Monday, how many on Tuesday, etc., the total for the week, the total for the month, and so on.

I have thus built a database where I have a Date table that has the start and end date of the week. I have a Day table that has fields for each of the seven days. I have a relationship between the operator answering the call and the Date and Day tables, so I can both record the number of calls per Day and retrieve the number of calls per day, week and month through queries.

Does this clarify? What I am wondering: is this the best way to organize the tables? See my original post, and you'll hopefully get the sense of how I have created the call table. I want to ensure I have ease of use for data entry. Once the numbers (and that's all I am collecting -- how many calls received and made) are entered, I neeed to be able to report on the total numbers.

As we collect the numbers, I have seven or eight people taking calls, so I plan to enter the calls by user, so I can also see who is taking the most calls, but I am also mostly concerned about rolling up the data to the number of calls per week and per month as a global total, not for the individual operators.

I hope this is more clear. Once again, feedback is appreciated.

 

[Cosmos75]

I would get rid of the DayID and Day table and just have a date field in your Calls table. I know it cumbersome to add the same date for many Members but you can use an update query to add records for you for one date.

Do you really need the Week Table? If so, I would suggest just have a date field and only enter the last day of the week ad a Week (You may want to use another name I think Week is a reserved word in VBA) so you can use date functions.

 

[databasedonr]

To Cosmos Questions

CallsIn (number) and CallsOut(number) are data entered from call log sheets.

The statistics are purely sums of the CallsIn and CallsOut, so I can see how many calls the operators are processing.

By collecting the data by Day in a separate Day table, am I making a) data collection more difficult, b) data reporting more difficult?

Thanks,

 

[Vassago]

I would recommend just storing the date of the call, instead of the start day and end day of the week. Then you can organize your queries to separate the days by week/month/year or whatever the user is looking for. There are ways for Access to recognize the weeks, using modules. I have a sample if you need it. This might clean up your code a little and save you a step in storing the week dates on your table.

 

[databasedonr]

So, I was trying to recreate the manual system.... you suggest not doing that, and just enter the date.

In the data entry process, I have a log sheet for each operator, and enter in the number of calls received (CallsIn) and made (CallsOut). If I have use the week start/end date, then I enter the date once for 98 entries (7 operators, 7 days/wk. x two types of calls) for one table, and I have three tables like this (Calls, Correspondence and OnCall_Duty), so I can either enter the date once in a Week table, or enter the date 294 times.

Thus my dilemma - and trying to keep off the horns of said dilemma.

 

[Cosmos75]

Did that help enough? If not, post back and we'll (may be speaking out of turn here on Vassago's behalf ) try to help.

 

[databasedonr]

I'm not sure -- the input has been great, and thanks to everyone who responded. Just clarifying this for the forum has helped me thinking this through (not to mention staring at it all day!).

Where I am now is still trying to minimize data entry. I have an operator table, a couple of "type" lookup tables, and my main tracking table, and my tblWeek with my week start and end times.

Because I don't really care about what day the records were on, other than that I have them for the week, I am persisting with this (at least until the week-end). And, as all I am doing is capturing a number (representing the number of calls) I am doing this:

tblTracking
trackingID - PK
weekID - FK (to my week start and end)
OperatorID - Fk (to the person taking the call)
CallTypeID - FK to lookup for Call Type - Inbound, Outbound, etc.
CorrespondenceTypeID - FK to lookup for letter types
Monday (number)
Tuesday (number)
Wednesday (number)
Thursday (number)
Friday (number)
Saturday (number)
Sunday (number)

So, now you call tell me that I am crazy, and that's okay, 'cause I may well be. I hope that I was able to convey the nature of what I am trying to do, so that all readers can appreciate that I don't want to put literally hundreds of days per week of entry into my table. The way I have it now, I have built a form that I can drop in my operator, type of call, and then number of calls responded to over the course of the week.

What I am still hoping for from the forum is
- some validation that this is a decent way to do it
- warnings if any that I am about to get into hot (or at least tepid) water in the future when I have loads of data and want to report on it.

Any feedback, good, bad, suggestions, warnings are really welcome.

Thanks again to all for the opportunity to share my madness.

databasedonr

 

[Cosmos75]

tblTracking
trackingID - PK
weekID - FK (to my week start and end)
OperatorID - Fk (to the person taking the call)
CallTypeID - FK to lookup for Call Type - Inbound, Outbound, etc.
CorrespondenceTypeID - FK to lookup for letter types
Monday (number)
Tuesday (number)
Wednesday (number)
Thursday (number)
Friday (number)
Saturday (number)
Sunday (number)

I strongly suggest you don't do this. It can cause problem later one if you decide you want to extract info differently.

tblTracking
trackingID - PK
OperatorID - Fk (to the person taking the call)
CallTypeID - FK to lookup for Call Type - Inbound, Outbound, etc.
CorrespondenceTypeID - FK to lookup for letter types
Date - Date (Short)
Calls - Number

You could have a form where you enter the date for last day in the week (Fri or Sat) and have an append query to add records for the date entered and the 6 days prior to that date entered (probably require some VBA but not sure without trying it out myself)

This way you can query a particular date or date range.

If you REALLY need to include Week field, go ahead and add a table and include it as a FK in your tblTracking.
tblWeek
EndOfWeek - Date (Short) : Only the date of the last day of the week.

The only advantage to doing this is that you can create a form to select a certain week and you can have a form to show records that are Between [EndOfWeek] and ([EndOfWeek]-6). And things like that.

Keep in mind that you will have to continued adding to the tblWeek unless you come up with code to do that automatically.

 

[Cosmos75]

Example DB

This might give you a head start. Lemme know if you need more help understanding it.


Access97 Version. Let me know if you prefer Access 2000.

 

[databasedonr]

Thanks, Cosmos75! That's pretty cool, and it just might work.

I am working in Access2000, but that's not a big deal.

I am going to spend some time with this and see if it will meet my end-user needs -- I am actually creating totals from input forms (paper based) and entering the totals, so I will need to amend the append ....

Thanks again -- this has potential! Appreciate the advice and input.

 

[Vassago]

Thanks for throwing me into your sample database Cosmos! I feel like I helped some here!

 

[Cosmos75]

databasedonr,

No problem! The database should give you an idea of what you can do. Glad to be of help.


Keeping the tblWeek will allow you to create a criteria query useing to comboboxes tied to tblWeek. One can be a Start week and one can be the End Week. You can then use them as criteria in a query to select all tracking records between Start and End week.

Post back if you run into any problems and I'll try to help as best as I can. I like to know how people's databases are coming along especially if I've managed to help somewhat. Sometimes, I get to see my own designs take directions that I've never thought of or be improved upon. Helps me learn new things!


Vassago,
Well, I wanted to populate it with more than two people and you were on this thread.

 

[databasedonr]

Cosmos75 & Vassago:

Thanks a million for this advice. I paid heed, and reaped the dividends, as the solution is working perferctly. The only thing I did differently was capture the number of items to add in a parameter in the update query.

I really appreciate the feedback, because the reports on activity work perfectly as well. So thanks again. I appreciate learning at the feet of the masters. Thanks too to Access World Forums -- who knows where I would have ended up otherwise!

Greatfully,

databasedonr

 

[Cosmos75]

I really appreciate the feedback, because the reports on activity work perfectly as well. So thanks again. I appreciate learning at the feet of the masters. Thanks too to Access World Forums -- who knows where I would have ended up otherwise!

Can't speack for Vassago but I wouldn't call myself a master, or even an experienced Access person. I've a LONG way to go. Still learning...


But, nonetheless, I am glad to have been of help.