New to Access

Sorry it's my first time posting.

I have researched this and haven't found what I need.

I have two main tables.

TDY (Table)

ID (PKey)
Program
Location
Purpose
PM
ED
CT


Vacation (Table)

TeamMemberID (PKey)
Start Date
End Date
PM
ED
CT




I have a for that the PM fills out with all the columns in my TDY table. After the start date and end date they have to select individuals from ED and CT which are dropdowns made with comboboxes. I need something to help when an individual is selected, it would check my tables for availability and return a message if they are not available based on person, start date and end date.

One approach is for the comboboxes to list only those individuals who are available for the date range entered.

Don't understand data structure. Do you have a table of Individuals? What are ED and CT? Why are they in both tables? Want to provide db for analysis? Follow instructions at bottom of my post.

I have attached the file. ED (Engineering Division) and CT (Contracting) are sections within our division that support the Project Manager (PM). At one point I had
everyone listed in the Vaction (0 Leave) table as name and then changed it to match the TDY (0 Projects TDY). I simplified my table name in my orignal post to better help me to understand any directions I receive.

Thanks in advance.

There are serious issues with your db structure that need to be addressed before pursuing any further custom programming.

Why do you have records in Others with only a dot in the name field? You seem to be attempting to show a break between the Divisions. This is unnecessary and really useless. Remove those records.

The Metering, Metering/UMCS, UMCS, ED, CT tables are replicating the Others table. No reason to have separate tables.

Don't understand why the PM, ED, CT, Other1, Other2, Other3 fields are in Leave table. Doesn't matter what category an employee falls in, all that is needed to know is that employee is not available for the date range. Don't duplicate their division assignment into Leave table.

What is PM, this is not a category in any of the employee tables.

The Name textbox on Add Leave Or TDY form has an invalid ControlSource. Why is this form titled 'Leave Or TDY' and it is bound to Leave table?

Leave table has TeamMemberID set as an autonumber primary key. This should be a number field to hold the TeamMemberID as a foreign key. The PM, ED, CT, Other1, Other2, Other3 fields in ProjectsTDY should be number fields to hold the TeamMemberID as a foreign key, not the NameID. These multiple similar fields is not a truly normalized data structure. Though it might be workable for your needs, it might also be a real headache. It is a balancing act between normalization and ease of data entry/output.

Name parts should actually be in separate fields (LastName, FirstName) in the employees table.

I tried to cleanup the structure the best I could.

Getting better.

Didn't modify the ProjectsTDY table as suggested. You want to store concatenated name as First & Last will make search and sort on those fields impractical.

Employees table has a Tracey Edmonds but the Leave table shows Tracey Edmunds. How did that happen? Also, the names in Leave table don't agree with the names in Employees table for the same TeamMemberID. Don't save names into Leave table.

I renamed the date textboxes to tbxStart and tbxEnd.

RowSource for Combo8:
SELECT [Employees].[FirstName] & " " & [Employees].[LastName] AS Name, Employees.Division
FROM Employees
WHERE (((Employees.TeamMemberID) Not In (SELECT TeamMemberID FROM Leave WHERE (((Leave.StartDate) Between [Forms]![Add TDY Tasking]![tbxStart] And [Forms]![Add TDY Tasking]![tbxEnd])) OR (((Leave.EndDate) Between [Forms]![Add TDY Tasking]![tbxStart] And [Forms]![Add TDY Tasking]![tbxEnd])))) AND ((Employees.Division)="ED"));

Then code in Combo8 GotFocus event:
Me.Combo8.Requery

RowSource for Combo9:
SELECT [Employees].[FirstName] & " " & [Employees].[LastName] AS Name, Employees.Division FROM Employees WHERE (((Employees.TeamMemberID) Not In (SELECT TeamMemberID FROM Leave WHERE (((Leave.StartDate) Between [Forms]![Add TDY Tasking]![tbxStart] And [Forms]![Add TDY Tasking]![tbxEnd])) OR (((Leave.EndDate) Between [Forms]![Add TDY Tasking]![tbxStart] And [Forms]![Add TDY Tasking]![tbxEnd])))) AND ((Employees.Division) LIKE "CT*"));


Advise not to use special characters and spaces in names. If you do, must enclose in [].