Criteria question

[bnageshrao]

Hi

I have two tables one is called lead and another one is called contact. Companyid is the key in both tables. For each lead there can be either one lead or more than one lead. I would like to find out lead with more than one contacts. I did the grouping based on company ids and pulled the contact names. It shows for each companyid either one or more contacts. What criteria I should use to get just company with more than one contacts. Do I have to use Group By and along with other criteria like having etc. I just dont know the syntax. If some one can shed some light into this, I will be gratefull, thanks in advance. My query looks like this

SELECT Lead_Only_Fields.[Company ID], Contacst_Only_Fields.FirstName, Contacst_Only_Fields.LastName
FROM Lead_Only_Fields INNER JOIN Contacst_Only_Fields ON Lead_Only_Fields.[Company ID] = Contacst_Only_Fields.[Company ID]
GROUP BY Lead_Only_Fields.[Company ID], Contacst_Only_Fields.FirstName, Contacst_Only_Fields.LastName
HAVING ((Count([Lead_Only_Fields]![Company ID])>"1"));

I get an error data type mismatch, i might noe have written the right sql.

 

[spizotfl]

do you need "" around the 1 in the having clause?

 

[DotNetGnat]

nope..he doesnt need quotes around 1.

-DNG

 

[bnageshrao]

Even when i take that "" it does not run, any suggestions?

 

[Golom]

Try this

SELECT L.[Company ID], C.FirstName, C.LastName

FROM Lead_Only_Fields L INNER JOIN Contacst_Only_Fields C
     ON L.[Company ID] = C.[Company ID]

GROUP BY L.[Company ID], C.FirstName, C.LastName

HAVING Count(L.[Company ID])>1;

 

[PHV]

SELECT L.[Company ID], C.FirstName, C.LastName
FROM (Lead_Only_Fields AS L
INNER JOIN Contacst_Only_Fields AS C ON L.[Company ID] = C.[Company ID])
INNER JOIN (SELECT [Company ID] FROM Contacst_Only_Fields
GROUP BY [Company ID] HAVING Count(*)>1
) AS G ON L.[Company ID] = G.[Company ID]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886