Structural problem: Add unknown amount of data through a form

[Resurrection]

My database is coming along, but I am now stuck. My project tracks
employees, who are assigned to a work section, by each day, and what
"station" they are working at. I have a form which allows an end user
to input a work roster for each date. The form allows for a name to be
put into each work station field. This is not a problem. My unknown
variable that I can't seem to deal with is what to do with people who
are "off", training, leave, etc for each day. On one day, I might have
4 people who fall into this category. On the next, I might have 2. On
the next I might have 9. How do I, in theory, structure my database to
allow for a user to input on the form all the names of people who are
not assigned to a specific work station? And I want to store each name
as a separate piece of data so that I can quatify that data later, as
in during these dates, I had x number of people at training.

1) I thought I might want to just code a way to add fields to the work
roster table for each person, but that seemed like self abuse, as well
as I read through here that this is not the correct way to build a
database. "Fields are expensive, records are not" <-- See I am reading
and learning here!

2) So then I thought I might make a separate table for this info that
has a record for each date with a field for each person, but how to
deal with an unknown number of possible fields?

3) I have to be able to historically track the data, so having a
status field for each person and changeing it on a daily basis would
not work.

4) Building in a limited number of fields seems the "cheap" way to
solve the problem but that of course doesn't allow for the rare case of
needing more fields and it doesn't seem "professional" to me.

5) Building a table which has a record for name (with fields for date,
status, etc) seems feasible but also expensive. If I have 60 people
not "working" each day, in a year or more that seems like a ton of
records. And even in that case I would want to input a name in a text
box on the form, on update move that text to a list, and allow for
another entry in the same box until a list was built on the form or
some way to see all the names that were not "working".

Storing the unknown number of people who are not at any work station on
a given day as unique pieces of data (i.e. not multiple names in one
field) plus keeping track of this by date is my problem in a nutshell.
Any ideas? Maybe a way to add names to a listbox on the form, but then
how to store them individually?

 

[Guest]

If I have understood your info correctly it sounds to me as if your form
should allow the end user to put the whereabouts of each employee on each
date, That way you could allocate several people to the same work roster or
say they were on leave etc. Consider the structure of your database and see
if that would work.

HTH Sheila

 

[Resurrection]

If I have understood your info correctly it sounds to me as if your form
should allow the end user to put the whereabouts of each employee on each
date, That way you could allocate several people to the same work roster or
say they were on leave etc. Consider the structure of your database and see
if that would work.

HTH Sheila

You are correct, that is what I want to do: To account for every
employee's whereabouts on a roster for every day. The problem is that
I have work sections with varying number of employees, numbers of
people not in a work status varies each day, and I have to be able to
track each person's status over a historical time period. And with 75+
employees in a work section, I don't want to create 75 boxes on a table
to put one name and status info in for every person. Which gets back
to the heart of my problem. How do I structurally track this, as well
as how do I allow for an input field in a form that can "grow" so to
speak based on the amount of input.

 

[John Vinson]

How do I, in theory, structure my database to
allow for a user to input on the form all the names of people who are
not assigned to a specific work station? And I want to store each name
as a separate piece of data so that I can quatify that data later, as
in during these dates, I had x number of people at training.

Why not just have another few rows in the table of Workstations -
Vacation, Training, Sick leave? It sounds like what you're trying to
track is not so much "which workstation is each employee at" as "where
is each employee today".

It's not clear what your table design might be, but I'd suggest it
should have three tables:

Employees
EmployeeID
LastName
FirstName
<other bio data>

Workstations
WorkstationID
Description

Assignments
EmployeeID <who's assigned to this>
WorkstationID <what are they assigned to>
StartTime <date/time this session started>
EndTime <date/time they finished>


John W. Vinson[MVP]

 

[Resurrection]

Well that is a close approximation of how my tables are set up except
this:

Workstation Roster Table
Date (Primary key)
Workstation1
Workstation2
Workstation3
etc
etc

The reason its this way is because the central most important piece of
info is for me to be able to pull out info of whose on what workstation
per day. The tracking of the other employees is just a side
requirement. I based the Roster table off of date so that I can have a
record for each date with all the pertinent data in the record.

And I understand what you are saying John, but I would still encounter
my original problem (at least I think I would) of how to deal with an
unknown number of employees that are not at a workstation. If I don't
know on a given day in the future how many employees would be off, how
do I build enough fields to contain the data from the start? How do I
keep track of 1 off person one day, then 18 the next day, then 5 the
next day, etc. I guess I could hard code in a "best guess" of how many
fields I might need, but that seems like the wrong way.

Right now my only thought is to have a separate table like:

NotAtWorkTable
Employee ID
Status
Date

where I have one record for every "off" person per day. But this could
be literally up to 50 people. 50 records added x 365 days = 18250
records per year. Is this alot? I know I would need to track this
data for at least a year, maybe up to 2 years max before I can start
removing old records. Not having implemented a database of this scale
before, am I going to create a "gorilla that I can't tame" so to speak
by doing this?

 

[John Vinson]

Well that is a close approximation of how my tables are set up except
this:

Workstation Roster Table
Date (Primary key)
Workstation1
Workstation2
Workstation3
etc
etc

The reason its this way is because the central most important piece of
info is for me to be able to pull out info of whose on what workstation
per day.

In that case you want that information STORED IN ONE FIELD.

In the normalized many to many table structure, you have only one
field to search. In your "wide-flat" spreadsheet design, you have
many. If you add a new workstation, what do you do - restructure your
table, redesign all your queries, redesign all your forms!?

The tracking of the other employees is just a side
requirement. I based the Roster table off of date so that I can have a
record for each date with all the pertinent data in the record.

Then store it correctly:

RosterTable
WorkDate
WorkstationID
EmployeeID

with WorkDate and WorkstationID as a joint, two-field primary key.
This will let you keep track of any number of workstations. In
addition, you can add pseudo "workstations" of "Sick Leave",
"Vacation", "Training" - so you can identify those employees who are
NOT at one of the defined workstations.

where I have one record for every "off" person per day. But this could
be literally up to 50 people. 50 records added x 365 days = 18250
records per year. Is this alot?

It's tiny. When you get a thousand times that many records you'll have
a pretty big table. Tens or hundreds of thousands of records are *no*
problem for a properly designed Access database.

John W. Vinson[MVP]

 

[Resurrection]

Thanks John, I understand what you are telling me, and it does help
with my current problem. Yes this would require a redesign of my
structure, but I'm fine with that. I want this to be "professional"
not hacked together / barely functional. There were a lot of nuances
of relational databases that I didn't fully understand until after
having worked through some issues like this and redesigning things a
couple of times. All the better for a solid end product.

John Vinson wrote:

In that case you want that information STORED IN ONE FIELD.

In the normalized many to many table structure, you have only one
field to search. In your "wide-flat" spreadsheet design, you have
many. If you add a new workstation, what do you do - restructure your
table, redesign all your queries, redesign all your forms!?

You are right of course, I just hadn't thought down this road until I
read this. Changing work station structure later on would be a major
undertaking of redesigning forms, tables, etc. And in my organization,
people are likely to scrap it quickly if it isn't easily adaptable.

Then store it correctly:

RosterTable
WorkDate
WorkstationID
EmployeeID

with WorkDate and WorkstationID as a joint, two-field primary key.
This will let you keep track of any number of workstations. In
addition, you can add pseudo "workstations" of "Sick Leave",
"Vacation", "Training" - so you can identify those employees who are
NOT at one of the defined workstations.

This is of course easy to implement from a table standpoint, but may be
difficult for me to implement from a form standpoint. By your
RosterTable idea, I would have a record for every person for every day.
But I would prefer to have a form set up that will allow a user to
enter multiple workstations per day and the people on the stations. I
can see a future obstacle there of how to add mutiple records (at the
same time) from within one form that is neat and compact appearance.
Maybe some implementation of a continous form where I would have a user
enter a good number of people records at one time? I will work on
implementing your idea tonight and then post back if I run into form
building problems.

It's tiny. When you get a thousand times that many records you'll have
a pretty big table. Tens or hundreds of thousands of records are *no*
problem for a properly designed Access database.

Thanks thats reassuring, I had no idea what "big" was in terms of
Access. Plus I guess there is always the possibility of creating
archives by month or something and keeping a working copy of the db
small in terms of records.

 

[John Vinson]

This is of course easy to implement from a table standpoint, but may be
difficult for me to implement from a form standpoint. By your
RosterTable idea, I would have a record for every person for every day.
But I would prefer to have a form set up that will allow a user to
enter multiple workstations per day and the people on the stations. I
can see a future obstacle there of how to add mutiple records (at the
same time) from within one form that is neat and compact appearance.
Maybe some implementation of a continous form where I would have a user
enter a good number of people records at one time? I will work on
implementing your idea tonight and then post back if I run into form
building problems.

See the Orders form in the Northwind sample database for an example. A
continuous Subform with a simple table like this is very
straightforward, compact, and usable. You could use a main form with
just a single (unbound) textbos for the date, and use that control as
the Master Link Field; on each row of the subform you'ld have a
workstation and a combo box for the person assigned to that
workstation, by basing the subform on a Query joining the Workstations
table to the assignments table.

John W. Vinson[MVP]

 

[Resurrection]

See the Orders form in the Northwind sample database for an example. A
continuous Subform with a simple table like this is very
straightforward, compact, and usable. You could use a main form with
just a single (unbound) textbos for the date, and use that control as
the Master Link Field; on each row of the subform you'ld have a
workstation and a combo box for the person assigned to that
workstation, by basing the subform on a Query joining the Workstations
table to the assignments table.

John W. Vinson[MVP]

Well after much more research, I recreated a portion of my database
structure as suggested above. Now I have the following tables:
Employees (contains all people type data), WorkStation (contains the
abbreviated station name, long name description, etc), and Roster
(which is structured as suggested above with fields for date,
workstationID, namedID. I created a form as suggested above but it
doesn't function as I want. The end result is that I want a user to be
able to enter the date and then in a continous subform enter the name
for each workstation. I also want the subform to display previously
entered data depending upon what date is selected in the combo box on
the main form. Right now, my subform populates the Roster table with
each workstation and nameID that I enter, but it doesn't fill the date
field. I need the date field to be populated for each record.

 

[Resurrection]

This may be a repeat post by me, my first try didn't seem to go
through.

See the Orders form in the Northwind sample database for an example. A
continuous Subform with a simple table like this is very
straightforward, compact, and usable. You could use a main form with
just a single (unbound) textbos for the date, and use that control as
the Master Link Field; on each row of the subform you'ld have a
workstation and a combo box for the person assigned to that
workstation, by basing the subform on a Query joining the Workstations
table to the assignments table.

John W. Vinson[MVP]

I restructured my database accordingly from the above posts and I now
have these tables: Employees (contains all people type data),
WorkStations (workstationID, workstation name, etc), and Roster (date,
workstationID, nameID). I tried to build the form as you suggested
with an unbound combo box for date and then a continous sub form below
it showing input combo boxes for workstationID, nameID. It works
except that the date entered in the date box doesn't get stored, which
I assume is because its unbound. I want the user to be able to enter a
date, and then regardless of whether they enter 1 or 100 workstation /
name records, the date should be added for each one. What am I doing
wrong?

 

[Resurrection]

This may be a repeat post by me, my first try didn't seem to go
through.

See the Orders form in the Northwind sample database for an example. A
continuous Subform with a simple table like this is very
straightforward, compact, and usable. You could use a main form with
just a single (unbound) textbos for the date, and use that control as
the Master Link Field; on each row of the subform you'ld have a
workstation and a combo box for the person assigned to that
workstation, by basing the subform on a Query joining the Workstations
table to the assignments table.

John W. Vinson[MVP]

I restructured my database accordingly from the above posts and I now
have these tables: Employees (contains all people type data),
WorkStations (workstationID, workstation name, etc), and Roster (date,
workstationID, nameID). I tried to build the form as you suggested
with an unbound combo box for date and then a continous sub form below
it showing input combo boxes for workstationID, nameID. It works
except that the date entered in the date box doesn't get stored, which
I assume is because its unbound. I want the user to be able to enter a
date, and then regardless of whether they enter 1 or 100 workstation /
name records, the date should be added for each one. What am I doing
wrong?

 

[Resurrection]

This may be a repeat post by me, my first try didn't seem to go
through.

See the Orders form in the Northwind sample database for an example. A
continuous Subform with a simple table like this is very
straightforward, compact, and usable. You could use a main form with
just a single (unbound) textbos for the date, and use that control as
the Master Link Field; on each row of the subform you'ld have a
workstation and a combo box for the person assigned to that
workstation, by basing the subform on a Query joining the Workstations
table to the assignments table.

John W. Vinson[MVP]

I restructured my database accordingly from the above posts and I now
have these tables: Employees (contains all people type data),
WorkStations (workstationID, workstation name, etc), and Roster (date,
workstationID, nameID). I tried to build the form as you suggested
with an unbound combo box for date and then a continous sub form below
it showing input combo boxes for workstationID, nameID. It works
except that the date entered in the date box doesn't get stored, which
I assume is because its unbound. I want the user to be able to enter a
date, and then regardless of whether they enter 1 or 100 workstation /
name records, the date should be added for each one. What am I doing
wrong?

 

[Resurrection]

This may be a repeat post by me, my first try didn't seem to go
through.

See the Orders form in the Northwind sample database for an example. A
continuous Subform with a simple table like this is very
straightforward, compact, and usable. You could use a main form with
just a single (unbound) textbos for the date, and use that control as
the Master Link Field; on each row of the subform you'ld have a
workstation and a combo box for the person assigned to that
workstation, by basing the subform on a Query joining the Workstations
table to the assignments table.

John W. Vinson[MVP]

I restructured my database accordingly from the above posts and I now
have these tables: Employees (contains all people type data),
WorkStations (workstationID, workstation name, etc), and Roster (date,
workstationID, nameID). I tried to build the form as you suggested
with an unbound combo box for date and then a continous sub form below
it showing input combo boxes for workstationID, nameID. It works
except that the date entered in the date box doesn't get stored, which
I assume is because its unbound. I want the user to be able to enter a
date, and then regardless of whether they enter 1 or 100 workstation /
name records, the date should be added for each one. What am I doing
wrong?

 

[John Vinson]

This may be a repeat post by me, my first try didn't seem to go
through.

I restructured my database accordingly from the above posts and I now
have these tables: Employees (contains all people type data),
WorkStations (workstationID, workstation name, etc), and Roster (date,
workstationID, nameID). I tried to build the form as you suggested
with an unbound combo box for date and then a continous sub form below
it showing input combo boxes for workstationID, nameID. It works
except that the date entered in the date box doesn't get stored, which
I assume is because its unbound. I want the user to be able to enter a
date, and then regardless of whether they enter 1 or 100 workstation /
name records, the date should be added for each one. What am I doing
wrong?

I don't know, since I don't know what you did.

What is the Recordsource of the Subform? What are its Master Link
Field and Child Link Field properties? The Master Link Field should be
the name of the date control... is it?

John W. Vinson[MVP]

 

[Resurrection]

All five of them did, in fact. Patience is a virtue... <g>

Sorry about that, I did wait a while between posts, they usually only
take a few minutes to show up, but these took far longer. And I did
try to remove them.

I don't know, since I don't know what you did.

What is the Recordsource of the Subform? What are its Master Link
Field and Child Link Field properties? The Master Link Field should be
the name of the date control... is it?

John W. Vinson[MVP]

Well I solved part of my issue. I wasn't quite as well educated on the
concept of Master and Child Link Fields and a little Google searching
has enlightened me, thanks for jogging my brain to know what to search
for. This page helped:
http://www.microsoft.com/technet/archive/office/office95/tips/mo99766.mspx?mfr=true

The two forms were not linked properly, specifically because I don't
have Date anywhere else but the Roster Table. I can't see any place in
my other tables where it would make sense to have another Date field,
so I link the forms together by WorkStation. This works except for one
fact. My subform has a combo box to allow me to choose the
WorkStationID. The other combo boxes on my subform allow me to add the
info for the other fields that go in the Roster Table. The problem I
have is that even if I enter no data (i.e. open my form and look at it,
then close) I get a record created in RosterTable that only has the
WorkStationID field filled in with the first value from my list for
that combo box. How do I prevent it from inserting this incomplete
record? I believe I need some code for the BeforeInsert Event of the
subForm, but I'm not quite sure what to do.

 

[John Vinson]

The two forms were not linked properly, specifically because I don't
have Date anywhere else but the Roster Table. I can't see any place in
my other tables where it would make sense to have another Date field,
so I link the forms together by WorkStation. This works except for one
fact. My subform has a combo box to allow me to choose the
WorkStationID. The other combo boxes on my subform allow me to add the
info for the other fields that go in the Roster Table. The problem I
have is that even if I enter no data (i.e. open my form and look at it,
then close) I get a record created in RosterTable that only has the
WorkStationID field filled in with the first value from my list for
that combo box. How do I prevent it from inserting this incomplete
record? I believe I need some code for the BeforeInsert Event of the
subForm, but I'm not quite sure what to do.

I would guess that you need to REMOVE some code, not add it! Just
looking at a subform will *not* create a new record unless you a) do
something to dirty the record or b) have some code to do so.

A Master Link Field need NOT be a table field. You could have the
MasterLinkField be

WorkstationID;txtDate

and the child link field be the workstation ID and roster date fields.
If the mainform had an (unbound) textbox txtDate (perhaps defaulting
to today's date with =Date() ) it would link and fill in that date in
new records.

Without knowing more about the form, I have no idea why it's creating
this record for you. What are (now) the Recordsources of the main and
subforms? What's the Master/Child Link Fields? Is there any code?

John W. Vinson[MVP]