Can you put a calculated field in a table?

[mrbeepa]

I am trying to get a column to display a user defined paylevel. (I tried to
think of how to make this a seperate table but not sure) if a sales person
sold 1 - 3 contracts pay level would = 1; 4 - 7 pay level would = 2 and 8 +
pay level would = 3. I want a table column to display the pay level number
for any given pay week so it can be accessed later to figure bonuses pay
adjustments and etc.

Trouble is I don't see where I can enter a formula in a data column in
design view, was trying to use default view but I don't seem to be able to
access any form of an IF statement, using the expression builder.

I set up a query that calculates the number of sales in a given week but I
don't know how to get that to automatically run when the table is access nor
how to get it to determine a lvl like 1,2 or 3 so far it just counts the
sales. (but I think it's just monday brain on weekend time for that part!!)

Hope this isn't too discombobulated!

Thank you for any assistance,

 

[Jerry Whittle]

Tables store data. You can not have a calculated field in one.

Also, with very few exceptions, you should not even store calculated data in
a field. For example Date of Birth? Yes. Age? No. You can calculate the Age
from the DOB as needed. You can do such calculations in a query, form, or
report, but not in a table.

 

[Ken Sheridan]

One way would be to use the Switch function to return a value based on the
number of sales. Lets say the column in your query which returns the number
of sales per week is called CountOfSales, then in the 'field' row of a blank
column in query design view put:

PayLevel: Switch([CountOfSales]>0 And [CountOfSales]<4,1,[CountOfSales]>3
And [CountOfSales]<8,2,[CountOfSales]>7,3)

This approach is questionable, however, as its really storing data in the
expression. In a relational database data should only be stored as values in
tables. So another way would be to create a table PayLevels with columns
SaleCount and Paylevel, i.e.

SaleCount PayLevel
1 1
2 1
3 1
4 2
5 2
6 2
7 2
8 3
< and so on to>
100 3

This assumes no salesperson will make more than 100 sales per week, but I'd
imagine you won't in fact need to go that high.

You can then simply join your existing query to this on SaleCount and return
the PayLevel column in the final query.

A major advantage of using a PayLevels table is that the data can very
easily be user updated should the basis for the pay levels be changed. One
reason, of course, why data should only be stored in this way and not in
expressions etc.

The question then arises as to whether you should store the resulting values
in a base table, or just rely on the query to return the values as and when
necessary. This is a question of what's called in the jargon 'functional
dependence'.

If the pay levels will always be computed from the values in the PayLevels
table, i.e. if the basis of the pay levels should be changed by updating the
values in the table, and the pay levels computed for employees for all weks
past and present would reflect these changes, then they should not be stored
in a column in a base table but computed on the fly by the query. The levels
are 'functionally dependent' solely on the sales count.

If, however, the basis of the pay levels should be changed by updating the
values in the table, but the pay levels previously computed for each
employee/week should remain static, then the values should be stored in a
table because they are now also functionally dependent on the employee/week,
so you'd have another table with columns such as EmployeeID, WorkYear,
WorkWeek and PayLevel and you'd use an 'append' query (again by joining your
existing query to the PayLevels table) to insert rows into this table on a
regular basis. The primary key of this table should be a composite one of
the EmployeeID, WorkYear and WorkWeek columns (or your equivalent) which
would prevent more than one row per employee/week being inadvertently entered.

Ken Sheridan
Stafford, England

 

[John W. Vinson/MVP]

I am trying to get a column to display a user defined paylevel. (I tried to
think of how to make this a seperate table but not sure) if a sales person
sold 1 - 3 contracts pay level would = 1; 4 - 7 pay level would = 2 and 8 +
pay level would = 3. I want a table column to display the pay level number
for any given pay week so it can be accessed later to figure bonuses pay
adjustments and etc.

Trouble is I don't see where I can enter a formula in a data column in
design view, was trying to use default view but I don't seem to be able to
access any form of an IF statement, using the expression builder.

I set up a query that calculates the number of sales in a given week but I
don't know how to get that to automatically run when the table is access nor
how to get it to determine a lvl like 1,2 or 3 so far it just counts the
sales. (but I think it's just monday brain on weekend time for that part!!)

Hope this isn't too discombobulated!

Thank you for any assistance,

You can't - but then again, you shouldn't.

Storing calculated data such as this in a Table does three things:
wastes disk space; wastes time (almost any calculation is much faster
than a disk fetch); and risks data corruption (since the number of
contracts might change, causing the stored pay level to be WRONG
without any indication).

In this case I'd use a calculation such as:

PayLevel: Switch([Sold] <= 3, 1, [Sold] <= 7, 2, True, 3)

or - especially if the translation might change or if you go to more
levels - a Query joining to a translation table indicating the pay
level for each value of Sold.

 

[mrbeepa]

Thank you all for your advice and suggestions.

This has helped me immensly! I really like the switch ideas. And I do think
I will have to store the pay level because it is subject to change and we
need to know at what rate it was calculated.

This is a great forum.

 

[Jerry Whittle]

Something like the pay level, which occurs at a point in time and would be
very difficult to recalculate, is exactly one of those "exceptions" that I
mentioned. Invoices are another.