Lookup

[JimDerDog]

Hi all - I have a problem with an Overtime recording database that I am
hoping someone may be able to assist with.
I have a master form with a box to set a month to look at. I then have a
subform which shows data relating to that month from a long list of employee
IDs, hours, pay rates and the month in question - ie one record per month
that an employee does overtime. The number of people doing overtime each
month varies wildly so I dont want a record per month per employee, or most
of them will be blank or zero.
The problem I have is looking up the pay rate applicable for that month when
inputting the information. I have a table of pay rates, showing what date
they were implemented and need to be able to look up in that table the last
pay rate for the particular employee prior to the month in question on the
master form, then set this as the rate in the subform record. presumably
this could be done on exiting or after updating each record, but i am not
good at lookup expressions.
If anyone understands the gibberish that I have written above and can
assist, it would be much appreciated.
Thanks

JimDer

 

[Dennis]

Have a look in help on the DLookUp function. A simple example would be

DLooup("[Pay Rate]","[Pay Rates Table]","[Month] = 'June'")

This will get the Pay rate field value from the Pay Rates Table from the
record where the Month is June.

 

[JimDerDog]

Thanks for your help Dennis - one outstanding question though. The pay rates
table would not have an entry for june necessarily, but several at different
points prior to june, whenever the employee had received an increase in the
past. It may even have future increases logged, waiting to be implemented.
The problem I had was getting the lookup to find the last one of these that
is before the month being looked at in the main form.
Sorry if I am pushing my luck!

JimDer

Have a look in help on the DLookUp function. A simple example would be

DLooup("[Pay Rate]","[Pay Rates Table]","[Month] = 'June'")

This will get the Pay rate field value from the Pay Rates Table from the
record where the Month is June.

Hi all - I have a problem with an Overtime recording database that I am
hoping someone may be able to assist with.
I have a master form with a box to set a month to look at. I then have a
subform which shows data relating to that month from a long list of employee
IDs, hours, pay rates and the month in question - ie one record per month
that an employee does overtime. The number of people doing overtime each
month varies wildly so I dont want a record per month per employee, or most
of them will be blank or zero.
The problem I have is looking up the pay rate applicable for that month when
inputting the information. I have a table of pay rates, showing what date
they were implemented and need to be able to look up in that table the last
pay rate for the particular employee prior to the month in question on the
master form, then set this as the rate in the subform record. presumably
this could be done on exiting or after updating each record, but i am not
good at lookup expressions.
If anyone understands the gibberish that I have written above and can
assist, it would be much appreciated.
Thanks

JimDer

 

[Dennis]

The only way I can think of is to set up a query that contains the Employees
ID and the Date with the criteria on the date set to <Now()
Create another query based on the first query and click the totals button.
Leave Employees ID as Group By and set Pay Rate to Max.
Create another query with your pay rates table joined to the 2nd query on
Employees ID and Date and include the Pay Rate column. This query should only
produce 1 row. You can then use the

DLookUp("Pay Rate","Last Query")

Hope this makes sense.

Thanks for your help Dennis - one outstanding question though. The pay rates
table would not have an entry for june necessarily, but several at different
points prior to june, whenever the employee had received an increase in the
past. It may even have future increases logged, waiting to be implemented.
The problem I had was getting the lookup to find the last one of these that
is before the month being looked at in the main form.
Sorry if I am pushing my luck!

JimDer

Have a look in help on the DLookUp function. A simple example would be

DLooup("[Pay Rate]","[Pay Rates Table]","[Month] = 'June'")

This will get the Pay rate field value from the Pay Rates Table from the
record where the Month is June.

Hi all - I have a problem with an Overtime recording database that I am
hoping someone may be able to assist with.
I have a master form with a box to set a month to look at. I then have a
subform which shows data relating to that month from a long list of employee
IDs, hours, pay rates and the month in question - ie one record per month
that an employee does overtime. The number of people doing overtime each
month varies wildly so I dont want a record per month per employee, or most
of them will be blank or zero.
The problem I have is looking up the pay rate applicable for that month when
inputting the information. I have a table of pay rates, showing what date
they were implemented and need to be able to look up in that table the last
pay rate for the particular employee prior to the month in question on the
master form, then set this as the rate in the subform record. presumably
this could be done on exiting or after updating each record, but i am not
good at lookup expressions.
If anyone understands the gibberish that I have written above and can
assist, it would be much appreciated.
Thanks

JimDer