I use a query to pull-out records that haven't yet come to past from my Appointment table.
I have a potential to have a meeting with four groups of people that are kept in different tables:
TenantsT
SuppliersT
ContactsT
EmployeesT
I am somewhat able to pull out the records from TenantsT and employeesT with the current statement:
Expand|Select|Wrap|Line Numbers
- SELECT AppointmentT.AppointmentID,
- AppointmentT.AppActive,
- AppointmentT.AppDate,
- AppointmentT.AppTimeFrom,
- AppointmentT.AppTimeTo,
- IIf(IsNull([AppointmentT].[EmployeeID]),[AppointmentT].[TenantID] & [TenantsT].[firstName] & " " & [TenantsT].[LastName],[employeesT].[FirstName] & " " & [EmployeesT].[LastName]) AS [With],
- AppointmentT.IamID
- FROM TenantsT RIGHT JOIN (SuppliersT RIGHT JOIN (ContactsT RIGHT JOIN (EmployeesT RIGHT JOIN AppointmentT ON (EmployeesT.EmployeeID = AppointmentT.EmployeeID) AND (EmployeesT.EmployeeID = AppointmentT.EmployeeID)) ON ContactsT.ContactID = AppointmentT.ContactID) ON SuppliersT.SupplierID = AppointmentT.SupplierID) ON TenantsT.TenantID = AppointmentT.TenantID
- WHERE (((AppointmentT.AppActive)=-1) AND ((AppointmentT.IamID)=[TempVars]![CurrentUserID]));
The challenge that I have is the null values and the iif statement.
simply described:
Do I have a meeting with a tenant OR supplier OR contact OR employee? or any combination thereof? Or Do I have a meeting with a Tenant AND a Supplier And an employee but not with a Contact? or any combination thereof?
The combination of people involved in a meeting can be quite easy in real life if you just say it: "Yes I have a meeting today with a Tenant and there will be a Supplier that will come over with an Employee and a Contact in order to do an estimate on a job."
Or
"Today I only have a meeting with two Employees.
not sure if I am phrasing this right or clear enough. please let me know and if you have an idea on how to write the statement. thanks.