Trouble Making A DB

[Loony22]

Hello all. I am having trouble making db of Employee working trace.
The clerks in the company wants that the form of inserting one work trace to look like this.

Month / Year (Entered by the clerk)
Employee Id (Entered by the clerk)


Day In Month (Locked value) Day in week (locked value) Start Hour End Hour (could be many)

1 Sunday 10:30 - 12:30
2 Monday 11:30 - 12:30 AND 14:30 - 16:30
3 Tusday 14:40 - 16:30 AND 00:00 - 03:00 AND 06:00 - 07:00
....31 (depend on month) and etc etc...


As u see in the example. The user only needs to insert the start Hour and End hour. Day within month and day within week are locked values.
One problem to be addressed is what happens if the employee worked in one day two shifts?
I really don’t know how to do the db. I tried several ways, none of them are good.

 

[Fuga]

This is how I would start out. Maybe there are better ways.

Two tables:

1. Employees (with fields Employee ID (counter), Name (text))
2. Hours (with fields employee (number, linking the post to the right empployee ID in Employees table, start time (date/time) and end time (date/time)

This should suffice for the 1:N relation you need.

Fuga.

 

[Loony22]

Thank you very much for your help. However, my problem is not making the tables. I don't konw how to make the form as the employees wanted it to look (example above) . If you could help me make the form i'll be greatful.

 

[ritchieroo]

Why this layout?

You're going to have a struggle making the form look the way the company clerks want. Access works best if forms are as near to normalized as possible.

I would push back... start by asking why this layout is particularly important?

It may be a case that this layout may be an aid to keying in values from a big report. In which case, there might be a case for creating an electronic interface. The source system may be able to produce an extract instead of a report. If this is not possible, you might be able to get results keyed onto a spreadsheet, then write a routine for inporting these to Access.

You might discover that layout is not at all important for keying data, but clerks like to see it that way for checking. In this case I would suggest a report (one based on a crosstab query). And produce a more standard interface for keying data.

You could try "distracting the audience with something flashy". For instance, use a calendar control to select date, and a standard two column table to enter values. This has worked well for me in the past.

The best advice I can give is talk to the users, try to find out the reasons for specific requests like this. Then try to sell something better wherever possible.


Here's one scenario... The head of department was given the job for defining requirements for a new system to replace a slow outdated system. In her department she had 20 or so employees who used the system on a day to day basis to key new data, and she herself had to access the system once a month to produce some reports. The legacy system had very poor reporting facilities, so in fact in order to produce reports she went into every record and compiled summary information from input panels manually (this wasn't a problem until the business started to grow).

The current interface makes it relatively easy to perform this manual work (although it now takes a great deal of time). People have been doing this way for so long, that they have forgot it was a workround to start with, and it is now seen as "proper procedure". Everyone is scared of change!

The people keying information on a daily basis moaned about the bad layout when they started, but eventually became resigned to doing it that way. The head of department has been so busy coming up with specifications and doing her normal job that she didn't have time to ask any of the users for input. (Remember: she uses the system once a month, they use it daily).


It's an all to familiar story in my experience, and what it taught me was to always question. To find out who uses the system and to interview them, find out what they want and why they want it.

If something smells bad from a design point of view then you can be almost assured it will stink once you go live.


A final word of caution. If you ask the question "why" and the answer comes back "it's a legal requirement" your stuck, and it's time to bite the bullet. Still there's no harm in asking.

 

[Loony22]

Thank you for your lovely post. I must admit you are 100% right. I sat down, talked to the manager first and he told me as followed (translated ) : " At start everything looks hard and impossible to do cause its new. They (employees) will get used to it. "
So i will make calender view.

Thank you very much indeed.