FOrm and COmbo Box...

Thank you Tina.

I followed your example in your previous post and got things working all the
way to the Main form. The main form displays the Unit and Pay Period combo
boxes, and they have proper values in the drop down list, but nothing happens
when the selections are made. Also, when I open the form, I get a parameter
box asking for a Unit, and then a Pay Period, then the form appears,
selections are made via the combo boxes, but nothing appears in the sub form.
Any suggestions. I'm pretty sure that I folowed you procedures very
carefully. I also re-did the relationships relating the tblEmployeeOveetime
to tblEmployee with EmployeeID and relating the tblPayPeriod PayPeriodID to
tblEmployeeOveretime PayPeriodID.

Mark
-----
"tina" wrote:

Mark, my warning was about Lookup *fields* in tables, not about "lookup"
tables (i call them "supporting" tables, so as to not confuse them with
Lookup fields). databases often have numerous supporting tables. they're
typically "parent" tables, with a one-to-many link to one or more data
tables, that simply provide a list of valid values for use in the linked
data table(s) . usually, the values in a supporting table are not added
to/edited/deleted very often. and typically the kind of table that i call a
supporting table has only two fields, a Number field that serves as a
primary key, and a value field for whatever values are being listed. lookup
tables are useful in three ways: 1) they ensure that valid, correctly
spelled values are available to the user, in forms; 2) they allow for more
efficient data entry in forms - generally picking from a pre-defined combo
box droplist is easier and faster than typing a value manually and checking
that it's spelled correctly; 3) they provide standardized values, that are
meaningful to the company, which can be used to "slice 'n dice" the data for
statistical analysis.

the tblPayPeriods, that i suggested in my previous post, is an example of a
supporting table. some others i've used a tblTitles (Mr., Mrs., Ms,
Rev., Dr., etc); tblPhoneTypes (Home, Office, Business, Cell, Fax, Pager,
etc); tblCategories (any list of categories that compartmentalize or "label"
data in a way that is meaningful to the company using the database);
tblStates (a list of the U.S. states with a field for the two-character USPS
abbreviation, and a field for the full name of the state/territory).

hth


"NWO" wrote in message
...
Tina, regarding the lookup table business, would you then recommend that
the
values just be entered in the field even if there are several duplicate
values, or just do a better job at normalizing the table structure so that
these repeating fileds are located in another table (I suspect you're
going
to go with the latter, which means I'll have to make significant chmnages
to
my tables because most of the fileds are indeed based on lookup tables,
but
that's OK because I want to do the right thing at the desing stage and not
pay for poor desing latter). Your coments are always welcome.

Mark
-------------

"tina" wrote:

well, i believe i would simplify the table structure (and hence the data
entry issues), as

no change to tblEmployees

tblPayPeriods
PayPeriodID
PayPeriod
note: this changes the table to be simply a list of all pay periods
(you
could add the coming years pay periods at the beginning of each year,
for
instance, so they're available all year long for data entry purposes).
the
table is *not* linked to tblEmployees.
*also note*: you should NOT give a field in a table the *exact* same
name
as the table's name. recommend you change one or the other, for example
by
making the table name plural, as in the example above.

tblEmployeeOvertime
OvertimeID (pk)
EmployeeID (fk)
PayPeriodID (fk)
G22LineNUmber
NumApprovals
NumDenials
NumHours

note: if you have any Lookup fields in any of your tables, recommend
you
get rid of them. see http://www.mvps.org/access/lookupfields.htm for
reasons
why.

okay, now to address the data entry scenario you previously described as
what you "really want to do":

Supervisors first selectes a PAy Period form a drop down list.
Supervisor
then selects a Unit name form another drop down list and all of the
employees
assinged to that unit appear in a subform. Supervisor then selects an
employee and either existing Overtime record(s) record appers for the
Pay
Period selected or the supervisor has the ability to add new Overtime
detail
records for the selected pay period.

suggest the following:

create a form, bound to tblEmployees, i'll call it sfrmEmployees, and
set
the form's DefaultView property to SingleForm.

create another form, bound to tblEmployeeOvertime, i'll call it
sfrmOvertime, and set the form's DefaultView to ContinuousForms or
Datasheet.

open sfrmEmployees and add a subform control, i'll call it
ChildOvertime.
set its' properties as follows

SourceObject: sfrmOvertime
LinkChildFields: EmployeeID
LinkMasterFields: EmployeeID

when you move to each record in sfrmEmployees, the related records in
sfrmEmployeeOvertime will display in the subform ChildOvertime.

now create an unbound form, to serve as the "main" form. add two unbound
combo boxes, as

cboUnit (with RowSource based on a tblUnits - which would be a list of
all
units that you're tracking employees for.)
cboPayPeriod (with RowSource based on tblPayPeriods.)

add a subform control, i'll call it ChildEmployees. set its' properties
as
follows

SourceObject: sfrmEmployees
LinkChildFields: AssignedUnit
LinkMasterFields: cboUnit

now each time you select a unit from cboUnit in the main form, the
subform
ChildEmployees will display that unit's employees.

go back to sfrmOvertime in the database window, and open it in Design
view.
add the following code to the form's BeforeUpdate event procedure, as

Me!PayPeriodID = Me.Parent.Parent!cboPayPeriod

here's how it all works: the supervisor opens the main form, selects a
pay
period from cboPayPeriod, and selects a unit from cboUnit. the subform
populates with all the employee records assigned to that unit. the
supervisor moves through the subform records to an employee he wants to
add
overtime for, then he moves into the "sub" subform, and enters the
G22LineNumber and the NumHours, etc.
he does NOT need to add the OvertimeID because that should be generated
automatically as the primary key field.
he does NOT need to add the EmployeeID because that should be
automatically
added via the link between the Employees subform and the Overtime "sub"
subform.
he does NOT need to add the PayPeriodID, because that will be
automatically
added by the code in the "sub" subform's BeforeUpdate event.

whew, that's a loooong explanation. but if you set it up step by step, i
think you'll find it easy and pretty straightforward. and it provides
the
quick, efficient data entry you wanted for your supervisor users.

hth


"NWO" wrote in message
...
Hello Tina (again...

Yes, the sole purpose for the current Pay Period scheme is to group
the
Overtime detail records and tie to a specific Pay Period. With this
being
the case, do you think it is not overburdonsome for the user to just
click
on
the appropaite Pay Period and then continue on with the data entry for
each
user (there is a max of twenty user per supervisor and about 2 to 3
records
per employee, per pay period)? Because if this is a the case., then
I'm
already set. At any rate, here are the tables with fields:

tblEmployee
EmployeeID (PK)
Name
Grade
PositionTitle
AssingedUnit

tblPayPeriod
PayPeriodID (PK)
EmployeeID (FK)
PayPeriod (i.e. 21-1,22-2,23-1...)

tblOvertimeDetailRecords
OvertimeID (PK)
PayPeriodID (FK)
G22LineNUmber (Drop down selection list)
NumApprovals
NumDenials
NumHours

Note that there is no direct realtionship between the tblEmployee and
tblOvertime. For each Pay Period record in the tblPayPeriod table,
these
is
a correpsnding variable number of OT detail records for that Pay
Period
and
the associated employee based on ther EmployeeID FK in the Pay Period
table.
Relationship is a One to many from EmployeeID to Pay PeriodID, and a
one
to
many from PayPeriodID to OvertimeID.

Your input is always appreciated. I'm not the coding type, but am
quickly
learning. I am more concerned with the design issue at this point,
but if
you know of some code that will work, please advise. My guess,
though, is
that if there is a problem with the above approach, the issue is
design
related.

Thank you again Tina.

NWO

-----

"tina" wrote:

you're welcome

re your "new" question: from a data entry standpoint, you could
probably
come up with a creative "outside the box" form setup to facilitate
the
data
entry the way you describe. in fact, even as i write this, a few
ideas
are
swimming around in my head.

but, i think i'd take a look at your table structure first.

I just don't really like
the idea of the user having to create a new Pay Period record for
every
employee who works overtime, although once the Pay Period record
is
created,
the user can then just add addtional ocvetime detail records.

is the purpose of the PayPeriod table simply to "group" overtime
records? if
so, that's not necessarily bad - i just need to understand the
tables
structure better. can you post the fields in in the PayPeriod and
Overtime
tables, please, as

PayPeriod
PPID (pk)
EmployeeID (fk)
NextFieldName
NextFieldName

and explain what each field is for, unless the fieldname makes it
obvious.

hth


"NWO" wrote in message
...
Thank you very much Tina.

You explain things in a very clear and easy to understadn manner.

Can you tackle this one:

I have a database that collects overtime data on a pay period
basis.
The
relationship is as folows:

Employee table (PK = Employee ID (auto number))
Pay Period table (PK = PPID (autonumber), with EmployeeID as a FK)
Overtime table (PK = OvertimeID (auto number), with PPID as the

Almost there...

As it turns out, I made a couple of typos and the forms now work as designed
(thank you). There is still a problem, however. When you enter a record for
a particular pay period, exit the form, reopen the form, and select the same
unit and a different pay period, the Overtime detail record still shows the
data that was entered under a different pay period for the same employee. It
was my intent to only show records for a given pay period as entered - any
ideas how to fix this?

Thanks again Tina.

Mark
------------------------

"NWO" wrote:

Thank you Tina.

I followed your example in your previous post and got things working all the
way to the Main form. The main form displays the Unit and Pay Period combo
boxes, and they have proper values in the drop down list, but nothing happens
when the selections are made. Also, when I open the form, I get a parameter
box asking for a Unit, and then a Pay Period, then the form appears,
selections are made via the combo boxes, but nothing appears in the sub form.
Any suggestions. I'm pretty sure that I folowed you procedures very
carefully. I also re-did the relationships relating the tblEmployeeOveetime
to tblEmployee with EmployeeID and relating the tblPayPeriod PayPeriodID to
tblEmployeeOveretime PayPeriodID.

Mark
-----
"tina" wrote:

Mark, my warning was about Lookup *fields* in tables, not about "lookup"
tables (i call them "supporting" tables, so as to not confuse them with
Lookup fields). databases often have numerous supporting tables. they're
typically "parent" tables, with a one-to-many link to one or more data
tables, that simply provide a list of valid values for use in the linked
data table(s) . usually, the values in a supporting table are not added
to/edited/deleted very often. and typically the kind of table that i call a
supporting table has only two fields, a Number field that serves as a
primary key, and a value field for whatever values are being listed. lookup
tables are useful in three ways: 1) they ensure that valid, correctly
spelled values are available to the user, in forms; 2) they allow for more
efficient data entry in forms - generally picking from a pre-defined combo
box droplist is easier and faster than typing a value manually and checking
that it's spelled correctly; 3) they provide standardized values, that are
meaningful to the company, which can be used to "slice 'n dice" the data for
statistical analysis.

the tblPayPeriods, that i suggested in my previous post, is an example of a
supporting table. some others i've used a tblTitles (Mr., Mrs., Ms,
Rev., Dr., etc); tblPhoneTypes (Home, Office, Business, Cell, Fax, Pager,
etc); tblCategories (any list of categories that compartmentalize or "label"
data in a way that is meaningful to the company using the database);
tblStates (a list of the U.S. states with a field for the two-character USPS
abbreviation, and a field for the full name of the state/territory).

hth


"NWO" wrote in message
...
Tina, regarding the lookup table business, would you then recommend that
the
values just be entered in the field even if there are several duplicate
values, or just do a better job at normalizing the table structure so that
these repeating fileds are located in another table (I suspect you're
going
to go with the latter, which means I'll have to make significant chmnages
to
my tables because most of the fileds are indeed based on lookup tables,
but
that's OK because I want to do the right thing at the desing stage and not
pay for poor desing latter). Your coments are always welcome.

Mark
-------------

"tina" wrote:

well, i believe i would simplify the table structure (and hence the data
entry issues), as

no change to tblEmployees

tblPayPeriods
PayPeriodID
PayPeriod
note: this changes the table to be simply a list of all pay periods
(you
could add the coming years pay periods at the beginning of each year,
for
instance, so they're available all year long for data entry purposes).
the
table is *not* linked to tblEmployees.
*also note*: you should NOT give a field in a table the *exact* same
name
as the table's name. recommend you change one or the other, for example
by
making the table name plural, as in the example above.

tblEmployeeOvertime
OvertimeID (pk)
EmployeeID (fk)
PayPeriodID (fk)
G22LineNUmber
NumApprovals
NumDenials
NumHours

note: if you have any Lookup fields in any of your tables, recommend
you
get rid of them. see http://www.mvps.org/access/lookupfields.htm for
reasons
why.

okay, now to address the data entry scenario you previously described as
what you "really want to do":

Supervisors first selectes a PAy Period form a drop down list.
Supervisor
then selects a Unit name form another drop down list and all of the
employees
assinged to that unit appear in a subform. Supervisor then selects an
employee and either existing Overtime record(s) record appers for the
Pay
Period selected or the supervisor has the ability to add new Overtime
detail
records for the selected pay period.

suggest the following:

create a form, bound to tblEmployees, i'll call it sfrmEmployees, and
set
the form's DefaultView property to SingleForm.

create another form, bound to tblEmployeeOvertime, i'll call it
sfrmOvertime, and set the form's DefaultView to ContinuousForms or
Datasheet.

open sfrmEmployees and add a subform control, i'll call it
ChildOvertime.
set its' properties as follows

SourceObject: sfrmOvertime
LinkChildFields: EmployeeID
LinkMasterFields: EmployeeID

when you move to each record in sfrmEmployees, the related records in
sfrmEmployeeOvertime will display in the subform ChildOvertime.

now create an unbound form, to serve as the "main" form. add two unbound
combo boxes, as

cboUnit (with RowSource based on a tblUnits - which would be a list of
all
units that you're tracking employees for.)
cboPayPeriod (with RowSource based on tblPayPeriods.)

add a subform control, i'll call it ChildEmployees. set its' properties
as
follows

SourceObject: sfrmEmployees
LinkChildFields: AssignedUnit
LinkMasterFields: cboUnit

now each time you select a unit from cboUnit in the main form, the
subform
ChildEmployees will display that unit's employees.

go back to sfrmOvertime in the database window, and open it in Design
view.
add the following code to the form's BeforeUpdate event procedure, as

Me!PayPeriodID = Me.Parent.Parent!cboPayPeriod

here's how it all works: the supervisor opens the main form, selects a
pay
period from cboPayPeriod, and selects a unit from cboUnit. the subform
populates with all the employee records assigned to that unit. the
supervisor moves through the subform records to an employee he wants to
add
overtime for, then he moves into the "sub" subform, and enters the
G22LineNumber and the NumHours, etc.
he does NOT need to add the OvertimeID because that should be generated
automatically as the primary key field.
he does NOT need to add the EmployeeID because that should be
automatically
added via the link between the Employees subform and the Overtime "sub"
subform.
he does NOT need to add the PayPeriodID, because that will be
automatically
added by the code in the "sub" subform's BeforeUpdate event.

whew, that's a loooong explanation. but if you set it up step by step, i
think you'll find it easy and pretty straightforward. and it provides
the
quick, efficient data entry you wanted for your supervisor users.

hth


"NWO" wrote in message
...
Hello Tina (again...

Yes, the sole purpose for the current Pay Period scheme is to group
the
Overtime detail records and tie to a specific Pay Period. With this
being
the case, do you think it is not overburdonsome for the user to just
click
on
the appropaite Pay Period and then continue on with the data entry for
each
user (there is a max of twenty user per supervisor and about 2 to 3
records
per employee, per pay period)? Because if this is a the case., then
I'm
already set. At any rate, here are the tables with fields:

tblEmployee
EmployeeID (PK)
Name
Grade
PositionTitle
AssingedUnit

tblPayPeriod
PayPeriodID (PK)
EmployeeID (FK)
PayPeriod (i.e. 21-1,22-2,23-1...)

tblOvertimeDetailRecords
OvertimeID (PK)
PayPeriodID (FK)
G22LineNUmber (Drop down selection list)
NumApprovals
NumDenials
NumHours

Note that there is no direct realtionship between the tblEmployee and
tblOvertime. For each Pay Period record in the tblPayPeriod table,
these
is
a correpsnding variable number of OT detail records for that Pay
Period
and
the associated employee based on ther EmployeeID FK in the Pay Period
table.
Relationship is a One to many from EmployeeID to Pay PeriodID, and a
one
to
many from PayPeriodID to OvertimeID.

Your input is always appreciated. I'm not the coding type, but am
quickly
learning. I am more concerned with the design issue at this point,
but if
you know of some code that will work, please advise. My guess,
though, is
that if there is a problem with the above approach, the issue is
design
related.

Thank you again Tina.

NWO

-----

"tina" wrote:

you're welcome

re your "new" question: from a data entry standpoint, you could
probably
come up with a creative "outside the box" form setup to facilitate
the
data
entry the way you describe. in fact, even as i write this, a few
ideas
are
swimming around in my head.

but, i think i'd take a look at your table structure first.

I just don't really like
the idea of the user having to create a new Pay Period record for
every
employee who works overtime, although once the Pay Period record
is
created,
the user can then just add addtional ocvetime detail records.

is the purpose of the PayPeriod table simply to "group" overtime
records? if
so, that's not necessarily bad - i just need to understand the
tables
structure better. can you post the fields in in the PayPeriod and
Overtime
tables, please, as

PayPeriod
PPID (pk)
EmployeeID (fk)
NextFieldName
NextFieldName

and explain what each field is for, unless the fieldname makes it
obvious.

hth

this is turning into a really long thread, Mark. rather than go thru another
extended Q&A, i'm willing to take a look at your database and troubleshoot
the solution, or create a "demo" db and send it to you, so you can see an
example of the setup. if you decide to pursue either option, post back with
an email address, disguised to foil the spammers, and tell me which option
you choose.


"NWO" wrote in message
...
Thank you Tina.

I followed your example in your previous post and got things working all
the
way to the Main form. The main form displays the Unit and Pay Period
combo
boxes, and they have proper values in the drop down list, but nothing
happens
when the selections are made. Also, when I open the form, I get a
parameter
box asking for a Unit, and then a Pay Period, then the form appears,
selections are made via the combo boxes, but nothing appears in the sub
form.
Any suggestions. I'm pretty sure that I folowed you procedures very
carefully. I also re-did the relationships relating the
tblEmployeeOveetime
to tblEmployee with EmployeeID and relating the tblPayPeriod PayPeriodID
to
tblEmployeeOveretime PayPeriodID.

Mark
-----
"tina" wrote:

Mark, my warning was about Lookup *fields* in tables, not about "lookup"
tables (i call them "supporting" tables, so as to not confuse them with
Lookup fields). databases often have numerous supporting tables. they're
typically "parent" tables, with a one-to-many link to one or more data
tables, that simply provide a list of valid values for use in the linked
data table(s) . usually, the values in a supporting table are not added
to/edited/deleted very often. and typically the kind of table that i
call a
supporting table has only two fields, a Number field that serves as a
primary key, and a value field for whatever values are being listed.
lookup
tables are useful in three ways: 1) they ensure that valid, correctly
spelled values are available to the user, in forms; 2) they allow for
more
efficient data entry in forms - generally picking from a pre-defined
combo
box droplist is easier and faster than typing a value manually and
checking
that it's spelled correctly; 3) they provide standardized values, that
are
meaningful to the company, which can be used to "slice 'n dice" the data
for
statistical analysis.

the tblPayPeriods, that i suggested in my previous post, is an example
of a
supporting table. some others i've used a tblTitles (Mr., Mrs., Ms,
Rev., Dr., etc); tblPhoneTypes (Home, Office, Business, Cell, Fax,
Pager,
etc); tblCategories (any list of categories that compartmentalize or
"label"
data in a way that is meaningful to the company using the database);
tblStates (a list of the U.S. states with a field for the two-character
USPS
abbreviation, and a field for the full name of the state/territory).

hth


"NWO" wrote in message
...
Tina, regarding the lookup table business, would you then recommend
that
the
values just be entered in the field even if there are several
duplicate
values, or just do a better job at normalizing the table structure so
that
these repeating fileds are located in another table (I suspect you're
going
to go with the latter, which means I'll have to make significant
chmnages
to
my tables because most of the fileds are indeed based on lookup
tables,
but
that's OK because I want to do the right thing at the desing stage and
not
pay for poor desing latter). Your coments are always welcome.

Mark
-------------

"tina" wrote:

well, i believe i would simplify the table structure (and hence the
data
entry issues), as

no change to tblEmployees

tblPayPeriods
PayPeriodID
PayPeriod
note: this changes the table to be simply a list of all pay periods
(you
could add the coming years pay periods at the beginning of each
year,
for
instance, so they're available all year long for data entry
purposes).
the
table is *not* linked to tblEmployees.
*also note*: you should NOT give a field in a table the *exact*
same
name
as the table's name. recommend you change one or the other, for
example
by
making the table name plural, as in the example above.

tblEmployeeOvertime
OvertimeID (pk)
EmployeeID (fk)
PayPeriodID (fk)
G22LineNUmber
NumApprovals
NumDenials
NumHours

note: if you have any Lookup fields in any of your tables,
recommend
you
get rid of them. see http://www.mvps.org/access/lookupfields.htm for
reasons
why.

okay, now to address the data entry scenario you previously
described as
what you "really want to do":

Supervisors first selectes a PAy Period form a drop down list.
Supervisor
then selects a Unit name form another drop down list and all of
the
employees
assinged to that unit appear in a subform. Supervisor then
selects an
employee and either existing Overtime record(s) record appers for
the
Pay
Period selected or the supervisor has the ability to add new
Overtime
detail
records for the selected pay period.

suggest the following:

create a form, bound to tblEmployees, i'll call it sfrmEmployees,
and
set
the form's DefaultView property to SingleForm.

create another form, bound to tblEmployeeOvertime, i'll call it
sfrmOvertime, and set the form's DefaultView to ContinuousForms or
Datasheet.

open sfrmEmployees and add a subform control, i'll call it
ChildOvertime.
set its' properties as follows

SourceObject: sfrmOvertime
LinkChildFields: EmployeeID
LinkMasterFields: EmployeeID

when you move to each record in sfrmEmployees, the related records
in
sfrmEmployeeOvertime will display in the subform ChildOvertime.

now create an unbound form, to serve as the "main" form. add two
unbound
combo boxes, as

cboUnit (with RowSource based on a tblUnits - which would be a list
of
all
units that you're tracking employees for.)
cboPayPeriod (with RowSource based on tblPayPeriods.)

add a subform control, i'll call it ChildEmployees. set its'
properties
as
follows

SourceObject: sfrmEmployees
LinkChildFields: AssignedUnit
LinkMasterFields: cboUnit

now each time you select a unit from cboUnit in the main form, the
subform
ChildEmployees will display that unit's employees.

go back to sfrmOvertime in the database window, and open it in
Design
view.
add the following code to the form's BeforeUpdate event procedure,
as

Me!PayPeriodID = Me.Parent.Parent!cboPayPeriod

here's how it all works: the supervisor opens the main form,
selects a
pay
period from cboPayPeriod, and selects a unit from cboUnit. the
subform
populates with all the employee records assigned to that unit. the
supervisor moves through the subform records to an employee he wants
to
add
overtime for, then he moves into the "sub" subform, and enters the
G22LineNumber and the NumHours, etc.
he does NOT need to add the OvertimeID because that should be
generated
automatically as the primary key field.
he does NOT need to add the EmployeeID because that should be
automatically
added via the link between the Employees subform and the Overtime
"sub"
subform.
he does NOT need to add the PayPeriodID, because that will be
automatically
added by the code in the "sub" subform's BeforeUpdate event.

whew, that's a loooong explanation. but if you set it up step by
step, i
think you'll find it easy and pretty straightforward. and it
provides
the
quick, efficient data entry you wanted for your supervisor users.

hth


"NWO" wrote in message
...
Hello Tina (again...

Yes, the sole purpose for the current Pay Period scheme is to
group
the
Overtime detail records and tie to a specific Pay Period. With
this
being
the case, do you think it is not overburdonsome for the user to
just
click
on
the appropaite Pay Period and then continue on with the data entry
for
each
user (there is a max of twenty user per supervisor and about 2 to
3
records
per employee, per pay period)? Because if this is a the case.,
then
I'm
already set. At any rate, here are the tables with fields:

tblEmployee
EmployeeID (PK)
Name
Grade
PositionTitle
AssingedUnit

tblPayPeriod
PayPeriodID (PK)
EmployeeID (FK)
PayPeriod (i.e. 21-1,22-2,23-1...)

tblOvertimeDetailRecords
OvertimeID (PK)
PayPeriodID (FK)
G22LineNUmber (Drop down selection list)
NumApprovals
NumDenials
NumHours

Note that there is no direct realtionship between the tblEmployee
and
tblOvertime. For each Pay Period record in the tblPayPeriod
table,
these
is
a correpsnding variable number of OT detail records for that Pay
Period
and
the associated employee based on ther EmployeeID FK in the Pay
Period
table.
Relationship is a One to many from EmployeeID to Pay PeriodID, and
a
one
to
many from PayPeriodID to OvertimeID.

Your input is always appreciated. I'm not the coding type, but am
quickly
learning. I am more concerned with the design issue at this
point,
but if
you know of some code that will work, please advise. My guess,
though, is
that if there is a problem with the above approach, the issue is
design
related.

Thank you again Tina.

NWO

-----

"tina" wrote:

you're welcome

re your "new" question: from a data entry standpoint, you could
probably
come up with a creative "outside the box" form setup to
facilitate
the
data
entry the way you describe. in fact, even as i write this, a few
ideas
are
swimming around in my head.

but, i think i'd take a look at your table structure first.

I just don't really like
the idea of the user having to create a new Pay Period record
for
every
employee who works overtime, although once the Pay Period
record
is
created,
the user can then just add addtional ocvetime detail records.

is the purpose of the PayPeriod table simply to "group" overtime
records? if
so, that's not necessarily bad - i just need to understand the
tables
structure better. can you post the fields in in the PayPeriod
and
Overtime
tables, please, as

PayPeriod
PPID (pk)
EmployeeID (fk)
NextFieldName
NextFieldName

and explain what each field is for, unless the fieldname makes
it
obvious.

hth


"NWO" wrote in message
...
Thank you very much Tina.

You explain things in a very clear and easy to understadn
manner.

Can you tackle this one:

I have a database that collects overtime data on a pay period
basis.
The
relationship is as folows:

Employee table (PK = Employee ID (auto number))
Pay Period table (PK = PPID (autonumber), with EmployeeID as a
FK)
Overtime table (PK = OvertimeID (auto number), with PPID as
the

Thank you Tina.

I think a Demo would be nice address is at end of this thread.

Thank you.

Mark

"tina" wrote:

this is turning into a really long thread, Mark. rather than go thru another
extended Q&A, i'm willing to take a look at your database and troubleshoot
the solution, or create a "demo" db and send it to you, so you can see an
example of the setup. if you decide to pursue either option, post back with
an email address, disguised to foil the spammers, and tell me which option
you choose.


"NWO" wrote in message
...
Thank you Tina.

I followed your example in your previous post and got things working all
the
way to the Main form. The main form displays the Unit and Pay Period
combo
boxes, and they have proper values in the drop down list, but nothing
happens
when the selections are made. Also, when I open the form, I get a
parameter
box asking for a Unit, and then a Pay Period, then the form appears,
selections are made via the combo boxes, but nothing appears in the sub
form.
Any suggestions. I'm pretty sure that I folowed you procedures very
carefully. I also re-did the relationships relating the
tblEmployeeOveetime
to tblEmployee with EmployeeID and relating the tblPayPeriod PayPeriodID
to
tblEmployeeOveretime PayPeriodID.

Mark
-----
"tina" wrote:

Mark, my warning was about Lookup *fields* in tables, not about "lookup"
tables (i call them "supporting" tables, so as to not confuse them with
Lookup fields). databases often have numerous supporting tables. they're
typically "parent" tables, with a one-to-many link to one or more data
tables, that simply provide a list of valid values for use in the linked
data table(s) . usually, the values in a supporting table are not added
to/edited/deleted very often. and typically the kind of table that i
call a
supporting table has only two fields, a Number field that serves as a
primary key, and a value field for whatever values are being listed.
lookup
tables are useful in three ways: 1) they ensure that valid, correctly
spelled values are available to the user, in forms; 2) they allow for
more
efficient data entry in forms - generally picking from a pre-defined
combo
box droplist is easier and faster than typing a value manually and
checking
that it's spelled correctly; 3) they provide standardized values, that
are
meaningful to the company, which can be used to "slice 'n dice" the data
for
statistical analysis.

the tblPayPeriods, that i suggested in my previous post, is an example
of a
supporting table. some others i've used a tblTitles (Mr., Mrs., Ms,
Rev., Dr., etc); tblPhoneTypes (Home, Office, Business, Cell, Fax,
Pager,
etc); tblCategories (any list of categories that compartmentalize or
"label"
data in a way that is meaningful to the company using the database);
tblStates (a list of the U.S. states with a field for the two-character
USPS
abbreviation, and a field for the full name of the state/territory).

hth


"NWO" wrote in message
...
Tina, regarding the lookup table business, would you then recommend
that
the
values just be entered in the field even if there are several
duplicate
values, or just do a better job at normalizing the table structure so
that
these repeating fileds are located in another table (I suspect you're
going
to go with the latter, which means I'll have to make significant
chmnages
to
my tables because most of the fileds are indeed based on lookup
tables,
but
that's OK because I want to do the right thing at the desing stage and
not
pay for poor desing latter). Your coments are always welcome.

Mark
-------------

"tina" wrote:

well, i believe i would simplify the table structure (and hence the
data
entry issues), as

no change to tblEmployees

tblPayPeriods
PayPeriodID
PayPeriod
note: this changes the table to be simply a list of all pay periods
(you
could add the coming years pay periods at the beginning of each
year,
for
instance, so they're available all year long for data entry
purposes).
the
table is *not* linked to tblEmployees.
*also note*: you should NOT give a field in a table the *exact*
same
name
as the table's name. recommend you change one or the other, for
example
by
making the table name plural, as in the example above.

tblEmployeeOvertime
OvertimeID (pk)
EmployeeID (fk)
PayPeriodID (fk)
G22LineNUmber
NumApprovals
NumDenials
NumHours

note: if you have any Lookup fields in any of your tables,
recommend
you
get rid of them. see http://www.mvps.org/access/lookupfields.htm for
reasons
why.

okay, now to address the data entry scenario you previously
described as
what you "really want to do":

Supervisors first selectes a PAy Period form a drop down list.
Supervisor
then selects a Unit name form another drop down list and all of
the
employees
assinged to that unit appear in a subform. Supervisor then
selects an
employee and either existing Overtime record(s) record appers for
the
Pay
Period selected or the supervisor has the ability to add new
Overtime
detail
records for the selected pay period.

suggest the following:

create a form, bound to tblEmployees, i'll call it sfrmEmployees,
and
set
the form's DefaultView property to SingleForm.

create another form, bound to tblEmployeeOvertime, i'll call it
sfrmOvertime, and set the form's DefaultView to ContinuousForms or
Datasheet.

open sfrmEmployees and add a subform control, i'll call it
ChildOvertime.
set its' properties as follows

SourceObject: sfrmOvertime
LinkChildFields: EmployeeID
LinkMasterFields: EmployeeID

when you move to each record in sfrmEmployees, the related records
in
sfrmEmployeeOvertime will display in the subform ChildOvertime.

now create an unbound form, to serve as the "main" form. add two
unbound
combo boxes, as

cboUnit (with RowSource based on a tblUnits - which would be a list
of
all
units that you're tracking employees for.)
cboPayPeriod (with RowSource based on tblPayPeriods.)

add a subform control, i'll call it ChildEmployees. set its'
properties
as
follows

SourceObject: sfrmEmployees
LinkChildFields: AssignedUnit
LinkMasterFields: cboUnit

now each time you select a unit from cboUnit in the main form, the
subform
ChildEmployees will display that unit's employees.

go back to sfrmOvertime in the database window, and open it in
Design
view.
add the following code to the form's BeforeUpdate event procedure,
as

Me!PayPeriodID = Me.Parent.Parent!cboPayPeriod

here's how it all works: the supervisor opens the main form,
selects a
pay
period from cboPayPeriod, and selects a unit from cboUnit. the
subform
populates with all the employee records assigned to that unit. the
supervisor moves through the subform records to an employee he wants
to
add
overtime for, then he moves into the "sub" subform, and enters the
G22LineNumber and the NumHours, etc.
he does NOT need to add the OvertimeID because that should be
generated
automatically as the primary key field.
he does NOT need to add the EmployeeID because that should be
automatically
added via the link between the Employees subform and the Overtime
"sub"
subform.
he does NOT need to add the PayPeriodID, because that will be
automatically
added by the code in the "sub" subform's BeforeUpdate event.

whew, that's a loooong explanation. but if you set it up step by
step, i
think you'll find it easy and pretty straightforward. and it
provides
the
quick, efficient data entry you wanted for your supervisor users.

hth


"NWO" wrote in message
...
Hello Tina (again...

Yes, the sole purpose for the current Pay Period scheme is to
group
the
Overtime detail records and tie to a specific Pay Period. With
this
being
the case, do you think it is not overburdonsome for the user to
just
click
on
the appropaite Pay Period and then continue on with the data entry
for
each
user (there is a max of twenty user per supervisor and about 2 to
3
records
per employee, per pay period)? Because if this is a the case.,
then
I'm
already set. At any rate, here are the tables with fields:

tblEmployee
EmployeeID (PK)
Name
Grade
PositionTitle
AssingedUnit

tblPayPeriod
PayPeriodID (PK)
EmployeeID (FK)
PayPeriod (i.e. 21-1,22-2,23-1...)

tblOvertimeDetailRecords
OvertimeID (PK)
PayPeriodID (FK)
G22LineNUmber (Drop down selection list)
NumApprovals
NumDenials
NumHours

Note that there is no direct realtionship between the tblEmployee
and
tblOvertime. For each Pay Period record in the tblPayPeriod
table,
these
is
a correpsnding variable number of OT detail records for that Pay
Period

i'll put together a small demo with the tables/fields we've been discussing,
Mark, and email it to you probably on Sat 11/19. (btw, i hope you don't get
spammed to death; it's generally not a good idea to post a "real" email
address in these newsgroups unless you disguise it to foil the spammers.)


"NWO" wrote in message
...
Thank you Tina.

I think a Demo would be nice address is at end of this thread.

Thank you.

Mark

"tina" wrote:

this is turning into a really long thread, Mark. rather than go thru
another
extended Q&A, i'm willing to take a look at your database and
troubleshoot
the solution, or create a "demo" db and send it to you, so you can see
an
example of the setup. if you decide to pursue either option, post back
with
an email address, disguised to foil the spammers, and tell me which
option
you choose.


"NWO" wrote in message
...
Thank you Tina.

I followed your example in your previous post and got things working
all
the
way to the Main form. The main form displays the Unit and Pay Period
combo
boxes, and they have proper values in the drop down list, but nothing
happens
when the selections are made. Also, when I open the form, I get a
parameter
box asking for a Unit, and then a Pay Period, then the form appears,
selections are made via the combo boxes, but nothing appears in the
sub
form.
Any suggestions. I'm pretty sure that I folowed you procedures very
carefully. I also re-did the relationships relating the
tblEmployeeOveetime
to tblEmployee with EmployeeID and relating the tblPayPeriod
PayPeriodID
to
tblEmployeeOveretime PayPeriodID.

Mark
-----
"tina" wrote:

Mark, my warning was about Lookup *fields* in tables, not about
"lookup"
tables (i call them "supporting" tables, so as to not confuse them
with
Lookup fields). databases often have numerous supporting tables.
they're
typically "parent" tables, with a one-to-many link to one or more
data
tables, that simply provide a list of valid values for use in the
linked
data table(s) . usually, the values in a supporting table are not
added
to/edited/deleted very often. and typically the kind of table that i
call a
supporting table has only two fields, a Number field that serves as
a
primary key, and a value field for whatever values are being listed.
lookup
tables are useful in three ways: 1) they ensure that valid,
correctly
spelled values are available to the user, in forms; 2) they allow
for
more
efficient data entry in forms - generally picking from a pre-defined
combo
box droplist is easier and faster than typing a value manually and
checking
that it's spelled correctly; 3) they provide standardized values,
that
are
meaningful to the company, which can be used to "slice 'n dice" the
data
for
statistical analysis.

the tblPayPeriods, that i suggested in my previous post, is an
example
of a
supporting table. some others i've used a tblTitles (Mr., Mrs.,
Ms,
Rev., Dr., etc); tblPhoneTypes (Home, Office, Business, Cell, Fax,
Pager,
etc); tblCategories (any list of categories that compartmentalize or
"label"
data in a way that is meaningful to the company using the database);
tblStates (a list of the U.S. states with a field for the
two-character
USPS
abbreviation, and a field for the full name of the state/territory).

hth


"NWO" wrote in message
...
Tina, regarding the lookup table business, would you then
recommend
that
the
values just be entered in the field even if there are several
duplicate
values, or just do a better job at normalizing the table structure
so
that
these repeating fileds are located in another table (I suspect
you're
going
to go with the latter, which means I'll have to make significant
chmnages
to
my tables because most of the fileds are indeed based on lookup
tables,
but
that's OK because I want to do the right thing at the desing stage
and
not
pay for poor desing latter). Your coments are always welcome.

Mark
-------------

"tina" wrote:

well, i believe i would simplify the table structure (and hence
the
data
entry issues), as

no change to tblEmployees

tblPayPeriods
PayPeriodID
PayPeriod
note: this changes the table to be simply a list of all pay
periods
(you
could add the coming years pay periods at the beginning of each
year,
for
instance, so they're available all year long for data entry
purposes).
the
table is *not* linked to tblEmployees.
*also note*: you should NOT give a field in a table the *exact*
same
name
as the table's name. recommend you change one or the other, for
example
by
making the table name plural, as in the example above.

tblEmployeeOvertime
OvertimeID (pk)
EmployeeID (fk)
PayPeriodID (fk)
G22LineNUmber
NumApprovals
NumDenials
NumHours

note: if you have any Lookup fields in any of your tables,
recommend
you
get rid of them. see http://www.mvps.org/access/lookupfields.htm
for
reasons
why.

okay, now to address the data entry scenario you previously
described as
what you "really want to do":

Supervisors first selectes a PAy Period form a drop down list.
Supervisor
then selects a Unit name form another drop down list and all
of
the
employees
assinged to that unit appear in a subform. Supervisor then
selects an
employee and either existing Overtime record(s) record appers
for
the
Pay
Period selected or the supervisor has the ability to add new
Overtime
detail
records for the selected pay period.

suggest the following:

create a form, bound to tblEmployees, i'll call it
sfrmEmployees,
and
set
the form's DefaultView property to SingleForm.

create another form, bound to tblEmployeeOvertime, i'll call it
sfrmOvertime, and set the form's DefaultView to ContinuousForms
or
Datasheet.

open sfrmEmployees and add a subform control, i'll call it
ChildOvertime.
set its' properties as follows

SourceObject: sfrmOvertime
LinkChildFields: EmployeeID
LinkMasterFields: EmployeeID

when you move to each record in sfrmEmployees, the related
records
in
sfrmEmployeeOvertime will display in the subform ChildOvertime.

now create an unbound form, to serve as the "main" form. add two
unbound
combo boxes, as

cboUnit (with RowSource based on a tblUnits - which would be a
list
of
all
units that you're tracking employees for.)
cboPayPeriod (with RowSource based on tblPayPeriods.)

add a subform control, i'll call it ChildEmployees. set its'
properties
as
follows

SourceObject: sfrmEmployees
LinkChildFields: AssignedUnit
LinkMasterFields: cboUnit

now each time you select a unit from cboUnit in the main form,
the
subform
ChildEmployees will display that unit's employees.

go back to sfrmOvertime in the database window, and open it in
Design
view.
add the following code to the form's BeforeUpdate event
procedure,
as

Me!PayPeriodID = Me.Parent.Parent!cboPayPeriod

here's how it all works: the supervisor opens the main form,
selects a
pay
period from cboPayPeriod, and selects a unit from cboUnit. the
subform
populates with all the employee records assigned to that unit.
the
supervisor moves through the subform records to an employee he
wants
to
add
overtime for, then he moves into the "sub" subform, and enters
the
G22LineNumber and the NumHours, etc.
he does NOT need to add the OvertimeID because that should be
generated
automatically as the primary key field.
he does NOT need to add the EmployeeID because that should be
automatically
added via the link between the Employees subform and the
Overtime
"sub"
subform.
he does NOT need to add the PayPeriodID, because that will be
automatically
added by the code in the "sub" subform's BeforeUpdate event.

whew, that's a loooong explanation. but if you set it up step by
step, i
think you'll find it easy and pretty straightforward. and it
provides
the
quick, efficient data entry you wanted for your supervisor
users.

hth


"NWO" wrote in message
...
Hello Tina (again...

Yes, the sole purpose for the current Pay Period scheme is to
group
the
Overtime detail records and tie to a specific Pay Period.
With
this
being
the case, do you think it is not overburdonsome for the user
to
just
click
on
the appropaite Pay Period and then continue on with the data
entry
for
each
user (there is a max of twenty user per supervisor and about 2
to
3
records
per employee, per pay period)? Because if this is a the
case.,
then
I'm
already set. At any rate, here are the tables with fields:

tblEmployee
EmployeeID (PK)
Name
Grade
PositionTitle
AssingedUnit

tblPayPeriod
PayPeriodID (PK)
EmployeeID (FK)
PayPeriod (i.e. 21-1,22-2,23-1...)

tblOvertimeDetailRecords
OvertimeID (PK)
PayPeriodID (FK)
G22LineNUmber (Drop down selection list)
NumApprovals
NumDenials
NumHours

Note that there is no direct realtionship between the
tblEmployee
and
tblOvertime. For each Pay Period record in the tblPayPeriod
table,
these
is
a correpsnding variable number of OT detail records for that
Pay
Period

well, that's a little more involved. i'll see if i can write a solution into
the demo db, and let you know if it's included, when i send the demo db to
you.


"NWO" wrote in message
...
Almost there...

As it turns out, I made a couple of typos and the forms now work as
designed
(thank you). There is still a problem, however. When you enter a record
for
a particular pay period, exit the form, reopen the form, and select the
same
unit and a different pay period, the Overtime detail record still shows
the
data that was entered under a different pay period for the same employee.
It
was my intent to only show records for a given pay period as entered - any
ideas how to fix this?

Thanks again Tina.

Mark
------------------------

"NWO" wrote:

Thank you Tina.

I followed your example in your previous post and got things working all
the
way to the Main form. The main form displays the Unit and Pay Period
combo
boxes, and they have proper values in the drop down list, but nothing
happens
when the selections are made. Also, when I open the form, I get a
parameter
box asking for a Unit, and then a Pay Period, then the form appears,
selections are made via the combo boxes, but nothing appears in the sub
form.
Any suggestions. I'm pretty sure that I folowed you procedures very
carefully. I also re-did the relationships relating the
tblEmployeeOveetime
to tblEmployee with EmployeeID and relating the tblPayPeriod PayPeriodID
to
tblEmployeeOveretime PayPeriodID.

Mark
-----
"tina" wrote:

Mark, my warning was about Lookup *fields* in tables, not about
"lookup"
tables (i call them "supporting" tables, so as to not confuse them
with
Lookup fields). databases often have numerous supporting tables.
they're
typically "parent" tables, with a one-to-many link to one or more data
tables, that simply provide a list of valid values for use in the
linked
data table(s) . usually, the values in a supporting table are not
added
to/edited/deleted very often. and typically the kind of table that i
call a
supporting table has only two fields, a Number field that serves as a
primary key, and a value field for whatever values are being listed.
lookup
tables are useful in three ways: 1) they ensure that valid, correctly
spelled values are available to the user, in forms; 2) they allow for
more
efficient data entry in forms - generally picking from a pre-defined
combo
box droplist is easier and faster than typing a value manually and
checking
that it's spelled correctly; 3) they provide standardized values, that
are
meaningful to the company, which can be used to "slice 'n dice" the
data for
statistical analysis.

the tblPayPeriods, that i suggested in my previous post, is an example
of a
supporting table. some others i've used a tblTitles (Mr., Mrs.,
Ms,
Rev., Dr., etc); tblPhoneTypes (Home, Office, Business, Cell, Fax,
Pager,
etc); tblCategories (any list of categories that compartmentalize or
"label"
data in a way that is meaningful to the company using the database);
tblStates (a list of the U.S. states with a field for the
two-character USPS
abbreviation, and a field for the full name of the state/territory).

hth


"NWO" wrote in message
...
Tina, regarding the lookup table business, would you then recommend
that
the
values just be entered in the field even if there are several
duplicate
values, or just do a better job at normalizing the table structure
so that
these repeating fileds are located in another table (I suspect
you're
going
to go with the latter, which means I'll have to make significant
chmnages
to
my tables because most of the fileds are indeed based on lookup
tables,
but
that's OK because I want to do the right thing at the desing stage
and not
pay for poor desing latter). Your coments are always welcome.

Mark
-------------

"tina" wrote:

well, i believe i would simplify the table structure (and hence
the data
entry issues), as

no change to tblEmployees

tblPayPeriods
PayPeriodID
PayPeriod
note: this changes the table to be simply a list of all pay
periods
(you
could add the coming years pay periods at the beginning of each
year,
for
instance, so they're available all year long for data entry
purposes).
the
table is *not* linked to tblEmployees.
*also note*: you should NOT give a field in a table the *exact*
same
name
as the table's name. recommend you change one or the other, for
example
by
making the table name plural, as in the example above.

tblEmployeeOvertime
OvertimeID (pk)
EmployeeID (fk)
PayPeriodID (fk)
G22LineNUmber
NumApprovals
NumDenials
NumHours

note: if you have any Lookup fields in any of your tables,
recommend
you
get rid of them. see http://www.mvps.org/access/lookupfields.htm
for
reasons
why.

okay, now to address the data entry scenario you previously
described as
what you "really want to do":

Supervisors first selectes a PAy Period form a drop down list.
Supervisor
then selects a Unit name form another drop down list and all of
the
employees
assinged to that unit appear in a subform. Supervisor then
selects an
employee and either existing Overtime record(s) record appers
for the
Pay
Period selected or the supervisor has the ability to add new
Overtime
detail
records for the selected pay period.

suggest the following:

create a form, bound to tblEmployees, i'll call it sfrmEmployees,
and
set
the form's DefaultView property to SingleForm.

create another form, bound to tblEmployeeOvertime, i'll call it
sfrmOvertime, and set the form's DefaultView to ContinuousForms or
Datasheet.

open sfrmEmployees and add a subform control, i'll call it
ChildOvertime.
set its' properties as follows

SourceObject: sfrmOvertime
LinkChildFields: EmployeeID
LinkMasterFields: EmployeeID

when you move to each record in sfrmEmployees, the related records
in
sfrmEmployeeOvertime will display in the subform ChildOvertime.

now create an unbound form, to serve as the "main" form. add two
unbound
combo boxes, as

cboUnit (with RowSource based on a tblUnits - which would be a
list of
all
units that you're tracking employees for.)
cboPayPeriod (with RowSource based on tblPayPeriods.)

add a subform control, i'll call it ChildEmployees. set its'
properties
as
follows

SourceObject: sfrmEmployees
LinkChildFields: AssignedUnit
LinkMasterFields: cboUnit

now each time you select a unit from cboUnit in the main form, the
subform
ChildEmployees will display that unit's employees.

go back to sfrmOvertime in the database window, and open it in
Design
view.
add the following code to the form's BeforeUpdate event procedure,
as

Me!PayPeriodID = Me.Parent.Parent!cboPayPeriod

here's how it all works: the supervisor opens the main form,
selects a
pay
period from cboPayPeriod, and selects a unit from cboUnit. the
subform
populates with all the employee records assigned to that unit. the
supervisor moves through the subform records to an employee he
wants to
add
overtime for, then he moves into the "sub" subform, and enters the
G22LineNumber and the NumHours, etc.
he does NOT need to add the OvertimeID because that should be
generated
automatically as the primary key field.
he does NOT need to add the EmployeeID because that should be
automatically
added via the link between the Employees subform and the Overtime
"sub"
subform.
he does NOT need to add the PayPeriodID, because that will be
automatically
added by the code in the "sub" subform's BeforeUpdate event.

whew, that's a loooong explanation. but if you set it up step by
step, i
think you'll find it easy and pretty straightforward. and it
provides
the
quick, efficient data entry you wanted for your supervisor users.

hth


"NWO" wrote in message
...
Hello Tina (again...

Yes, the sole purpose for the current Pay Period scheme is to
group
the
Overtime detail records and tie to a specific Pay Period. With
this
being
the case, do you think it is not overburdonsome for the user to
just
click
on
the appropaite Pay Period and then continue on with the data
entry for
each
user (there is a max of twenty user per supervisor and about 2
to 3
records
per employee, per pay period)? Because if this is a the case.,
then
I'm
already set. At any rate, here are the tables with fields:

tblEmployee
EmployeeID (PK)
Name
Grade
PositionTitle
AssingedUnit

tblPayPeriod
PayPeriodID (PK)
EmployeeID (FK)
PayPeriod (i.e. 21-1,22-2,23-1...)

tblOvertimeDetailRecords
OvertimeID (PK)
PayPeriodID (FK)
G22LineNUmber (Drop down selection list)
NumApprovals
NumDenials
NumHours

Note that there is no direct realtionship between the
tblEmployee and
tblOvertime. For each Pay Period record in the tblPayPeriod
table,
these
is
a correpsnding variable number of OT detail records for that Pay
Period
and
the associated employee based on ther EmployeeID FK in the Pay
Period
table.
Relationship is a One to many from EmployeeID to Pay PeriodID,
and a
one
to
many from PayPeriodID to OvertimeID.

Your input is always appreciated. I'm not the coding type, but
am
quickly
learning. I am more concerned with the design issue at this
point,
but if
you know of some code that will work, please advise. My guess,
though, is
that if there is a problem with the above approach, the issue is
design
related.

Thank you again Tina.

NWO

-----

"tina" wrote:

you're welcome

re your "new" question: from a data entry standpoint, you
could
probably
come up with a creative "outside the box" form setup to
facilitate
the
data
entry the way you describe. in fact, even as i write this, a
few
ideas
are
swimming around in my head.

but, i think i'd take a look at your table structure first.

I just don't really like
the idea of the user having to create a new Pay Period
record for
every
employee who works overtime, although once the Pay Period
record
is
created,
the user can then just add addtional ocvetime detail
records.

is the purpose of the PayPeriod table simply to "group"
overtime
records? if
so, that's not necessarily bad - i just need to understand the
tables
structure better. can you post the fields in in the PayPeriod
and
Overtime
tables, please, as

PayPeriod
PPID (pk)
EmployeeID (fk)
NextFieldName
NextFieldName

and explain what each field is for, unless the fieldname makes
it
obvious.

hth

Thank you Tina. I'm working today and I look forward to your e-mail.

How do you disguise an e-mail address?

Mark

"tina" wrote:

i'll put together a small demo with the tables/fields we've been discussing,
Mark, and email it to you probably on Sat 11/19. (btw, i hope you don't get
spammed to death; it's generally not a good idea to post a "real" email
address in these newsgroups unless you disguise it to foil the spammers.)


"NWO" wrote in message
...
Thank you Tina.

I think a Demo would be nice address is at end of this thread.

Thank you.

Mark

"tina" wrote:

this is turning into a really long thread, Mark. rather than go thru
another
extended Q&A, i'm willing to take a look at your database and
troubleshoot
the solution, or create a "demo" db and send it to you, so you can see
an
example of the setup. if you decide to pursue either option, post back
with
an email address, disguised to foil the spammers, and tell me which
option
you choose.


"NWO" wrote in message
...
Thank you Tina.

I followed your example in your previous post and got things working
all
the
way to the Main form. The main form displays the Unit and Pay Period
combo
boxes, and they have proper values in the drop down list, but nothing
happens
when the selections are made. Also, when I open the form, I get a
parameter
box asking for a Unit, and then a Pay Period, then the form appears,
selections are made via the combo boxes, but nothing appears in the
sub
form.
Any suggestions. I'm pretty sure that I folowed you procedures very
carefully. I also re-did the relationships relating the
tblEmployeeOveetime
to tblEmployee with EmployeeID and relating the tblPayPeriod
PayPeriodID
to
tblEmployeeOveretime PayPeriodID.

Mark
-----
"tina" wrote:

Mark, my warning was about Lookup *fields* in tables, not about
"lookup"
tables (i call them "supporting" tables, so as to not confuse them
with
Lookup fields). databases often have numerous supporting tables.
they're
typically "parent" tables, with a one-to-many link to one or more
data
tables, that simply provide a list of valid values for use in the
linked
data table(s) . usually, the values in a supporting table are not
added
to/edited/deleted very often. and typically the kind of table that i
call a
supporting table has only two fields, a Number field that serves as
a
primary key, and a value field for whatever values are being listed.
lookup
tables are useful in three ways: 1) they ensure that valid,
correctly
spelled values are available to the user, in forms; 2) they allow
for
more
efficient data entry in forms - generally picking from a pre-defined
combo
box droplist is easier and faster than typing a value manually and
checking
that it's spelled correctly; 3) they provide standardized values,
that
are
meaningful to the company, which can be used to "slice 'n dice" the
data
for
statistical analysis.

the tblPayPeriods, that i suggested in my previous post, is an
example
of a
supporting table. some others i've used a tblTitles (Mr., Mrs.,
Ms,
Rev., Dr., etc); tblPhoneTypes (Home, Office, Business, Cell, Fax,
Pager,
etc); tblCategories (any list of categories that compartmentalize or
"label"
data in a way that is meaningful to the company using the database);
tblStates (a list of the U.S. states with a field for the
two-character
USPS
abbreviation, and a field for the full name of the state/territory).

hth


"NWO" wrote in message
...
Tina, regarding the lookup table business, would you then
recommend
that
the
values just be entered in the field even if there are several
duplicate
values, or just do a better job at normalizing the table structure
so
that
these repeating fileds are located in another table (I suspect
you're
going
to go with the latter, which means I'll have to make significant
chmnages
to
my tables because most of the fileds are indeed based on lookup
tables,
but
that's OK because I want to do the right thing at the desing stage
and
not
pay for poor desing latter). Your coments are always welcome.

Mark
-------------

"tina" wrote:

well, i believe i would simplify the table structure (and hence
the
data
entry issues), as

no change to tblEmployees

tblPayPeriods
PayPeriodID
PayPeriod
note: this changes the table to be simply a list of all pay
periods
(you
could add the coming years pay periods at the beginning of each
year,
for
instance, so they're available all year long for data entry
purposes).
the
table is *not* linked to tblEmployees.
*also note*: you should NOT give a field in a table the *exact*
same
name
as the table's name. recommend you change one or the other, for
example
by
making the table name plural, as in the example above.

tblEmployeeOvertime
OvertimeID (pk)
EmployeeID (fk)
PayPeriodID (fk)
G22LineNUmber
NumApprovals
NumDenials
NumHours

note: if you have any Lookup fields in any of your tables,
recommend
you
get rid of them. see http://www.mvps.org/access/lookupfields.htm
for
reasons
why.

okay, now to address the data entry scenario you previously
described as
what you "really want to do":

Supervisors first selectes a PAy Period form a drop down list.
Supervisor
then selects a Unit name form another drop down list and all
of
the
employees
assinged to that unit appear in a subform. Supervisor then
selects an
employee and either existing Overtime record(s) record appers
for
the
Pay
Period selected or the supervisor has the ability to add new
Overtime
detail
records for the selected pay period.

suggest the following:

create a form, bound to tblEmployees, i'll call it
sfrmEmployees,
and
set
the form's DefaultView property to SingleForm.

create another form, bound to tblEmployeeOvertime, i'll call it
sfrmOvertime, and set the form's DefaultView to ContinuousForms
or
Datasheet.

open sfrmEmployees and add a subform control, i'll call it
ChildOvertime.
set its' properties as follows

SourceObject: sfrmOvertime
LinkChildFields: EmployeeID
LinkMasterFields: EmployeeID

when you move to each record in sfrmEmployees, the related
records
in
sfrmEmployeeOvertime will display in the subform ChildOvertime.

now create an unbound form, to serve as the "main" form. add two
unbound
combo boxes, as

cboUnit (with RowSource based on a tblUnits - which would be a
list
of
all
units that you're tracking employees for.)
cboPayPeriod (with RowSource based on tblPayPeriods.)

add a subform control, i'll call it ChildEmployees. set its'
properties
as
follows

SourceObject: sfrmEmployees
LinkChildFields: AssignedUnit
LinkMasterFields: cboUnit

now each time you select a unit from cboUnit in the main form,
the
subform
ChildEmployees will display that unit's employees.

go back to sfrmOvertime in the database window, and open it in
Design
view.
add the following code to the form's BeforeUpdate event
procedure,
as

Me!PayPeriodID = Me.Parent.Parent!cboPayPeriod

here's how it all works: the supervisor opens the main form,
selects a
pay
period from cboPayPeriod, and selects a unit from cboUnit. the
subform
populates with all the employee records assigned to that unit.
the
supervisor moves through the subform records to an employee he
wants
to
add
overtime for, then he moves into the "sub" subform, and enters
the
G22LineNumber and the NumHours, etc.
he does NOT need to add the OvertimeID because that should be
generated
automatically as the primary key field.
he does NOT need to add the EmployeeID because that should be
automatically
added via the link between the Employees subform and the
Overtime
"sub"
subform.
he does NOT need to add the PayPeriodID, because that will be
automatically
added by the code in the "sub" subform's BeforeUpdate event.

whew, that's a loooong explanation. but if you set it up step by
step, i
think you'll find it easy and pretty straightforward. and it
provides

demo db on its' way.
basically, you break it up so that automated data miners (i think they're
called "spiders") dont' recognize it as an email address. example:

cRybEMOerVxE1aAtcLLComAcaPsItTdoAtnLetS

delete all the the capital letters and change the at and dot to symbols.

hth


"NWO" wrote in message
...
Thank you Tina. I'm working today and I look forward to your e-mail.

How do you disguise an e-mail address?

Mark

"tina" wrote:

i'll put together a small demo with the tables/fields we've been
discussing,
Mark, and email it to you probably on Sat 11/19. (btw, i hope you don't
get
spammed to death; it's generally not a good idea to post a "real" email
address in these newsgroups unless you disguise it to foil the
spammers.)


"NWO" wrote in message
...
Thank you Tina.

I think a Demo would be nice address is at end of this thread.

Thank you.

Mark

"tina" wrote:

this is turning into a really long thread, Mark. rather than go thru
another
extended Q&A, i'm willing to take a look at your database and
troubleshoot
the solution, or create a "demo" db and send it to you, so you can
see
an
example of the setup. if you decide to pursue either option, post
back
with
an email address, disguised to foil the spammers, and tell me which
option
you choose.


"NWO" wrote in message
...
Thank you Tina.

I followed your example in your previous post and got things
working
all
the
way to the Main form. The main form displays the Unit and Pay
Period
combo
boxes, and they have proper values in the drop down list, but
nothing
happens
when the selections are made. Also, when I open the form, I get a
parameter
box asking for a Unit, and then a Pay Period, then the form
appears,
selections are made via the combo boxes, but nothing appears in
the
sub
form.
Any suggestions. I'm pretty sure that I folowed you procedures
very
carefully. I also re-did the relationships relating the
tblEmployeeOveetime
to tblEmployee with EmployeeID and relating the tblPayPeriod
PayPeriodID
to
tblEmployeeOveretime PayPeriodID.

Mark
-----
"tina" wrote:

Mark, my warning was about Lookup *fields* in tables, not about
"lookup"
tables (i call them "supporting" tables, so as to not confuse
them
with
Lookup fields). databases often have numerous supporting tables.
they're
typically "parent" tables, with a one-to-many link to one or
more
data
tables, that simply provide a list of valid values for use in
the
linked
data table(s) . usually, the values in a supporting table are
not
added
to/edited/deleted very often. and typically the kind of table
that i
call a
supporting table has only two fields, a Number field that serves
as
a
primary key, and a value field for whatever values are being
listed.
lookup
tables are useful in three ways: 1) they ensure that valid,
correctly
spelled values are available to the user, in forms; 2) they
allow
for
more
efficient data entry in forms - generally picking from a
pre-defined
combo
box droplist is easier and faster than typing a value manually
and
checking
that it's spelled correctly; 3) they provide standardized
values,
that
are
meaningful to the company, which can be used to "slice 'n dice"
the
data
for
statistical analysis.

the tblPayPeriods, that i suggested in my previous post, is an
example
of a
supporting table. some others i've used a tblTitles (Mr.,
Mrs.,
Ms,
Rev., Dr., etc); tblPhoneTypes (Home, Office, Business, Cell,
Fax,
Pager,
etc); tblCategories (any list of categories that
compartmentalize or
"label"
data in a way that is meaningful to the company using the
database);
tblStates (a list of the U.S. states with a field for the
two-character
USPS
abbreviation, and a field for the full name of the
state/territory).

hth


"NWO" wrote in message
...
Tina, regarding the lookup table business, would you then
recommend
that
the
values just be entered in the field even if there are several
duplicate
values, or just do a better job at normalizing the table
structure
so
that
these repeating fileds are located in another table (I suspect
you're
going
to go with the latter, which means I'll have to make
significant
chmnages
to
my tables because most of the fileds are indeed based on
lookup
tables,
but
that's OK because I want to do the right thing at the desing
stage
and
not
pay for poor desing latter). Your coments are always welcome.

Mark
-------------

"tina" wrote:

well, i believe i would simplify the table structure (and
hence
the
data
entry issues), as

no change to tblEmployees

tblPayPeriods
PayPeriodID
PayPeriod
note: this changes the table to be simply a list of all pay
periods
(you
could add the coming years pay periods at the beginning of
each
year,
for
instance, so they're available all year long for data entry
purposes).
the
table is *not* linked to tblEmployees.
*also note*: you should NOT give a field in a table the
*exact*
same
name
as the table's name. recommend you change one or the other,
for
example
by
making the table name plural, as in the example above.

tblEmployeeOvertime
OvertimeID (pk)
EmployeeID (fk)
PayPeriodID (fk)
G22LineNUmber
NumApprovals
NumDenials
NumHours

note: if you have any Lookup fields in any of your tables,
recommend
you
get rid of them. see
http://www.mvps.org/access/lookupfields.htm
for
reasons
why.

okay, now to address the data entry scenario you previously
described as
what you "really want to do":

Supervisors first selectes a PAy Period form a drop down
list.
Supervisor
then selects a Unit name form another drop down list and
all
of
the
employees
assinged to that unit appear in a subform. Supervisor
then
selects an
employee and either existing Overtime record(s) record
appers
for
the
Pay
Period selected or the supervisor has the ability to add
new
Overtime
detail
records for the selected pay period.

suggest the following:

create a form, bound to tblEmployees, i'll call it
sfrmEmployees,
and
set
the form's DefaultView property to SingleForm.

create another form, bound to tblEmployeeOvertime, i'll call
it
sfrmOvertime, and set the form's DefaultView to
ContinuousForms
or
Datasheet.

open sfrmEmployees and add a subform control, i'll call it
ChildOvertime.
set its' properties as follows

SourceObject: sfrmOvertime
LinkChildFields: EmployeeID
LinkMasterFields: EmployeeID

when you move to each record in sfrmEmployees, the related
records
in
sfrmEmployeeOvertime will display in the subform
ChildOvertime.

now create an unbound form, to serve as the "main" form. add
two
unbound
combo boxes, as

cboUnit (with RowSource based on a tblUnits - which would be
a
list
of
all
units that you're tracking employees for.)
cboPayPeriod (with RowSource based on tblPayPeriods.)

add a subform control, i'll call it ChildEmployees. set its'
properties
as
follows

SourceObject: sfrmEmployees
LinkChildFields: AssignedUnit
LinkMasterFields: cboUnit

now each time you select a unit from cboUnit in the main
form,
the
subform
ChildEmployees will display that unit's employees.

go back to sfrmOvertime in the database window, and open it
in
Design
view.
add the following code to the form's BeforeUpdate event
procedure,
as

Me!PayPeriodID = Me.Parent.Parent!cboPayPeriod

here's how it all works: the supervisor opens the main
form,
selects a
pay
period from cboPayPeriod, and selects a unit from cboUnit.
the
subform
populates with all the employee records assigned to that
unit.
the
supervisor moves through the subform records to an employee
he
wants
to
add
overtime for, then he moves into the "sub" subform, and
enters
the
G22LineNumber and the NumHours, etc.
he does NOT need to add the OvertimeID because that should
be
generated
automatically as the primary key field.
he does NOT need to add the EmployeeID because that should
be
automatically
added via the link between the Employees subform and the
Overtime
"sub"
subform.
he does NOT need to add the PayPeriodID, because that will
be
automatically
added by the code in the "sub" subform's BeforeUpdate event.

whew, that's a loooong explanation. but if you set it up
step by
step, i
think you'll find it easy and pretty straightforward. and it
provides