Need Help with database and calculations

[insightlowellb]

Hi,

I am a hoping someone can help me figure something out.

I have been using filemaker for sometime. I am just not good with advanced programming or calculations. I'm more of a designer than a programmer.

I am building a database to keep track of staff time spent on projects. The database has been created to help me keep track of clients time spent on task, expenses associated with a client and task. Also, I am creating a way to build a budget estimating costs for an assignment - in which I want to view actual costs based on estimated costs.

I am having trouble with one component where I calculate a staffs hourly rate against time worked on a task. The problem is that the staffs rate is not constant. A staffs rate may be different for different "assignments" but all "tasks" within an assignment are billed at the same rate.

I don't know how to pull a given staffs rate when they complete their timesheet entering time worked on an assignments task.

The database has the following tables:

- Staff (contains list of staff names and contact info)

- Clients (contains list of clients)

- Assignments - Fields: assignment name, company (pulled from client) and some other date related info

- Tasks - Tasks are grouped by assignment - Fields: task name, assignment, date started, date due

- Timesheet - fields: Date, staff, client, Type(type of work), hours worked, assignment, Staff Rate [color:red](THIS IS WHAT I NEED HELP WITH), and total billable

- Expenses - staff, date, client, amount, gst rate, gst, total, receipt number, description

- Budget - I have fields for each of 5 staff, (staff 1 - 5), fields for staff rate (staff rate 1 - 5), assignment, I have a portal here showing all tasks for the given assignment.

I am currently setting each consultants rate within the budget table. I guess this could be done at the assignment table.

I just don't know how to pull/calculate the staff rate for a given task, based on the assignment.

Please provide help if you can.

Thanks very much,

Lowell Brown

[email protected]

[Fenton]

I can see a couple ways to do this.

1. Have a Staff_Assignments table where you need a record for every Staff-Assignment combo.

2. Use a percentage. Then you only need a % in Staff, and a rate for each Assignment. This is a simpler structure, but you humans are not rational entities.

I think more information is needed in order to make a decision. It seems to me that you are assigning staff only at the very last level of Timesheet. You will need more than that if you want to lookup from a table of staff rates/assignment.

[insightlowellb]

Hi,

Not sure if that will work.

Can I email you directly to send you other info? or can I send you a link to show you how I have things organized for this database?

[Fenton]

You can create and send me a Private Topic post, by clicking on my name, and starting a private topic. Then however you will be depending on my having time to review your file.

You know, I just realized that I didn't really catch your "Budget" table. That would be the "staff-assignment" table. Where you've gone wrong is having 5 fields for the staff. You need to have only 1 field. You'd need a new record for each staff-assignment combo. Using 5 fields is poor relational design, and is why it has become a pain to look up that data from elsewhere.