Date relationship to a table

[Guest]

I am working on a database for an incentive program. One of the factors is
years of experience. I don't want to use actual years of experience as a
multiplier in the bonus formula, but rather a related factor in a table. For
example, 5 years of experience would have a 3.5 multiplier. I've gotten the
years of experience calculation to work through a query, however, when I
relate it to my table that has the multipliers in it, it shows nothing under
the mulitplier column. I hope that's as clear as mud...Anyone have any ideas?

Thanks alot

 

[Guest]

Try this --
Put both tables in a query but do not join. Pull down all the fields you
need from the incentive table and none from the factors table. Build a new
column in the query like this --
Bonus: [Experience] * [Multiplier]

 

[Guest]

Thanks for the input. That's put me closer. But, what's happening now is that
it's taking the employee names and listing them as many times as there are
records in the years of experience table (which has the multiplier in it).
So if my years of experience table has 1, 2, 3, 4, with associated
multipliers of 1, 1.5, 2.0., 2.5, then it lists the employee records 4 times
in that query...

Try this --
Put both tables in a query but do not join. Pull down all the fields you
need from the incentive table and none from the factors table. Build a new
column in the query like this --
Bonus: [Experience] * [Multiplier]

I am working on a database for an incentive program. One of the factors is
years of experience. I don't want to use actual years of experience as a
multiplier in the bonus formula, but rather a related factor in a table. For
example, 5 years of experience would have a 3.5 multiplier. I've gotten the
years of experience calculation to work through a query, however, when I
relate it to my table that has the multipliers in it, it shows nothing under
the mulitplier column. I hope that's as clear as mud...Anyone have any ideas?

Thanks alot

 

[Guest]

Try adding a criteria to the [Employee].[Experience] fields as
<= [ExperienceTable].[Experience]

Thanks for the input. That's put me closer. But, what's happening now is that
it's taking the employee names and listing them as many times as there are
records in the years of experience table (which has the multiplier in it).
So if my years of experience table has 1, 2, 3, 4, with associated
multipliers of 1, 1.5, 2.0., 2.5, then it lists the employee records 4 times
in that query...

Try this --
Put both tables in a query but do not join. Pull down all the fields you
need from the incentive table and none from the factors table. Build a new
column in the query like this --
Bonus: [Experience] * [Multiplier]

I am working on a database for an incentive program. One of the factors is
years of experience. I don't want to use actual years of experience as a
multiplier in the bonus formula, but rather a related factor in a table. For
example, 5 years of experience would have a 3.5 multiplier. I've gotten the
years of experience calculation to work through a query, however, when I
relate it to my table that has the multipliers in it, it shows nothing under
the mulitplier column. I hope that's as clear as mud...Anyone have any ideas?

Thanks alot

 

[Guest]

Ok, that got me even closer... Now, if an employeee's years of experience is,
say, 3.31 years, it's returning the factor for 4 years and if their
experience is 2.5 years, it returns 3 years and higher. If I do just = to, it
returns nothing. There seems to be a problem with decimal relationships since
3.3 years isn't returning the multiplier for 3.

Try adding a criteria to the [Employee].[Experience] fields as
<= [ExperienceTable].[Experience]

Thanks for the input. That's put me closer. But, what's happening now is that
it's taking the employee names and listing them as many times as there are
records in the years of experience table (which has the multiplier in it).
So if my years of experience table has 1, 2, 3, 4, with associated
multipliers of 1, 1.5, 2.0., 2.5, then it lists the employee records 4 times
in that query...

Try this --
Put both tables in a query but do not join. Pull down all the fields you
need from the incentive table and none from the factors table. Build a new
column in the query like this --
Bonus: [Experience] * [Multiplier]

:

I am working on a database for an incentive program. One of the factors is
years of experience. I don't want to use actual years of experience as a
multiplier in the bonus formula, but rather a related factor in a table. For
example, 5 years of experience would have a 3.5 multiplier. I've gotten the
years of experience calculation to work through a query, however, when I
relate it to my table that has the multipliers in it, it shows nothing under
the mulitplier column. I hope that's as clear as mud...Anyone have any ideas?

Thanks alot

 

[Guest]

Do this --
Bonus: Int([Experience]) * [Multiplier]

This returns the integer (whole number).

Ok, that got me even closer... Now, if an employeee's years of experience is,
say, 3.31 years, it's returning the factor for 4 years and if their
experience is 2.5 years, it returns 3 years and higher. If I do just = to, it
returns nothing. There seems to be a problem with decimal relationships since
3.3 years isn't returning the multiplier for 3.

Try adding a criteria to the [Employee].[Experience] fields as
<= [ExperienceTable].[Experience]

Thanks for the input. That's put me closer. But, what's happening now is that
it's taking the employee names and listing them as many times as there are
records in the years of experience table (which has the multiplier in it).
So if my years of experience table has 1, 2, 3, 4, with associated
multipliers of 1, 1.5, 2.0., 2.5, then it lists the employee records 4 times
in that query...

:

Try this --
Put both tables in a query but do not join. Pull down all the fields you
need from the incentive table and none from the factors table. Build a new
column in the query like this --
Bonus: [Experience] * [Multiplier]

:

I am working on a database for an incentive program. One of the factors is
years of experience. I don't want to use actual years of experience as a
multiplier in the bonus formula, but rather a related factor in a table. For
example, 5 years of experience would have a 3.5 multiplier. I've gotten the
years of experience calculation to work through a query, however, when I
relate it to my table that has the multipliers in it, it shows nothing under
the mulitplier column. I hope that's as clear as mud...Anyone have any ideas?

Thanks alot

 

[Guest]

Yes! Had to do that and a couple other small details, but it's working.
Thanks for the help!

Do this --
Bonus: Int([Experience]) * [Multiplier]

This returns the integer (whole number).

Ok, that got me even closer... Now, if an employeee's years of experience is,
say, 3.31 years, it's returning the factor for 4 years and if their
experience is 2.5 years, it returns 3 years and higher. If I do just = to, it
returns nothing. There seems to be a problem with decimal relationships since
3.3 years isn't returning the multiplier for 3.

Try adding a criteria to the [Employee].[Experience] fields as
<= [ExperienceTable].[Experience]

:

Thanks for the input. That's put me closer. But, what's happening now is that
it's taking the employee names and listing them as many times as there are
records in the years of experience table (which has the multiplier in it).
So if my years of experience table has 1, 2, 3, 4, with associated
multipliers of 1, 1.5, 2.0., 2.5, then it lists the employee records 4 times
in that query...

:

Try this --
Put both tables in a query but do not join. Pull down all the fields you
need from the incentive table and none from the factors table. Build a new
column in the query like this --
Bonus: [Experience] * [Multiplier]

:

I am working on a database for an incentive program. One of the factors is
years of experience. I don't want to use actual years of experience as a
multiplier in the bonus formula, but rather a related factor in a table. For
example, 5 years of experience would have a 3.5 multiplier. I've gotten the
years of experience calculation to work through a query, however, when I
relate it to my table that has the multipliers in it, it shows nothing under
the mulitplier column. I hope that's as clear as mud...Anyone have any ideas?

Thanks alot