Create Pull Down Menu based on 2 tables

[Comtech]

Hello again. Does anybody have a solution to the following problem?

I have 2 tables (tblListofActivities and tblActivities) that are linked by a 1 to many relationship. The tblListofActivities (which is the 1 side) just lists all the events for the entire year. The second table (tblActivities) will only get data as each event occurs. So the tblListofActivities will have many entries where there are no entries with the same ActivityID as the other table because the event hasn't occurred yet.
I want to use a combo box that will only list the events that have occurred, and will not show those items that doesn't have an ActivityID associated with tblListofActivities .

Hope this makes sense because I'm not sure how else to word it.



Right now, I have the following line of code, but it lists all items in the table.

[Forms]![frmFindbyLastName]![LastName].RowSource = "SELECT DISTINCTROW [tblListofActivities].[ActivityDate] , [tblListofActivities].[ActivityName] FROM [tblListofActivities] ORDER BY [ActivityDate],[ActivityName];"


Any help would be appreciated. Thank you.
Comtech

[John_G]

Use an inner join to the tblActivities table. You don't need to select anything from tblActivities, but the inner join will only show rows where there are corresponding records in tblActivities:

"SELECT DISTINCTROW [tblListofActivities].[ActivityDate] , [tblListofActivities].[ActivityName] FROM [tblListofActivities] inner join [tblActivities] on [tblListofActivities].[ActivityID] = [tblActivities].[ActivityID] ORDER BY [ActivityDate],[ActivityName];"

Change ActivityID to the name(s) of the field(s) you use to relate the two tables.

[Comtech]

John_G
Thank you for the reply. I just had to indicate which table to ORDER [ActivityDate] and [ActivityName] by, but this worked perfect.\

Thanks again.
Comtech