Uosing different rates for employees in various stages

[Eddie]

Hi to all
I am building a database to track project expenses information. My
database has 4 main tables, Employees, Activity, project info and
rates.

We add a project and track activities on the project. Each employee
will add an activity on a certain date. What I need to do is check the
date the activity was added and then check what the rate is that
employee was paid during that period.

I am thinking in the lines of a vlookup in excel, but can't seem to
find a similiar function in access. I simply want to add an employee
increase/decrease rate in the rates table. Then, when I add an
activity for a date, it should match the activity date with the rate
date, rounding down (as a vlookup does) and use that rate.

Any assistance in this matter would be appreciated. This will save me
hours of check 10 years worth of activity rates.

Kind regards,

Eddie

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll have to use a subquery to find the Rate in the correct date
range. E.g., say you wanted to find an employee's Rate for the date
Oct. 31, 2002, you'd use a query like this:

SELECT E.EmployeeName, R.Rate
FROM Employees As E INNER JOIN Rates As R
ON E.EmployeeID = R.EmployeeID
WHERE R.EffectiveDate = (SELECT MAX(EffectiveDate)
FROM Rates WHERE EmployeeID = E.EmployeeID
AND EffectiveDate <= #10/31/2002#)
AND EmployeeID = 2566

The subquery gets the last effective date that is on, or before, Oct.
31, 2002. The main query uses this date to get the Rate that was
entered on that date.

The above assumes the Rates table is structured similar to this:

EmployeeID, EffectiveDate, Rate
< number > < date > < currency or double >

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQMYEp4echKqOuFEgEQLd1QCeNOzXxdoYqYnuqdsfDTS2MX CwQcQAoK7C
Pz9nlsAGb4vx8HWdqhagNQy0
=hyxy
-----END PGP SIGNATURE-----

Eddie wrote:

Hi to all
I am building a database to track project expenses information. My
database has 4 main tables, Employees, Activity, project info and
rates.

We add a project and track activities on the project. Each employee
will add an activity on a certain date. What I need to do is check the
date the activity was added and then check what the rate is that
employee was paid during that period.

I am thinking in the lines of a vlookup in excel, but can't seem to
find a similiar function in access. I simply want to add an employee
increase/decrease rate in the rates table. Then, when I add an
activity for a date, it should match the activity date with the rate
date, rounding down (as a vlookup does) and use that rate.

Any assistance in this matter would be appreciated. This will save me
hours of check 10 years worth of activity rates.

[Eddie]

Thanks a million! I really appreciate the help on the sub query. This
works fine and all my records are accurate. I can't say that I
understand the statement quite, but this is what it looks like. Could
you explain the section from where the sub query kicks in. That would
be great. Thanks a million again

Regards,

Eddie

SELECT employees.Name, rates.rate, Activity.Activitydate,
Activity.ProjectName, Activity.Hours, [Hours]*[rate] AS [Total
Activity Cost]
FROM (employees RIGHT JOIN Activity ON employees.empid =
Activity.Empid) LEFT JOIN rates ON employees.empid = rates.empid
WHERE (((rates.effectivedate)=(SELECT MAX(EffectiveDate)
FROM Rates WHERE EmpID = Employees.EmpID
AND EffectiveDate <= activity.activitydate)));

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The subquery selects the date that is on, or before, the Activity Date,
for all the Employees. E.g.: You want to find the latest EffectiveDate
that is on, or before, Oct. 14, 2003. If an employee's Effective dates
are like this:

EffectiveDate
6/15/2001
7/1/2002
7/1/2003 <-
8/1/2004

then 7/1/2003 is the date selected as the Max(EffectiveDate). IOW,
7/1/2003 is the highest (MAX) date before 10/14/2003 in that employee's
list of EffectiveDates.

Since there is only one Rate for that employee on that EffectiveDate (or
should be only one Rate) the main query uses the employee's Rate that is
on the EffectiveDate selected by the subquery.

HTH,

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQMfeKIechKqOuFEgEQLmDQCgkCtBVhrWFoXrZEExgPhiOh yyrWAAoInJ
a97YcA7iFoCe5CnJG5PUnsRD
=JHV3
-----END PGP SIGNATURE-----
Eddie wrote:

Thanks a million! I really appreciate the help on the sub query. This
works fine and all my records are accurate. I can't say that I
understand the statement quite, but this is what it looks like. Could
you explain the section from where the sub query kicks in. That would
be great. Thanks a million again

Regards,

Eddie

SELECT employees.Name, rates.rate, Activity.Activitydate,
Activity.ProjectName, Activity.Hours, [Hours]*[rate] AS [Total
Activity Cost]
FROM (employees RIGHT JOIN Activity ON employees.empid =
Activity.Empid) LEFT JOIN rates ON employees.empid = rates.empid
WHERE (((rates.effectivedate)=(SELECT MAX(EffectiveDate)
FROM Rates WHERE EmpID = Employees.EmpID
AND EffectiveDate <= activity.activitydate)));