How to make a drop down list (list box)in an MS Acess Query with values from two different tables.

Question

I need the drop down box above to display the date as shown and the ClassTypeDesc as you can see above the dropdown list shows 1/12/2010 twice. They have different ClassTypes Assigned to them.

The drop down list should show:
1/12/2010 ACLS-I Day One AM
1/12/2010 ACLS-I Day One PM

I need to know the statement to put in the Row Source Box on the lookup tab in the Field Properties to make this work.

Related Question on Making a drop down list

Answer 1

As you already have the right number of rows, you just need to concatenate enough fields to make it more useful, so your query would be something like:

SELECT c.Date + ' ' + s.ClassTypeDesc AS YourFieldName
FROM...

Answer 2

There's no need to concatenate the two columns. Based on the diagram, the SQL for your lookup combo box should look like this:

  SELECT tblClassSession.SessionID, tblClassSession.Date, tblSessionType.ClassTypeDesc
  FROM tblClassSession INNER JOIN tblSessionType 
    ON tblClassSession.SessionTypeID = tblSessionType.SessionTypeID;

Then in the properties for your lookup combo box, change ColumnCount to 3, and Column Widths to 0 (if you want to size the other columns, change Column Width sto something like 0";.75";1.5", and the List Width property to 2.25").

I may have gotten some of the field names wrong, but that's the basic idea.

(also, you probably really ought to rename tblClassSession.Date to tblClassSession.SessionDate so you don't run into problems with the fact that Date is a reserved word)