Subform based on Query with 3 tables

[Yumi]

Problem: One of my two table-controls shows single appointment doubled.

The calendar is in two tables “calendar” for entries and “members” for participants. They are linked by the field “SCcID”. The form should allow to search for a type or location, show the entries and multiple participants

Created a query which basically works but when I run it It lists the date entry twice while 2 participants
SELECT “SCcalendar”., “SCcmembers”. FROM “SCcalendar”, “SCcmembers”, “SCcfilter” WHERE “SCcalendar”.“SCcID” = “SCcmembers”.“SCcID” AND UPPER ( “SCcalendar”.“SCclocation” ) LIKE UPPER ( “SCcfilter”.“Location” || ‘%’ ) OR “SCcalendar”.“SCcID” = “SCcmembers”.“SCcID” AND UPPER ( “SCcalendar”.“SCctype” ) LIKE UPPER ( “SCcfilter”.“Purpose” || ‘%’ )

On the subform linked to the query I have two table controls. As in the query it shows the appointment entry twice, the participants correct.

I had it working correctly, then experimented and now am unable to get it back!

[Wanderer]

I usually use joins, but for your query read on DISTINCT:

Selecting distinct combinations

Other options here

Select a unique combination of two columns

[Yumi]

Solved my problem. I removed all references to the “members” table from the query. The query now only filters the records from the “calendar” table. Moved the “Participants” table control to a subsubform and linked it to the “Appointments” table control by “SCcID” as Master/Slave.

Now only one entry per appointment and multiple participants. When table control 1 shows multiple appointments at the same day, location or purpose the related participants are listed in 2 when I select the row in 1.

My query now reads:
SELECT “SCcalendar”.* FROM “SCcalendar”, “SCcfilter” WHERE UPPER ( “SCcalendar”.“SCclocation” ) LIKE UPPER ( “SCcfilter”.“Location” || ‘%’ ) OR UPPER ( “SCcalendar”.“SCctype” ) LIKE UPPER ( “SCcfilter”.“Purpose” || ‘%’ ) OR UPPER ( “SCcalendar”.“SCcstartdate” ) LIKE UPPER ( “SCcfilter”.“StartDate” || ‘%’ )

Thanks Wanderer, Joints look interesting but I have to learn first before commenting.