Validation

[Florens]

Hi,

I've got a problem. I have a table, activiteiten(activities). This table contains:
acti_opdrachtgever_id (the companies)
acti_activiteit_id (the activities)
acti_medewerker_id (the employee which is doing the activity)
acti_medewerker_gepland (the time for which the employee is planned to do this activity)
acti_benodigde_tijd (the time needed to do the activity, not the actual time which it took, but the time calculated in the planning.)

It's possible to have more then one employee doing the activity. In cases like this, the record is duplicated and the employeename is changed in the duplicate record.

This is my problem: The TOTAL time for which employees are planned to do one activity cannot be more then the time needed to do the activity.

But since the time planned per employee are in different records I don't know how to do this. And do I need to build my validation in the table itself, or do I build it in my form(only one form uses this table).

Thanks in advance,

Florens

 

[shay]

Hi

I think you need to modify your database structure.

In cases like this, the record is duplicated

is an immediate flag that the structure isn't correct.

You need two tables to store this information - for example, tblActivites and tblEmployees.

tblActivities
acti_opdrachtgever_id (the companies)
acti_activiteit_id (the activities)
acti_benodigde_tijd (the time needed to do the activity, not the actual time which it took, but the time calculated in the planning.)

tblEmployees
acti_activiteit_id (the activities)
acti_medewerker_id (the employee which is doing the activity)
acti_medewerker_gepland (the time for which the employee is planned to do this activity)

Join the tables as one-to-many on acti_activiteit_id.

If the data is being entered on a form, you will need to use a subform for the employee information. As this is entered you can calculate the activity total and compare it with acti_benodigde_tijd as part of your data validation process.

hth

shay

 

[Florens]

hi,

thanks for the reply. you're absolutely right, these tables aren't correctly build. but...(ofcourse, there's a but ) because of the way my boss wants the layout of this screen, I already have 4 subforms on my form and this in one of them. and because this is not a single(is this the right word?)form another sub is not possible.

Thanks a lot for the reply, maybe you have any other ideas?

Florens

 

[shay]

Hi

I still think you should correct the structure of the tables.

If you can't have the sub-subform, why not put a button on the Activity subform which opens a pop-up form on which you could enter details of the employees and their time.

shay