Could some one help me with a query to do the below.I have 2 tablestblCustomer:ID1 ID2 Fname LnameA1 1 Simth M.A1 2 Bob B.A2 1 David B.A3 2 Derek B.A4 3 Tammy N.A4 1 Ann J. tblRequest:ReID CustID1 CustID21 A1 12 A1 13 A2 14 A2 15 A3 26 A1 17 A2 18 A4 39 A1 210 A4 1 How can I get the result below:CustID1 CustID2 Fname Lname TotalRequestsA1 1 Smith M. 3A1 2 Bob B. 1A2 1 David B. 3A3 2 Derek B. 1A4 3 Tammy N. 1A4 1 Ann J. 1I have tried:SELECTA.[Fname],A.[Lname],A.[iD1],A.[iD2],COUNT(B.[CustID1]) AS TotalRequestFROM[tblCustomer] AS AINNER JOIN[tblRequest] AS BON B.[CustID2] = A.[iD2]WHERE A.[iD1] = B.[CustID1]GROUP BYA.[Fname],A.[Lname],A.[iD1],A.[iD2]ORDER BYA.[iD2],A.[iD1] But not getting the right results.Please help!Thanks in advanced.
