Query Question for Appointments

[jaad]

Good day all,

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:

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]));

I would like to be able to also pull out SuppliersT and ContactsT.

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.

[NeoPa]

Well, it seems AppointmentT is the fundamental table you need. If you connect all the others into it with LEFT JOINS then all appointments will be included, and so will all the other items if, and only if, they exist.

It seems as simple as that, but maybe I'm missing something.

[jaad]

Yes you are right this is how I would do it if I was only looking at the query result. What I should have said is how can I "concatanat e" every result into one field.?

You see I have an appointment form on my main page that shows all current appointment that have not yet come to past. If I simply use the foreign key of the other tables then I get multiple records pulled out for one appointment. I am a tad constricted with space on my main page and trying to keep it as compact and as simple as I can with only one line showing all the people that I have appointment with in one record. Does this make more sense?

[NeoPa]

Now you're confusing me Jaad. Do you mean field or record? It doesn't mean the same thing. In fact it is entirely different.

I see no reason why you would have the problem you describe in the scenario I outlined. Perhaps you could explain more clearly. Be especially sure to use the correct terms if possible. Using the wrong term is a recipe for confusion.

[jaad]

i meant field. a control or more precisely a combox. concatenate all that I have appointment with into a single field on the same record. if you look at the statement on the first like of code you will see that I can show either employees and tenants or both or a single tenant along or a single employee alone. I would like to ad contacts and suppliers to that statement

[NeoPa]

Check out Using "&" and "+" in WHERE Clause.

You'll need to replace line #6 with something like :

       Mid((', ' + ((EmployeesT.FirstName + ' ') & EmployeesT.FirstName)) &
           (', ' + ((TenantsT.FirstName + ' ') & TenantsT.FirstName)) &
           (', ' + ((SuppliersT.FirstName + ' ') & SuppliersT.FirstName)) &
           (', ' + ((ContactsT.FirstName + ' ') & ContactsT.FirstName)),3,999) AS [With],