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

Duplicate Records on Continuous Form

P: 1

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
Aug 20 '15 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 15k+
P: 31,433
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.
Aug 20 '15 #2

Post your reply

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