Using lookups or rates problems

[Eddie]

I have an Access database that tracks project information. The
database is very simple, except for 1 small aspect.

Some background: 4 Tables - Project information, Employees, activity
and pay rates.

The user will add an activity to a project. The activity will take
place on a date, so they will enter the date it took place and the
hours worked. The problem is that the employee might get an increase
during the project, which will need to be changed in the rates table.
This creates a new entry, keeping the old rate up to that date and I
then enter the new rate from that date.

If an activity is entered, I would like the database to automatically
look at the date the activity took place, and use the rate from the
rates table for that period.

I don't know if I have the correct apprach down for this database, and
any help would be appreciated. My boss is counting on me to make this
work, how, I don't know!

Sample table info and relationships:
Employees -- ID and name fields
Project -- ID and name, and details of project
Activity -- ID, Type of Activity, Date, employee ID and Project ID
Rates -- rateid, employee ID, rate, rate date from

Any ideas on how to make this work??

Please help!!!

--Ed

[Larry Linson]

You'll make life a lot easier if you have a from and a to date for the
rates... until a rate expires, use a far future date. It will be simpler to
use a BETWEEN criteria for a date than any other way. The tables seem to
assume that a minimum time for an activity is a full day... or you need to
add either To/From Time or HoursWorked to the Activity. I assume you want to
use the Text for the Activity... otherwise you may want to add an Activity
table with ActivityID and Description, etc. (if nothing else, that would
give you a check on data entry).

Larry Linson
Microsoft Access MVP

"Eddie" <ed**********@mousetraining.com> wrote in message
news:d5*************************@posting.google.co m...

I have an Access database that tracks project information. The
database is very simple, except for 1 small aspect.

Some background: 4 Tables - Project information, Employees, activity
and pay rates.

The user will add an activity to a project. The activity will take
place on a date, so they will enter the date it took place and the
hours worked. The problem is that the employee might get an increase
during the project, which will need to be changed in the rates table.
This creates a new entry, keeping the old rate up to that date and I
then enter the new rate from that date.

If an activity is entered, I would like the database to automatically
look at the date the activity took place, and use the rate from the
rates table for that period.

I don't know if I have the correct apprach down for this database, and
any help would be appreciated. My boss is counting on me to make this
work, how, I don't know!

Sample table info and relationships:
Employees -- ID and name fields
Project -- ID and name, and details of project
Activity -- ID, Type of Activity, Date, employee ID and Project ID
Rates -- rateid, employee ID, rate, rate date from

Any ideas on how to make this work??

Please help!!!

--Ed

[Eddie]

Hi Larry, thanks for this. I know that I didn't tell you all the
fields in the tables. The activity table will have a hours worked on
it, very simple as we only use 1 or .5 hours. The problem is looking
up the activity date against the employee pay rate date started, and
returning the rate by the nearest match down . Its almost like the
following in a vlookup

vlookup (activity date,employee rate start date, rate).

I add activities by employee for each project. The database miust
return all the activities for the project, and the activity cost for
all or 1 employee.

Any examples would be great. I hope my blurb is clear enough.

Regards and thanks a million to anyone who helps,
Eddie Holder
Novice database builder....
ed**********@mousetraining.com (Eddie) wrote in message news:<d5*************************@posting.google.c om>...

I have an Access database that tracks project information. The
database is very simple, except for 1 small aspect.

Some background: 4 Tables - Project information, Employees, activity
and pay rates.

The user will add an activity to a project. The activity will take
place on a date, so they will enter the date it took place and the
hours worked. The problem is that the employee might get an increase
during the project, which will need to be changed in the rates table.
This creates a new entry, keeping the old rate up to that date and I
then enter the new rate from that date.

If an activity is entered, I would like the database to automatically
look at the date the activity took place, and use the rate from the
rates table for that period.

I don't know if I have the correct apprach down for this database, and
any help would be appreciated. My boss is counting on me to make this
work, how, I don't know!

Sample table info and relationships:
Employees -- ID and name fields
Project -- ID and name, and details of project
Activity -- ID, Type of Activity, Date, employee ID and Project ID
Rates -- rateid, employee ID, rate, rate date from

Any ideas on how to make this work??

Please help!!!

--Ed

[Larry Linson]

Under what circumstances do you want to perform this action -- it might be
done by joining the tables in a query, using a DLOOKUP in code, or ???.
Whichever way, my advice on having an end date as well as a start date for
the rate table still stands.

SELECT x FROM y WHERE z BETWEEN a AND b

is a lot easier than trying to order the records and pick only the first one
where current date exceeds the date in the record. Records in tables in
relational databases, unlike "vlookup", are, by definition, unordered. You
have to use an ORDER BY clause in the SQL to order them (this can be
generated by "Sort" in the Query Builder).

Assuming you only need to show the rate in a report (invoice), then joining
the tables in the Query that is the RecordSource of the Report would be the
proper approach.

Larry Linson
Microsoft Access MVP

"Eddie" <ed**********@mousetraining.com> wrote in message
news:d5**************************@posting.google.c om...

Hi Larry, thanks for this. I know that I didn't tell you all the
fields in the tables. The activity table will have a hours worked on
it, very simple as we only use 1 or .5 hours. The problem is looking
up the activity date against the employee pay rate date started, and
returning the rate by the nearest match down . Its almost like the
following in a vlookup

vlookup (activity date,employee rate start date, rate).

I add activities by employee for each project. The database miust
return all the activities for the project, and the activity cost for
all or 1 employee.

Any examples would be great. I hope my blurb is clear enough.

Regards and thanks a million to anyone who helps,
Eddie Holder
Novice database builder....
ed**********@mousetraining.com (Eddie) wrote in message

news:<d5*************************@posting.google.c om>...

I have an Access database that tracks project information. The
database is very simple, except for 1 small aspect.

Some background: 4 Tables - Project information, Employees, activity
and pay rates.

The user will add an activity to a project. The activity will take
place on a date, so they will enter the date it took place and the
hours worked. The problem is that the employee might get an increase
during the project, which will need to be changed in the rates table.
This creates a new entry, keeping the old rate up to that date and I
then enter the new rate from that date.

If an activity is entered, I would like the database to automatically
look at the date the activity took place, and use the rate from the
rates table for that period.

I don't know if I have the correct apprach down for this database, and
any help would be appreciated. My boss is counting on me to make this
work, how, I don't know!

Sample table info and relationships:
Employees -- ID and name fields
Project -- ID and name, and details of project
Activity -- ID, Type of Activity, Date, employee ID and Project ID
Rates -- rateid, employee ID, rate, rate date from

Any ideas on how to make this work??

Please help!!!

--Ed

[Eddie]

Hi Larry. I actually have found a solution to my problem. I used a sub
query to get the results. here is the syntax i used, with the help of
another user in the group.

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)));
Thanks for your advice. You and the aother user was of great help.

Thanks a million

ed

[Larry Linson]

You are welcome. I'm glad you found a solution.

Larry Linson
Microsoft Access MVP

"Eddie" <ed**********@mousetraining.com> wrote in message
news:d5**************************@posting.google.c om...

Hi Larry. I actually have found a solution to my problem. I used a sub
query to get the results. here is the syntax i used, with the help of
another user in the group.

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)));
Thanks for your advice. You and the aother user was of great help.

Thanks a million

ed