Easiest way to do... (1 Viewer)

[Matizo]

Hello,

I have many to many relationship (services to employees) so I've broken it down into 3 tables. In the 3 table I use combo box to choose service (ID, title) and employee (ID, full name)

My question is... what is the best way to present this in a form taking into account the following constraint: TIME. In services table there are start date and estimated duration so it is possible to calculate the end date.
So.. After choosing the service I want only those employees who are available in the time frame (start date-end date).

What is the easiest way of doing this?
I have quite good understanding of Access, I'm doing this as part of uni project which is quite stupid because I'm doing Accounting and Finance!

Thanks!

 

[CEH]

My two cents........
I think I would first add another table... Should like you need to track "AvailableTime" of employees.
Then in your join table I would add ProjectTimeStart and ProjectTimeEnd... or whatever it is you want to call it.....
On the main form beginning and ending time/Date, and Service. A subform for employees that would be filtered by your beginning and ending time/Date on the main form.

 

[Matizo]

Hi Curtis,

Is there a way, to do it by using a query.

For example, user specifies the time frame and only those employees available in this time appear?

 

[CEH]

Yes, thats what I meant by your subform would be "filtered"... In your criteria you reference the fields on the form. So if you have a project date field [ProjectDate] you would reference the employee available dates in the criteria of [ProjectDate] as "Between [EmployeeDate1] and [EmployeeDate2]"
So if the Project date fell between EmployeeDate1 and EmployeeDate2 it would show in the query.
Hope that makes sense!
Sometimes you will need to use a path to the form field... or Me.EmployeeDate1
Hard to tell which without seeing the DB... But you can play with that a bit.