reducing the number of fields required?

[scubadiver]

For the last couple of weeks I have been trying to design a website and it is
proving to be a bit more challenging than I thought. I thought I had managed
it but I've decided I need some advice.

The database records hours worked, hourly rates and holiday records for
permanent and temporary staff. On top of this, I need four records for each
staff member (for each week of the month, so they are NOT duplicate records)

For one part I have

Week number
Employee Name
Current (whether they are currently working)
Status (permanent or temporary)
Rate (the fee paid by the agency to the employee)
totfee (the charge paid by the company to the agency)

My supervisor has suggested that I use a cascading combo box so that when I
select the employees name, it populates the other four fields as well. I have
managed to get this to work

For permanent staff I have

Department
Sub-department (which uses a cascading combo)

contracted hours
Time and a half (overtime)
Double time (overtime)

6 boxes for different types of holiday.


For temporary staff I have

75 text boxes (25 subdepartments by 3 boxes for recording standard hours and
overtime, like the permanent staff). This is so that we can accurately record
how many standard and overtime hours each member of temp staff worked for any
one week.

Is there any way I can make this more efficient without compromising the
compiling the data using reports.

Thanks in advance

 

[scubadiver]

I will clarify that the temp staff can move from one department to another
and that is why I really need all these text boxes.

I am trying to think outside the box and reorganise the fields somehow.

 

[scubadiver]

If I separate the fields for the temp staff into different tables, how do I
make sure all the information goes into one table?

 

[Duane Hookom]

I think you have an issue confusing data presentation with data storage. You
should store your data in normalized tables. This may not be how you want to
expose your data or allow editing for users. "6 boxes for different types of
holiday" and "75 text boxes" might make for a nice user interface but it is
not how you should be setting up your table structures.

Get your table structures correct by asking questions here. If you have
"website" questions, find a different news group.

 

[scubadiver]

Sorry, I don't understand what you are saying. I am trying to design a
database. Im not an Access expert by any stretch of the imagination.

 

[mnature]

Scuba, you have probably worked with Excel files, and never used Access
before. The problem we are seeing with your description is that you are
confusing data with information. Data is simply all of the names,
departments, work dates and such. Those should be all placed in their own
tables, with primary keys and foreign keys to designate what data belongs
with some other data. Information is how you look at this data, and will be
how your form looks.

Before you even think about creating forms (which will be used to enter your
data), and reports (which is probably how you will display your information
on your web site), you need to normalize your tables. Normalization is the
process by which you determine what your core values are, and then group
those core values into tables.

You have a core value of Staff. Staff can be either permanent or temporary.
But they are all Staff, and all information about Staff should be in a
single table.

You have a core value of TimeWorked. TimeWorked can be either straight time
or overtime. It could be done for a particular department, at a particular
rate, and on a particular date.

You also have a core value of Departments. Staff can be tied to a
particular department through TimeWorked.

You will need some linking tables for tying these other tables together in
many-to-many relationships.

You will end up with tables that will probably have less than ten fields
each. This is all you need. You will then use queries to filter the
information in those tables, and the results of the queries will then be the
basis for your forms and reports.

 

[Duane Hookom]

Thanks mnature. Scuba, there are some good links on table design at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101.

 

[John Vinson]

how do I make sure all the information goes into one table?

YOU DON'T.

That's the whole *PURPOSE* of a relational database. You store each
kind of information in its own particular table, and then create
Queries joining the tables to bring disparate chunks of information
together.

If you assume that all of the information MUST be in one table in
order to use it, it is that assumption which needs changing - not your
database software!

John W. Vinson[MVP]