OK, I've been using FM Pro for about 2 months, and I've figured out quite a bit, now I've been given the task of creating a database that allows our employees to enter their daily billable hours by category, Series, & Episode. Then we need to be able to pull reports based on whichever set of criteria we want, primarily the total number of billable hours per series for each category, the total number of billable hours per series, per episode, etc... also to look at date ranges for each editor? etc...

Basically a complete Enterprise level Time Tracking Solution...

What I have now is a two table file, one for Employee Information, the other to enter their time. I have it set up so that all they have to do is enter their employee ID and it pulls up their name, title, etc. from the employee table...I have a value list set up for Series, Episode, & Edit Function. The table I'm using to enter their billable hours seems to work very well, it calculates daily hours based on time in / time out, etc.

The problem I'm running into is how to then pull the reports I need...I'm sure I've set this up without near the complexity needed, but I am at a loss for exactly what to do next.

The Series, Episode, Edit Function, & Edit Hours fields have 10 repetitions each...should I set up a third table for reporting that can go into the other table and pull information? How do I do that without having to create a new record each time I want to pull information? The other problem is that in my data entry table I have it setup to create a new record for each day? Thus forcing the employees to enter their time daily, which isn't a problem, but when I pull the information, it doesn't really see the entries seperately, it sees everything they did on that day as one record and doesn't seperate the information out.... AHHHHHHHHHHHHHHHHH!!!!!!!

HELP!!!!

Thanks for the post. You've certainly run up against one of the fundamental principals of database design-- reporting is where you see the limitations of your architecture.

By "architecture" I mean the arrangement of fields in to tables: how many tables you have and what kinds of records are in each.

Generally in a time tracking application like this you don't want the time entered into repeating fields or a day's record. Rather, you want each time entry to be its own record of which the day the time was logged is merely an attribute.

Thinking about reports makes this clearer. I'f I log time on two projects one day I need those two time entries to be separate records so that I can related one of them to Project X and another to Project Y. I can the print this time in a SubSummary report broken out by project or broken out by day. Much more flexible.

We have a free, unlocked time tracking template along these lines that you might want to checkout:

http://www.seedcode.com/cp-app/ste_cat/CCTime

Hope that helps.

John