By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,261 Members | 1,674 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,261 IT Pros & Developers. It's quick & easy.

Query Question for Appointments

P: 158
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:


I am somewhat able to pull out the records from TenantsT and employeesT with the current statement:
Expand|Select|Wrap|Line Numbers
  1. SELECT AppointmentT.AppointmentID,
  2.        AppointmentT.AppActive,
  3.        AppointmentT.AppDate,
  4.        AppointmentT.AppTimeFrom,
  5.        AppointmentT.AppTimeTo,
  6.        IIf(IsNull([AppointmentT].[EmployeeID]),[AppointmentT].[TenantID] & [TenantsT].[firstName] & " " & [TenantsT].[LastName],[employeesT].[FirstName] & " " & [EmployeesT].[LastName]) AS [With],
  7.        AppointmentT.IamID
  9. 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
  10. 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."


"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.
Mar 10 '10 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 15k+
P: 31,768
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.
Mar 10 '10 #2

P: 158
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 "concatanate" 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?
Mar 10 '10 #3

Expert Mod 15k+
P: 31,768
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.
Mar 10 '10 #4

P: 158
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
Mar 11 '10 #5

Expert Mod 15k+
P: 31,768
Check out Using "&" and "+" in WHERE Clause.

You'll need to replace line #6 with something like :
Expand|Select|Wrap|Line Numbers
  1.        Mid((', ' + ((EmployeesT.FirstName + ' ') & EmployeesT.FirstName)) &
  2.            (', ' + ((TenantsT.FirstName + ' ') & TenantsT.FirstName)) &
  3.            (', ' + ((SuppliersT.FirstName + ' ') & SuppliersT.FirstName)) &
  4.            (', ' + ((ContactsT.FirstName + ' ') & ContactsT.FirstName)),3,999) AS [With],
Mar 11 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.