Table / Form Design for Headcount Report

[Debris]

Hello,

First, I'm not sure if this a "tables" design question or "forms" question
so I'm posting in both groups.

I receive a daily force / headcount report of non-resident contractors on
site for the day. The report lists the number of people on site, by craft
(pipefitter, electrician, etc.). While we usually have five or six crafts
onsite daily, there are a total of twenty possible crafts to choose.

I'd like this data in some sort of table for analysis. I guess the "quick
and dirty" way is to put the data in an Excel spreadsheet / pivot table, but
then you're always going back and resetting the data range, etc. (Plus it
would get to be a huge sheet,as we're working six days a week.) It seems a
small Access database would be a solution, with an Excel spreadsheet
querying the database.

My first table design was a twenty-one field table, with the date as the
first field, the count of the first craft as the second field record, and so
on:

First Field...Date
Second Field...CountCraft1
Third Field...CountCraft2
Fourth Field...CountCraft3

So this generated one record per day (there's also a corresponding form with
twenty-one fields). But then I realized all I had done was mimic an Excel
spreadsheet in an Access table (with the same level of functionality). But,
the form was nice, as it presents all twenty-one fields one page.

So, I tried a different table design: first field was the date, second field
was the craft (chosen from a related table that listed all of the crafts),
and the third field is the headcount of the chosen craft.

This works much better, except that now I have can up to twenty records for
any given day (not likely, but I will have six or seven).

This isn't a problem, except for the form: now the data clerk now has to
make six+ entries per day in the form I've set up. Even with a drop-down
combo box to choose the craft, it still seems to take time and there's some
room for data entry error.

So, to my question: is there some other table design I've overlooked? Or is
there some technique to make a better form that manages to get all twenty
possible crafts on one page? Subforms, perhaps?

Sorry for the long-winded note. Any help is appreciated.

D

 

[Damian S]

Hi Debris,

Your table design started out bad, but you identified that yourself,
rectified it and sound as though you are on the right track.

You could further break your design into another table, one with the header
information like this:
tblOnsiteCounts
OnsiteCountID autonumber PK
OnsiteCountDate datetime
Other fields as necessary

then a second with the Craft Counts like this:
tblOnsiteCountsCrafts
OnisteCountCraftID autonumber PK
OnsiteCountID number (not 0) FK - links to tblOnsiteCounts
CraftID number (not 0) FK - links to tlkCraft
OnsiteCountCraftCount number

using lookup table tlkCraft
CraftID autonumber PK
Craft text

then, have a main form with the Date and other details (from
tblOnsiteCounts), a continuous subform with the craft/counts (from
tblOnsiteCountsCraft). Make sure you have your tab order correct and your
combo box set up correctly, and you should find that little or no mouse
clicks are required, and you will get good data entry efficiency.

The benefit of this design is that if you decide to count other things, you
could either add them into the craft table, or if you require different
information, add additional tables and subforms linked back to your "header"
table.

Hope this helps.

Damian.

 

[Amy Blankenship]

Hello,

First, I'm not sure if this a "tables" design question or "forms" question
so I'm posting in both groups.

I receive a daily force / headcount report of non-resident contractors on
site for the day. The report lists the number of people on site, by craft
(pipefitter, electrician, etc.). While we usually have five or six crafts
onsite daily, there are a total of twenty possible crafts to choose.

I'd like this data in some sort of table for analysis. I guess the "quick
and dirty" way is to put the data in an Excel spreadsheet / pivot table,
but then you're always going back and resetting the data range, etc.
(Plus it would get to be a huge sheet,as we're working six days a week.)
It seems a small Access database would be a solution, with an Excel
spreadsheet querying the database.

My first table design was a twenty-one field table, with the date as the
first field, the count of the first craft as the second field record, and
so on:

First Field...Date
Second Field...CountCraft1
Third Field...CountCraft2
Fourth Field...CountCraft3

So this generated one record per day (there's also a corresponding form
with twenty-one fields). But then I realized all I had done was mimic an
Excel spreadsheet in an Access table (with the same level of
functionality). But, the form was nice, as it presents all twenty-one
fields one page.

So, I tried a different table design: first field was the date, second
field was the craft (chosen from a related table that listed all of the
crafts), and the third field is the headcount of the chosen craft.

This works much better, except that now I have can up to twenty records
for any given day (not likely, but I will have six or seven).

This isn't a problem, except for the form: now the data clerk now has to
make six+ entries per day in the form I've set up. Even with a drop-down
combo box to choose the craft, it still seems to take time and there's
some room for data entry error.

So, to my question: is there some other table design I've overlooked? Or
is there some technique to make a better form that manages to get all
twenty possible crafts on one page? Subforms, perhaps?

Let's assume that your first table is tblCraftDateHeadCount and your Lookup
table is tblCrafts

Let's further assume you have
tblCraftDateHeadCount fields:
CraftDateHeadcountID
CraftDate
CraftID
HeadCount

tblCraft
CraftID
CraftDesc

What you want to do is create a query that will serve as the data source for
the form that shows records that don't actually exist in order for your
clerk to fill them in. If the clerk doesn't fill anything in for those
records, they will never actually exist.

So, you'll need something like this:

SELECT CraftDateHeadcountID, CraftDate, HeadCount, CraftDesc FROM
tblCraftDateHeadCount LEFT JOIN tblCraft ON tblCraftDateHeadCount.CraftID =
tblCraft.CraftID

Make sure when you run the query before saving it in the form's row source
that the >* button is active (at the bottom of the data sheet), indicating
that this is an updatable query. If not, post back and we'll see what we
can do.

I'd like to make a suggestion that you also have a CraftDate table for the
sole purpose of allowing you to have a clear division between your dates in
the form and navigate back and forth between dates. I'm not even sure the
above will work properly without that, because it would simply give you one
set of records that could be filled in with any combination of dates and
crafts.

HTH;

Amy

HTH;

Amy