Using right group

[Guest]

Hi. I'm going to try my best to explain my problem so stick with me. I am
trying to make a way to base my employee's salary off of Pay Type (Weekday,
Weekend and Holiday), Month and Employee. What I have done is created 3
tables as followed:
Table 1 - Time : Employee's Initials, Date Worked, Pay Type and Hours Worked
Table 2 - Pay Group: Employee's Initials, Pay Group and Date (when pay group
took effect)
Table 3 - Type of Call - Pay Group, Pay Type and Pay Rate
Ever employee is in a pay group. A pay group simply specifies the pay rate
for the different pay types.
So here is what the table might look like:
Table 1 - TJE 5/1/2005 Weekday 8
TJE 6/1/2006 Weekday 8
Table 2 - TJE 1 5/1/2005
TJE 2 6/1/2006
Table 3 - 1 Weekday $10
1 Weekend $15
2 Weekday $20
2 Weekend $25

The problem is when I make a query using Employee's Initials, Hours Worked,
Pay Type, Date (of Hours Worked), Date (when Pay Group took effect), Pay Rate
and Pay Group, it comes out like this:
TJE 8 Weekday 5/1/2005 5/1/2005 $10 1 (should use this one)
TJE 8 Weekday 5/1/2005 6/1/2006 $20 2
TJE 8 Weekday 6/1/2005 5/1/2005 $10 1
TJE 8 Weekday 6/1/2005 6/1/2006 $20 2 (should use this one)
On both dates I used "Between [Type Start Date] And [Type End Date]" in the
criteon. It doesn't seems to understand that each day hours are worked, there
will only be one pay group used. I would like it so it uses pay group 1 from
5/1/2005 until 5/31/2006 and then used pay group 2 from 6/1/2006 on. I know
this will probably be a time consuming problem to help solve, but I would
really appreciate the help!

 

[[MVP] S.Clark]

IMHO, Tables 1 & 2 should be combined, and the records from Table 1 should
get a code, just like Table 2 has. Then, you can link to Table 3 to lookup
the dollar amount based on the code.

I think.

 

[Guest]

Steve,

I do not want to combine table 1 and 2. The data entered into table 1
is done with a data entry form. It would be really inconvinient for my user
to have to look up what pay group each employee is in when they are entering
that employees time, but also when that pay group took effect. It just seems
like the computer should be able to look into table 2 and see what pay group
is currently in use for each employee. I'm not trying to be rude, but this
process needs to be as convinient as possible for user.

IMHO, Tables 1 & 2 should be combined, and the records from Table 1 should
get a code, just like Table 2 has. Then, you can link to Table 3 to lookup
the dollar amount based on the code.

I think.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Hi. I'm going to try my best to explain my problem so stick with me. I am
trying to make a way to base my employee's salary off of Pay Type
(Weekday,
Weekend and Holiday), Month and Employee. What I have done is created 3
tables as followed:
Table 1 - Time : Employee's Initials, Date Worked, Pay Type and Hours
Worked
Table 2 - Pay Group: Employee's Initials, Pay Group and Date (when pay
group
took effect)
Table 3 - Type of Call - Pay Group, Pay Type and Pay Rate
Ever employee is in a pay group. A pay group simply specifies the pay rate
for the different pay types.
So here is what the table might look like:
Table 1 - TJE 5/1/2005 Weekday 8
TJE 6/1/2006 Weekday 8
Table 2 - TJE 1 5/1/2005
TJE 2 6/1/2006
Table 3 - 1 Weekday $10
1 Weekend $15
2 Weekday $20
2 Weekend $25

The problem is when I make a query using Employee's Initials, Hours
Worked,
Pay Type, Date (of Hours Worked), Date (when Pay Group took effect), Pay
Rate
and Pay Group, it comes out like this:
TJE 8 Weekday 5/1/2005 5/1/2005 $10 1 (should use this one)
TJE 8 Weekday 5/1/2005 6/1/2006 $20 2
TJE 8 Weekday 6/1/2005 5/1/2005 $10 1
TJE 8 Weekday 6/1/2005 6/1/2006 $20 2 (should use this one)
On both dates I used "Between [Type Start Date] And [Type End Date]" in
the
criteon. It doesn't seems to understand that each day hours are worked,
there
will only be one pay group used. I would like it so it uses pay group 1
from
5/1/2005 until 5/31/2006 and then used pay group 2 from 6/1/2006 on. I
know
this will probably be a time consuming problem to help solve, but I would
really appreciate the help!

 

[tina]

after some fiddling around, i came up with the following solution, using
only the table and field names you posted - except that i named the two Date
fields as DateWorked and DateAssigned (as a Reserved Word, "Date" should not
be used by itself to name any object in a database).

create Query1, as

SELECT Table2.EmployeeInitials, Table2.PayGroup, Table2.DateAssigned,
Table3.PayType, Table3.PayRate
FROM Table2 LEFT JOIN Table3 ON Table2.PayGroup = Table3.PayGroup;

create Query2, based on Table1 and Query1, as

SELECT Table1.EmployeeInitials, Table1.DateWorked, Last(Query1.DateAssigned)
AS LastOfDateAssigned, Table1.HoursWorked, Last(Query1.PayRate) AS
LastOfPayRate, Last(Query1.PayGroup) AS LastOfPayGroup, Table1.PayType
FROM Table1 LEFT JOIN Query1 ON (Table1.PayType = Query1.PayType) AND
(Table1.EmployeeInitials = Query1.EmployeeInitials)
WHERE (((Query1.DateAssigned)<[Table1].[DateWorked]))
GROUP BY Table1.EmployeeInitials, Table1.DateWorked, Table1.HoursWorked,
Table1.PayType
ORDER BY Table1.EmployeeInitials, Table1.DateWorked;

hth

Steve,

I do not want to combine table 1 and 2. The data entered into table 1
is done with a data entry form. It would be really inconvinient for my user
to have to look up what pay group each employee is in when they are entering
that employees time, but also when that pay group took effect. It just seems
like the computer should be able to look into table 2 and see what pay group
is currently in use for each employee. I'm not trying to be rude, but this
process needs to be as convinient as possible for user.

IMHO, Tables 1 & 2 should be combined, and the records from Table 1 should
get a code, just like Table 2 has. Then, you can link to Table 3 to lookup
the dollar amount based on the code.

I think.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Hi. I'm going to try my best to explain my problem so stick with me. I am
trying to make a way to base my employee's salary off of Pay Type
(Weekday,
Weekend and Holiday), Month and Employee. What I have done is created 3
tables as followed:
Table 1 - Time : Employee's Initials, Date Worked, Pay Type and Hours
Worked
Table 2 - Pay Group: Employee's Initials, Pay Group and Date (when pay
group
took effect)
Table 3 - Type of Call - Pay Group, Pay Type and Pay Rate
Ever employee is in a pay group. A pay group simply specifies the pay rate
for the different pay types.
So here is what the table might look like:
Table 1 - TJE 5/1/2005 Weekday 8
TJE 6/1/2006 Weekday 8
Table 2 - TJE 1 5/1/2005
TJE 2 6/1/2006
Table 3 - 1 Weekday $10
1 Weekend $15
2 Weekday $20
2 Weekend $25

The problem is when I make a query using Employee's Initials, Hours
Worked,
Pay Type, Date (of Hours Worked), Date (when Pay Group took effect), Pay
Rate
and Pay Group, it comes out like this:
TJE 8 Weekday 5/1/2005 5/1/2005 $10 1 (should use this one)
TJE 8 Weekday 5/1/2005 6/1/2006 $20 2
TJE 8 Weekday 6/1/2005 5/1/2005 $10 1
TJE 8 Weekday 6/1/2005 6/1/2006 $20 2 (should use this one)
On both dates I used "Between [Type Start Date] And [Type End Date]" in
the
criteon. It doesn't seems to understand that each day hours are worked,
there
will only be one pay group used. I would like it so it uses pay group 1
from
5/1/2005 until 5/31/2006 and then used pay group 2 from 6/1/2006 on. I
know
this will probably be a time consuming problem to help solve, but I would
really appreciate the help!

 

[Guest]

Tina,

Thank you so much! I have been working on this for the last couple of
week with no luck. But this is perfect. I really appreciate you help!

Tandy

 

[tina]

oh, glad it worked for you, Tandy - quite the little bear, wasn't it?

 

[[MVP] S.Clark]

Sorry, I did not recognize that T1 was for data entry, and I see your
point..
Glad Tina could find a solution for you.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

Steve,

I do not want to combine table 1 and 2. The data entered into table 1
is done with a data entry form. It would be really inconvinient for my
user
to have to look up what pay group each employee is in when they are
entering
that employees time, but also when that pay group took effect. It just
seems
like the computer should be able to look into table 2 and see what pay
group
is currently in use for each employee. I'm not trying to be rude, but this
process needs to be as convinient as possible for user.

IMHO, Tables 1 & 2 should be combined, and the records from Table 1
should
get a code, just like Table 2 has. Then, you can link to Table 3 to
lookup
the dollar amount based on the code.

I think.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Hi. I'm going to try my best to explain my problem so stick with me. I
am
trying to make a way to base my employee's salary off of Pay Type
(Weekday,
Weekend and Holiday), Month and Employee. What I have done is created 3
tables as followed:
Table 1 - Time : Employee's Initials, Date Worked, Pay Type and Hours
Worked
Table 2 - Pay Group: Employee's Initials, Pay Group and Date (when pay
group
took effect)
Table 3 - Type of Call - Pay Group, Pay Type and Pay Rate
Ever employee is in a pay group. A pay group simply specifies the pay
rate
for the different pay types.
So here is what the table might look like:
Table 1 - TJE 5/1/2005 Weekday 8
TJE 6/1/2006 Weekday 8
Table 2 - TJE 1 5/1/2005
TJE 2 6/1/2006
Table 3 - 1 Weekday $10
1 Weekend $15
2 Weekday $20
2 Weekend $25

The problem is when I make a query using Employee's Initials, Hours
Worked,
Pay Type, Date (of Hours Worked), Date (when Pay Group took effect),
Pay
Rate
and Pay Group, it comes out like this:
TJE 8 Weekday 5/1/2005 5/1/2005 $10 1 (should use this one)
TJE 8 Weekday 5/1/2005 6/1/2006 $20 2
TJE 8 Weekday 6/1/2005 5/1/2005 $10 1
TJE 8 Weekday 6/1/2005 6/1/2006 $20 2 (should use this one)
On both dates I used "Between [Type Start Date] And [Type End Date]" in
the
criteon. It doesn't seems to understand that each day hours are worked,
there
will only be one pay group used. I would like it so it uses pay group 1
from
5/1/2005 until 5/31/2006 and then used pay group 2 from 6/1/2006 on. I
know
this will probably be a time consuming problem to help solve, but I
would
really appreciate the help!

 

[tina]

i didn't recognize it either, Steve. <g> i originally thought her tables
design was flawed, and wrote out what i thought would be an appropriate
tables design - only then did i see that three of my tables actually matched
her tables 1, 2, and 3. and finally the lightbulb went on for me. then i had
to actually build the tables and fiddle around with some queries before
stumbling onto a query design that worked! <bg>

Sorry, I did not recognize that T1 was for data entry, and I see your
point..
Glad Tina could find a solution for you.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

Steve,

I do not want to combine table 1 and 2. The data entered into table 1
is done with a data entry form. It would be really inconvinient for my
user
to have to look up what pay group each employee is in when they are
entering
that employees time, but also when that pay group took effect. It just
seems
like the computer should be able to look into table 2 and see what pay
group
is currently in use for each employee. I'm not trying to be rude, but this
process needs to be as convinient as possible for user.

IMHO, Tables 1 & 2 should be combined, and the records from Table 1
should
get a code, just like Table 2 has. Then, you can link to Table 3 to
lookup
the dollar amount based on the code.

I think.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Hi. I'm going to try my best to explain my problem so stick with me. I
am
trying to make a way to base my employee's salary off of Pay Type
(Weekday,
Weekend and Holiday), Month and Employee. What I have done is created 3
tables as followed:
Table 1 - Time : Employee's Initials, Date Worked, Pay Type and Hours
Worked
Table 2 - Pay Group: Employee's Initials, Pay Group and Date (when pay
group
took effect)
Table 3 - Type of Call - Pay Group, Pay Type and Pay Rate
Ever employee is in a pay group. A pay group simply specifies the pay
rate
for the different pay types.
So here is what the table might look like:
Table 1 - TJE 5/1/2005 Weekday 8
TJE 6/1/2006 Weekday 8
Table 2 - TJE 1 5/1/2005
TJE 2 6/1/2006
Table 3 - 1 Weekday $10
1 Weekend $15
2 Weekday $20
2 Weekend $25

The problem is when I make a query using Employee's Initials, Hours
Worked,
Pay Type, Date (of Hours Worked), Date (when Pay Group took effect),
Pay
Rate
and Pay Group, it comes out like this:
TJE 8 Weekday 5/1/2005 5/1/2005 $10 1 (should use this one)
TJE 8 Weekday 5/1/2005 6/1/2006 $20 2
TJE 8 Weekday 6/1/2005 5/1/2005 $10 1
TJE 8 Weekday 6/1/2005 6/1/2006 $20 2 (should use this one)
On both dates I used "Between [Type Start Date] And [Type End Date]" in
the
criteon. It doesn't seems to understand that each day hours are worked,
there
will only be one pay group used. I would like it so it uses pay group 1
from
5/1/2005 until 5/31/2006 and then used pay group 2 from 6/1/2006 on. I
know
this will probably be a time consuming problem to help solve, but I
would
really appreciate the help!

 

[Marshall Barton]

Hey Tina, whatever works for you. The result is what
counts.

With more practice, it'll even get (a little) easier to
interpret all the unusual ways a question can be phrased
;-)

 

[tina]

With more practice, it'll even get (a little) easier to

interpret all the unusual ways a question can be phrased
;-)

LOL, promise? if i had a penny (well, a nickel - inflation, you know) for
every time i read a post and think "what...???", only to have an MVP or one
of the other highly-skilled contributors post back with "Oh, yes, just
do....", at which point i think "ohhh, that's what they meant..." <bg>

 

[Guest]

Tina,

Hi! I was wondering if you would possibly help me with a problem I am
having making a data entry form for table 3 (Pay Type/Rate). I have been told
many time not to tack on new problems, so my question is in the Access
Database Forms group. The subject line reads something like "Repost: Please
help! Or just tell me if not possible..." I posted it on 7/14. I would really
like your help given your understanding of my table and that I can not
restructure it. However, if you cannnot help for whatever reason, I
understand. I want to thank you again for doing what you have done so far. It
works perfect for my database and helped me tremendously!

Tandy

 

[tina]

just found your post, Tandy. i believe i have a solution to offer you; i'll
post details to the new thread today - i won't be back to this thread again.