Which function to use? IIF or DLookup

[jaad]

I have an appointment table where I keep date and time for an event(s) with different people or activity:

Tenants table
Suppliers Table
Employee Table
Project Table

I am having difficulty designing the front-end form to display a value in an expression field that I created called "WITH." The form is a simple datasheet and I am trying to keep it compact. So in other words I have to lookup in any table that are part of my query to see which one is not null and grab it so it shows into my [with] control.

this is what I have at the moment without the project table that I want to bring in:

 
SELECT AppointmentT.AppointmentID, AppointmentT.AppActive, AppointmentT.AppDate, AppointmentT.AppTimeFrom, AppointmentT.AppTimeTo, AppointmentT.IamID, AppointmentT.AppNote, IIf(IsNull([AppointmentT].[EmployeeID]),[AppointmentT].[TenantID] & " " & [TenantsT].[firstName] & " " & [TenantsT].[LastName],[employeesT].[FirstName] & " " & [EmployeesT].[LastName] & " " & [AppointmentT].[SupplierID] & " " & [suppliersT].[Company]) AS [With], AppointmentT.TenantID, AppointmentT.SupplierID, AppointmentT.EmployeeID
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]))
ORDER BY AppointmentT.AppDate;
 

When I have an appointment with an employee it works but it doesn't work with anyone else unless I include an employee with the other person or project that I deal with? any idea on how I can simplify this? thanks a bunch

Al

[orangeCat]

Do some reading on data normalization.
Are you really involved in Tenants, Suppliers,
Employees and Projects OR Appointments?
You may also want to look at Junction Tables.
Good luck.

[jaad]

I use the appointment form as a day timer for all my date and time driven activities. I could use one column for each Tenant, supplier, employee, project... but it makes the form too big and cluttered to look at.

Maybe my approach should be to hide any column that is null and somehow get the columns with value move to the left tightly packed side by side? can this be done?