Hello,

I'm hoping for a kick in the pants here. I'm stuck in an old way of doing something I know is wrong -- using repeating fields.

The solution I am working on tracks caseworkers' activities on a daily/weekly/monthly, etc. basis. They complete "work plans" for the week where they track their activities: whom they visited, how long they were there, what the objective was, and the outcome. They may visit anywhere from 1 to 5 or 6 clients per day. In the past, I used repeating fields that would track "Monday Visits", "Monday Start", "Monday End", etc.. with 6 repetitions. I know this is a bad way to go, especially for reporting. I found that out the hard way.

I have an opportunity to repent for my evil ways, and I can't get past what I've already done. Should I have a separate table for "Visits" and have each one be a separate record? That seems right, but how would I do this for Monday through Friday in different weeks? Would I need to have a field that holds the (say) last Friday of that week and the CaseWorker ID?

Hope I'm making at least a little sense & that someone might have an idea or two.

Much obliged,

Elizabeth

I think three files would be appropriate: caseworker, client, appointment (join file between caseworker and client). The appointment record would include details of one meeting of one caseworker with one client. You could have a portal in the caseworker file showing all appointments and another portal showing all clients. In the client file you could have a portal showing all appointments. You could generate reports based on the appointment dates.

Here's a possible design: Assuming a WorkPlanWeek table and an WorkPlanLine table, where each WorkPlanWeek is one person's weekly work plan, and a WorkPlanLine is one work plan entry in a specific day & time:

WorkPlanWeek

WorkPlanWeekID

CaseWorkerID

Week#

gDayOfWeek

WorkPlanLine

WorkPlanWeekID

DayOfWeek

Activity Description

Time Start

Time End

Time Elapsed

The data entry relationship would be:

WorkPlanWeek::WorkPlanWeekID = WorkPlanLine::WorkPlanWeekID

AND WorkPlanWeek::gDayOfWeek = WorkPlanLine::DayOfWeek

This would give you a data entry layout for the current week showing WorkPlanLines for a selected day of the week. gDayOfWeek could be changed by selecting from a popup menu, or by clicking Next/Previous buttons.

Thanks Ender and transpower! I'll get to work on your suggestions today.