Calculated Field

[Harry Taylor]

Please help.

I come from an Excel background so am probably doing this wrong, however . . .
I have a Table [RA], in the table i have a fields [Install], [SC], [SCCB]
This is in date order and totals what an engineer has accomplished each day. for example [Install] 1, [SC] 6, [SCCB] 2

I have another Table [Score] with the same fields with a score rating i.e [Install] is 7, and [SC] is 1 [SCCB] is 1

In Table [RA] i want a field that multiplies [Install] by the [Score] [Install]. This would give a daily score, so 1 Install (7) is the same as 7 SC's (7)
Hope that makes sense

From an Excel point of view something like this;
=Sum([RA] [Install] * [Score] [Install]) , ([RA] [SC] * [Score] [SC]) , ([RA] [SCCB] * [Score] [SCCB])

Any ideas?
Thank you in advance

 

[theDBguy]

Hi. Since you have two tables you want to use in a query, you'll need to JOIN them. Once you do that, you can then refer to any columns from either table for your formula.

 

[Harry Taylor]

Thank you.
How about if i just used numbers in one table?
=Sum([RA] [Install] * 7) , ([RA] [SC] * 1) , ([RA] [SCCB] * 1)

Could do that in a Calculated field?

 

[theDBguy]

Thank you.
How about if i just used numbers in one table?
=Sum([RA] [Install] * 7) , ([RA] [SC] * 1) , ([RA] [SCCB] * 1)

Could do that in a Calculated field?

Not in the table itself, I don't think. If you're referring to the Calculated Columns in tables, it can only refer to the fields/columns within the same table. It cannot reach out to other tables to look in their fields.

 

[Harry Taylor]

Hey, I should have said.
I mean a Calculated column in the same table.
So the Calculated column would be - [install] *7 + [SC] *1 + [SCCB]*1
Am i going about this all wrong?

 

[theDBguy]

Hey, I should have said.
I mean a Calculated column in the same table.
So the Calculated column would be - [install] *7 + [SC] *1 + [SCCB]*1
Am i going about this all wrong?

Hi Harry. Yes, if you simply use numbers in your calculation, it should work in a Calculated Field. Give it a shot and let us know how it goes. Cheers!

 

[plog]

I have a Table [RA], in the table i have a fields [Install], [SC], [SCCB]
This is in date order and totals what an engineer has accomplished each day. for example [Install] 1, [SC] 6, [SCCB] 2

I have another Table [Score] with the same fields with a score rating i.e [Install] is 7, and [SC] is 1 [SCCB] is 1

From a database perspective you've incorrectly set up your [RA] table. You don't store values in field names. Install, SC, SCCB all seem to be types of work; because of that they should be the values in a field, not the names of fields. The name of the field would be [WorkType], and you would have 3 records to capture each type's value, not 1 record that captures all 3 together.

When you do that you will be able to use your [Score] table in a query and not have to hard code your formula. As it is now, there is no need for the [Score] table--nothing ever will reference it. Further, if a new work type is ever added you wouldn't add a new field, you would simply add its value into the [WorkType] field discussed above and add 1 record to [Score] that tells the system what that WorkType should be multiplied by.

 

[Harry Taylor]

Thanks Guys,
Managed to get it working by using numbers rather than lookup, so - [Install]*7 + [SC] + [SCCB]
Slight problem that the fields had to have a number or it wouldn't work, so made the default 0.

Thank for your help

 

[CJ_London]

perhaps you have not included all the fields in your table but

This is in date order

cannot be assumed in a table. You need a date field to sort by.

There are huge differences between excel and databases, one of which is an excel 'table' combines views and data in one, whereas in databases, tables just store data in its raw form (no lookups or formatting) and without any order. You get order by sorting. You get views by using queries, usually in combination with forms or reports. If your records are appearing in the same order as you entered them, that is a happy coincidence but cannot be relied on.

 

[Harry Taylor]

Hi CJ,
Thank you for your reply.
I tried to keep my question simple so didn't include everything.
My table does include dates and is 'ordered' that we. It also includes, Week No, Day Status i.e. Working / Holiday / Bank Holiday, so i can discount these when looking at performance. What vehicle they drove, how many miles, times onsite etc.
By using the above I can work out the weekly 'target' and year to date performance.

I wanted to 'Score' the daily jobs to give a daily performance and some jobs score higher than others.

I am a bit of a newbie, and am always grateful for everyone's input