Duplicate Records on Continuous Form

[colpamia]

Hello!

I have a lead management database with a main form being a "list view" of all the leads in the database. This form gives brief information on leads including company, contact name/number, industry, location etc. This list may be filtered using predefined filters accessible from a pop-up form, or on the fly using filters for each control (Location, Company Name, Date Last Called Etc.) Each lead may be clicked on to be opened up in another form "details view" which offers more details and the ability to be edited.

Both these main forms are based on two tables, one for the lead information, and one for the contact person information. This is necessary because each lead may have multiple contacts - a relationship has joined these two tables based on a shared LeadId number

The issue is with the "list view" continuous form: its recordset is based on a query that pulls data from the two tables, so as to join relevant information into one line. However, when a lead has more than one contact, the lead is displayed once for each contact, creating a lot of unnecessary lines on my form. What I want is only the first contact to be shown.

I have tried everything and am going crazy. Im sure this is an easy solution but I cant seem to get there! Any help in the right direction would be amazing.

Thank you

[NeoPa]

Actually, this isn't the most straightforward situation at all.

Unless you you want to play around with aggregating or using DISTINCT for a subquery, you'll want to aggregate the main query. All fields from the Lead side will use GROUP BY but those from the Contact side will use FIRST instead.