Hi All,
Firstly I'd like to let you know that last week was the first time I have ever used Access so if this is a simple to resolve task, my apologies!
Basically have 2 tables:
One called "Employees" which has a list of all 110 employees at the company with their grade (director, principal, manager etc) in the only other field.
The second is called "Availability" and is a list of the work done by these employees each week giving the number of days spent on that task.
In this table are the below fields:
Name - First Name and Surname - not linked to the employee table but all spelt exactly the same
Grade - There are 11 Grades, the grade is the same in both tables
Week - 1 - 52
Client - The name of the task done/ company worked for
Type - There are 7 codes for type of work: say A - E are tasks that make people unavailable (eg. too important to cancel) and F&G are tasks with low priority meaning people with these tasks booked in are available to do an A - E task if necessary.
Days - The number of days that week spend on said task.
For example
I want to find who is free for 1 days work in week 23. Say there are 3 employees in the company - Fred, Mike and John. John doesnt have anything booked for week 23 and so is available. Fred is booked for 4 days, free for 1 and Mike is booked up for all 5.
Name Current Grade Week Client Type Days
Fred Manager 23 Company X C 4
Mike Director 23 Company Y B 5
Fred Manager 23 Company Z F 1
So, my problem is that when I do a query to show all people who say for example are available for 1 day in week 23 I am not sure how to get John to show (as he is obviously not on this list).
My thoughts were running a query to show who is not available and then somehow cross checking that again the list of employees but am struggling to do this part.
Any advice would be very much appreciated and I will reply to any questions/clarifications you might need if I havent explained this well enough.
Thanks!
Firstly I'd like to let you know that last week was the first time I have ever used Access so if this is a simple to resolve task, my apologies!
Basically have 2 tables:
One called "Employees" which has a list of all 110 employees at the company with their grade (director, principal, manager etc) in the only other field.
The second is called "Availability" and is a list of the work done by these employees each week giving the number of days spent on that task.
In this table are the below fields:
Name - First Name and Surname - not linked to the employee table but all spelt exactly the same
Grade - There are 11 Grades, the grade is the same in both tables
Week - 1 - 52
Client - The name of the task done/ company worked for
Type - There are 7 codes for type of work: say A - E are tasks that make people unavailable (eg. too important to cancel) and F&G are tasks with low priority meaning people with these tasks booked in are available to do an A - E task if necessary.
Days - The number of days that week spend on said task.
For example
I want to find who is free for 1 days work in week 23. Say there are 3 employees in the company - Fred, Mike and John. John doesnt have anything booked for week 23 and so is available. Fred is booked for 4 days, free for 1 and Mike is booked up for all 5.
Name Current Grade Week Client Type Days
Fred Manager 23 Company X C 4
Mike Director 23 Company Y B 5
Fred Manager 23 Company Z F 1
So, my problem is that when I do a query to show all people who say for example are available for 1 day in week 23 I am not sure how to get John to show (as he is obviously not on this list).
My thoughts were running a query to show who is not available and then somehow cross checking that again the list of employees but am struggling to do this part.
Any advice would be very much appreciated and I will reply to any questions/clarifications you might need if I havent explained this well enough.
Thanks!