DB Statistics in an Admin Window

[Tony Martin]

Hi everyone.

Need help trying to figure out the best way to create and lay out a window that allows the administrator to search on a particular zip code and sale date of a list of homes and have it show various statistics for each entity who has work to do on those properties.

I have several entities who work on each property. Miners, Drivers and evaluators. There are 5 Miners, 10 drivers and 10 evaluators.

I have three fields across each property record - a text field for MINER, DRIVER and EVAL where each record is assigned to one of each (in no particular order). In other words, the MINER field for property record 1 might be assigned to MINER01, DRIVER03 and EVAL06. The admin person chooses who gets assigned each property (not that it matters for the purpose of this question).

When the entity completes the property, its status changes using a commit record button. There's a text field where we "move" the status from MINER to DRIVER, DRIVER to EVAL, etc.

The things we need to know in this "Administrator" window for each MINER, DRIVER, and EVAL person include:

1) Properties Remaining to complete

2) Total properties assigned

3) Hours Logged on (this will be an entry field for now but I'd like to make this an auto calculation of the total time logged in

4) Properties completed

5) Properties per hour

For reference, here's what the company manager said they needed and the logic behind it:

"Properties Reviewed Note: This function is needed to keep track of the entire process to ensure that we are completing reviews of all properties in the system. Essentially this function takes the difference between the total files found within the selected data range less the total files completed by each level within the system. For example if I searched the sale date June 22 the boxes should show how may files are left to be reviewed for that date and the total % left to be completed. The lower the number and % = a greater review of all properties in the foreclosure system within the date range selected."

"Properties Remaining Note: This box, for each of the Miners, Drivers and Evaluators, assists the Office Manager / Admin in evaluating the performance and progress of each person. Upon selecting a date range this cell searchs for all files completed within each zip allocated in the Zip Allocation Table. The result shows how many properties each person has remaining and the respenctive % left (of the allocated zips) left to be completed. The lower the number and %, the higher the performance."

=====

Any ideas as to the best way to go about this?

Some questions that have come across my mind are:

1) Can this all be done in the current table?

and if not...

2) Do I need a separate table to hold these statistics for each entity? If so, whats the best way to set that up?

Your help would be appreciated! :bang:

[jamesducker]

You should at least start with a table that contains all the 'staff', ie one record per miner, one record per driver, one record per evaluator.

Start with three fields:

UniqueID ----- Identifier ------- Type

1 ----------- Miner01 -------- Miner

2 ----------- Miner02 -------- Miner

3 ----------- Miner03 -------- Miner

4 ----------- Driver01 -------- Driver

5 ----------- Driver02 -------- Driver

6 ----------- Driver03 -------- Driver

7 ----------- Eval01 --------- Eval

8 ----------- Eval02 --------- Eval

9 ----------- Eval03 --------- Eval

Create a value list comprising all the values from field staff::uniqueID, second field being staff::identifier. Show values only from second field. Use this value list for your three drop-downs. Create a new test record on the homes table and set the three fields.

Then create a relationship between the STAFF and HOMES tables, from staff::uniqueID to homes::miner.

On the staff page create a portal that lists records from the HOMES relationship. This portal will list all the homes for which the given staff member is the miner.

Once you have got this far you can create similar relationships to show all the properties for which the given staff member is eval or driver. You can do a count(homes::UniqueID) to arrive at some of the statistics you are looking for.

Hope that helps.