Calculated Field (1 Viewer)

Smurf774 · Oct 26, 2016

Hi Everyone
Just wondering if you could possible help me out?

I have created a calculated field in a query.

Timeperday: [Time Basic Hours]+[Time OT Hrs]+[Time OT Hrs SAT]+[Time Double]

But this only adds up the values in field [Time OT Hrs SAT].

All fields are numeric, have the same format and a default value of 0.

I have tried various approaches but I am not getting anywhere with this.

The only thing that has come to mind, is that the other three fields existing in the original 2003 DB and [Time OT Hrs SAT] was added in the 2010 version.

Any suggestions?

sneuberg · Oct 26, 2016

Can you upload your database?

Smurf774 · Oct 26, 2016

Hi Steve
Unfortunately I can't (not allowed)!!

sneuberg · Oct 26, 2016

If you put the fields that are not working in the query as an expression what do you see, e.g.

TBH: [Time Basic Hours]

Smurf774 · Oct 26, 2016

Hi Steve
Just done that and the hours show correctly in that field...confused!

sneuberg · Oct 26, 2016

I'd try rebuild the expression using the expression builder, i.e., right click on the grid and click build. Also what happens if you just try part of the expression let's say:

Timeperday: [Time Basic Hours]+[Time OT Hrs]

also try compact and repair.

Smurf774 · Oct 26, 2016

Hi Steve
Just added one at a time the three fields [Time Basic Hours]+[Time OT Hrs]+[Time Double] worked but when I added [Time OT Hrs SAT] the field was blank except for those that have values in this field. so it seems I have a problem with field [Time OT Hrs SAT]. I will compact and repair to see if this rectifies the problem. -Thanks

plog · Oct 26, 2016

Timeperday: [Time Basic Hours]+[Time OT Hrs]+[Time OT Hrs SAT]+[Time Double]

You have a structure issue. You are storing values that should be data as field names. In all those fields, everything after 'Time' should be stored in a new field called 'TimeType'. For example, lets say you have this data:

TimeTable
EmployeeID, TimeBasicHours, TimeOTHrs, TimeOTHrsSat, TimeDouble
11, 40, 8, 4, 0
12, 30, 0, 0, 4

That's how you have it, this is how that data should be stored:

TimeTable
EmployeeID, TimeWorked, TimeType
11, 40, Basic
11, 8, OT
11, 4, OT Sat
12, 30, Basic
12, 4, Double

Then when you need to add all the time together you can easily run an aggregate query and use the SUM function (http://www.w3schools.com/sql/sql_func_sum.asp). Further, when you need to assign different pay multipliers (Basic=1, OT=1.5, Double=2), you create a TimeType table and store it there--no need to go in and add those in the query manually.

Smurf774 · Oct 26, 2016

Hi Plog
Thanks for the info, but when designed the DB they wanted to see columns of the data as these are allocated to projects and it seemed the easiest way at the time. its only when different rates of OT were introduced that the [Time OT Hrs SAT] field was added. However as the other fields can be added together in the query, can you (anyone) shed some light on to why this additional field cannot?

sneuberg · Oct 26, 2016

What does [Time OT Hrs SAT] by itself show, e.g,

Test: [Time OT Hrs SAT]

Smurf774 · Oct 26, 2016

Hi Steve
If I just put that field in the query, its correct. That's what's confusing. It itself will display correctly, but if you add it to the other fields there is simply only it's result shown.

sneuberg · Oct 26, 2016

I really wish I could see this. It would make this a lot easier. The next poke with a stick I suggest is to try using Val or CDbl in the expression e.g.

Code:

Timeperday: Val([Time Basic Hours])+Val([Time OT Hrs])+Val([Time OT Hrs SAT])+Val([Time Double])

if this doesn't change anything try CDbl in place of Val

Calculated Field (1 Viewer)

Smurf774

Registered User.

sneuberg

AWF VIP

Smurf774

Registered User.

sneuberg

AWF VIP

Smurf774

Registered User.

sneuberg

AWF VIP

Smurf774

Registered User.

plog

Banishment Pending

Smurf774

Registered User.

sneuberg

AWF VIP

Smurf774

Registered User.

sneuberg

AWF VIP

Similar threads

Users who are viewing this thread