selecting records from a table

  • Thread starter Thread starter Pam
  • Start date Start date
P

Pam

I have a table which has different hourly rates of pay for
differnt jobs for each employee. (i.e. - shows emp #, job
1 rate, job 2 rate, job 3 rate, etc.)
When inputting the job done to figure up pay, I need to
select the record from this table that matches the
employee number and the job number. I'm in a state of
brain drain right now on this! Every route I've looked at
has a stumbling block I'm getting stuck on since I need to
make two matches and then pull in that field. The only
solution I've found somewhat workable, but kludgy, is
nested IIF statements!
Would someone please advise me on the way to do this??
Thanks-
 
Pam, the main trick is to get the right relational structure.
Then the calcuation of pay is easy.

if you need to be able to specify an hourly rate for each combination of
employee and job, you need a table with these fields:
EmpID Number (Long) Related to tblEmp.EmpID
JobID Number (Long) Related to tblJob.JobID
Rate Currency Hourly rate for this employee in this
job.

You will have another table that records multiple times the employee works
on any job:
EmpID Number (Long) Related to tblEmp.EmpID
JobID Number (Long) Related to tblJob.JobID
Start Date/Time Time the person began working on the job
End Date/Time Time the person knocked off this job this
time.

You can now create a query that calculates the pay by typing this expression
into a query that contains the above tables:
Pay: CCur(Round(DateDiff("n", [Start], [End]) * [Rate] / 60, 2))
 
My setup is a little more tricky than that - and thus my
problem. The hourly rates are in a table structured as:
empID
job 1 (rate for job 1)
job 2 (rate for job 2)
job 3 (rate for job 3)

Each employee has (or can have) a different rate of pay
for each job. Therefore, on my time input form I'm putting
in empID, date, hours, job number. I need to pull the rate
of pay for that employee for the job done.
IOW, need to pull in a different field (job1, job2 or
job3) depending on job done.
Hope that explains my problem a little better.
Thanks for the assistance-

-----Original Message-----
Pam, the main trick is to get the right relational structure.
Then the calcuation of pay is easy.

if you need to be able to specify an hourly rate for each combination of
employee and job, you need a table with these fields:
EmpID Number (Long) Related to tblEmp.EmpID
JobID Number (Long) Related to tblJob.JobID
Rate Currency Hourly rate for this employee in this
job.

You will have another table that records multiple times the employee works
on any job:
EmpID Number (Long) Related to tblEmp.EmpID
JobID Number (Long) Related to tblJob.JobID
Start Date/Time Time the person began working on the job
End Date/Time Time the person knocked off this job this
time.

You can now create a query that calculates the pay by typing this expression
into a query that contains the above tables:
Pay: CCur(Round(DateDiff("n", [Start], [End]) * [Rate] / 60, 2))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which has different hourly rates of pay for
differnt jobs for each employee. (i.e. - shows emp #, job
1 rate, job 2 rate, job 3 rate, etc.)
When inputting the job done to figure up pay, I need to
select the record from this table that matches the
employee number and the job number. I'm in a state of
brain drain right now on this! Every route I've looked at
has a stumbling block I'm getting stuck on since I need to
make two matches and then pull in that field. The only
solution I've found somewhat workable, but kludgy, is
nested IIF statements!
Would someone please advise me on the way to do this??
Thanks-
Click to expand...


.
Click to expand...
 
Yes: it is your field structure that is causing the problem.

Whenever you see a field repeating, such as Job1, Job2, Job3, it is a very
good indicator that you need to create a related table instead. That will
solve the problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Pam said:
My setup is a little more tricky than that - and thus my
problem. The hourly rates are in a table structured as:
empID
job 1 (rate for job 1)
job 2 (rate for job 2)
job 3 (rate for job 3)

Each employee has (or can have) a different rate of pay
for each job. Therefore, on my time input form I'm putting
in empID, date, hours, job number. I need to pull the rate
of pay for that employee for the job done.
IOW, need to pull in a different field (job1, job2 or
job3) depending on job done.
Hope that explains my problem a little better.
Thanks for the assistance-

-----Original Message-----
Pam, the main trick is to get the right relational structure.
Then the calcuation of pay is easy.

if you need to be able to specify an hourly rate for each combination of
employee and job, you need a table with these fields:
EmpID Number (Long) Related to tblEmp.EmpID
JobID Number (Long) Related to tblJob.JobID
Rate Currency Hourly rate for this employee in this
job.

You will have another table that records multiple times the employee works
on any job:
EmpID Number (Long) Related to tblEmp.EmpID
JobID Number (Long) Related to tblJob.JobID
Start Date/Time Time the person began working on the job
End Date/Time Time the person knocked off this job this
time.

You can now create a query that calculates the pay by typing this expression
into a query that contains the above tables:
Pay: CCur(Round(DateDiff("n", [Start], [End]) * [Rate] / 60, 2))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which has different hourly rates of pay for
differnt jobs for each employee. (i.e. - shows emp #, job
1 rate, job 2 rate, job 3 rate, etc.)
When inputting the job done to figure up pay, I need to
select the record from this table that matches the
employee number and the job number. I'm in a state of
brain drain right now on this! Every route I've looked at
has a stumbling block I'm getting stuck on since I need to
make two matches and then pull in that field. The only
solution I've found somewhat workable, but kludgy, is
nested IIF statements!
Would someone please advise me on the way to do this??
Thanks-
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Pam,

It sounds from your response to Allen's post that you may
have misinterpreted it, so I thought I would try to add
some clarification.

If you look back closely at Allen's earlier post, the
table structure that he suggested will be able to track
an infinite number of jobs, and corresponding rates, for
each employee. This is the best way to structure your
tables for a case like this. If done the way Allen
suggested, you can define the table relationships so that
Access will know that the times in your time log table
relate to the rate in your rates table based on the
Employee ID and the Job Number. This will make every
thing else you do easier and more efficient.

Hope this helps.

-Ted
-----Original Message-----
My setup is a little more tricky than that - and thus my
problem. The hourly rates are in a table structured as:
empID
job 1 (rate for job 1)
job 2 (rate for job 2)
job 3 (rate for job 3)

Each employee has (or can have) a different rate of pay
for each job. Therefore, on my time input form I'm putting
in empID, date, hours, job number. I need to pull the rate
of pay for that employee for the job done.
IOW, need to pull in a different field (job1, job2 or
job3) depending on job done.
Hope that explains my problem a little better.
Thanks for the assistance-

-----Original Message-----
Pam, the main trick is to get the right relational structure.
Then the calcuation of pay is easy.

if you need to be able to specify an hourly rate for
Click to expand...
each
combination of
employee and job, you need a table with these fields:
EmpID Number (Long) Related to tblEmp.EmpID
JobID Number (Long) Related to tblJob.JobID
Rate Currency Hourly rate for this employee in this
job.

You will have another table that records multiple times the employee works
on any job:
EmpID Number (Long) Related to tblEmp.EmpID
JobID Number (Long) Related to tblJob.JobID
Start Date/Time Time the person
Click to expand...
began
working on the job
End Date/Time Time the person
Click to expand...
knocked
off this job this
time.

You can now create a query that calculates the pay by typing this expression
into a query that contains the above tables:
Pay: CCur(Round(DateDiff("n", [Start], [End]) * [Rate] / 60, 2))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which has different hourly rates of
Click to expand...
Click to expand...
pay
for
Click to expand...
looked
at
Click to expand...
need
to


.
Click to expand...
.
Click to expand...
 
Back
Top