Total amount of hours left / used

[mosh]

Hello All,

I need to create a derived attributes (total hours left, total hours used).

In order for the calculation I have days_allocated in the employee table (this is converted into hours by multiplying this by 24).

How can I create a field in a query that works out the amount of hours used and left?

Here is a typical holiday record (screenshot of my query that I am trying to do)


Can someone please help me create a formula.

Thanks
________
Iolite vaporizer parts

 

[mosh]

anyone help?

Thanks
________
Art On The Hill

 

[namliam]

What you have looks OK to me??

What is wrong with that query?

 

[boblarson]

Well, for one thing, if you are showing all of the fields in your query you can get rid of the shift and manager tables as they aren't contributing to the query.

 

[Bat17]

what is wrong with what you have?

Peter

 

[boblarson]

Oh, and also probably change to this:

TotalHoursLeft: ([days_allocated]*24)-SUM(NZ([hours_booked],0))

The SUM part is that your hours booked is actually a "many" and could have multiple records tied to days allocated.

 

[mosh]

Hello all,

thanks for the replies,

The problem with what I have is it does not sum the total hours booked for one employee.

So for example there are two holiday records for one employee, one record he has taken 15 hours off and another record he has taken 7.5hours off, the current formula does not sum up the total hours taken to date.

The formula below doesn't seem to work

TotalHoursLeft: ([days_allocated]*24)-SUM(NZ([hours_booked],0))

I need it to calculate total hours taken for one employee (sum of all hours taken for one agent)

thanks for your help
________
Vaporizer Volcano

 

[Bat17]

you need to do it in two queries, the first summing the values in the holiday table the second joining the first with the employee table. you can do your calculations in this new query.

HTH

Peter

 

[mosh]

you need to do it in two queries, the first summing the values in the holiday table the second joining the first with the employee table. you can do your calculations in this new query.

HTH

Peter

Thanks for the reply, any help on the calculation field?

Thankss
________
Live sex webshows

 

[boblarson]

Put just the Holiday table in a query - pulling Payroll ID and HoursBooked.

Use the grouping button (looks like Greek Epsilon), and in the area that says Totals (you'll see the words GROUPBY by default in each column; change the one for hoursbooked to SUM.

Save that query an insert that query into another query and this time put the payroll table in as well and tie the payroll ID together using a Right Outer Join (select all records from Payroll ID and those records from the query where there is a match) that way you get all employees listed and those that have no hours booked will still show up but the fields will be null.

Then you can do up your formula in the way you want it (you won't need to use my SUM part of it though).

Then, you can

 

[mosh]

Thanks Bob,

Done it and works.

Iv got another question, The managers name is determined by a forename and surname, In the employee form the user has to select the manager for the employee. But I don't want the user having to select forename / surname on separate dropdown boxes.

What I have done is created a query that concatenates both forename/surname. Problem with this on the form is I do not know how to create a table value dropdown for this field.

Can anyone help?

Thanks
________
Prilosec classaction

 

[boblarson]

You're not selecting the person and then storing their NAME in the table are you? You shouldn't be doing that. You should be storing their primary key; that way if any spelling needs changing, or other corrections need to be made, then it can be updated in one place - the original employees table.

 

[mosh]

Hi Bob,

Not sure if you understand what I meant; Basically Because the Managers Name is in two parts, I would have liked the user to select the manager when adding a new employee, as it stands, the user has to enter a manager ID, or enter the name of the manager in a query field. What if the user doesn't know what the managers name is?
________
ZOLOFT SETTLEMENT INFORMATION

 

[boblarson]

Do you have the managers stored in a table? If so, their name should be stored in two parts in the table, something like this:
managerID - autonumber (primary Key)
managerFirstName - Text
managerLastName - Text

There can be other fields as well, but we're just concerned about those three.

So, you would have something like the attached sample (see attached).