Excluding values based on another table
Windows 7 64bit 
Access 2010 64bit
I have two tables:
Employees and Holidays.
Table Employees contains a list of employee names that I want to be able to choose from when selecting a person for a shift (im building a roster system). Holidays contains two fields, employee name and day off.
The relationship between the two tables is a one to many, Employees being the one and Holidays being the many - linked with employee name. Each person might have a few dozen entries into holidays for various days off
The form that I have made so far has a text box on it called datebox, which is the day that you are entering the shifts for.
What I would like is for the user to be unable to select a persons name for a shift if they are in the holidays table as having a day off on the same date as in datebox
I know I need to make a query to do this but all the critera (using one query) I tried so far seems to only want to either return every name, not filtering out any that are down for holidays, or only the ones that are down for holidays instead of the ones that arent.
I tried making two querys:
queryp1 - Has two fields. One is [Holidays].[Employee Name] and the other is [Holidays].[Day Off]. I used [Forms]![Shift Entry]![datebox] as a criteria on the [Holidays].[Day Off] field to only return the names of people who ARE on holidays for that day.
This part works fine
queryp2 - lists all of the names in [Employees].[Employee Name] with a criteria of Not Like [Queryp1]![Employee Name] however a input box comes up asking for the field [Queryp1]![Employee Name]. If I add the query into the tables area at the top then I end up with multiple results for every person because each person has multiple entries in the Holidays table the queryp1 is based on.
I'm really stuck with this one, and don't have a real lot of experience with access besides making another DB for invoices. Any help would be greatly appreciated.
I hope all that made sense
Reply With Quote
