Difficult Query; Trying to Show only One Record for only Two Field

[ryguy7272]

I am trying to figure out a way to set up a query which has many advertisers
per Sales Rep, but only one Sales Goal per Sales Rep. When I set the Field
‘Q4 Search Revenue’ to Sum or Group By, I see multiple (duplicate) results.
The same happens when I set the Field ‘Total – Q4’ to Sum or Group By. Is
there any way to return just one record for ‘Q4 Search Revenue’ and for
‘Total – Q4’? SQL below:

SELECT [Bookings and Pipe by Advertiser - RVP].Rep, [Bookings and Pipe by
Advertiser - RVP].RVP, tblRep_Summary.Region, [Bookings and Pipe by
Advertiser - RVP].Advertiser, Sum([Bookings and Pipe by Advertiser -
RVP]![Sum 08Q4 C1]+[Bookings and Pipe by Advertiser - RVP]![Sum 08Q4 C2]) AS
[Q4 Bookings by Advertiser], Sum(tblRep_Summary.[Total Revenue $]) AS [Q4
Search Revenue], Sum(tblGoals.[Total - Q408]) AS [SumOfTotal - Q408]
FROM (tblRep_Summary INNER JOIN (tblRVP_Mapping INNER JOIN [Bookings and
Pipe by Advertiser - RVP] ON tblRVP_Mapping.[Sales Rep] = [Bookings and Pipe
by Advertiser - RVP].Rep) ON tblRep_Summary.AE = tblRVP_Mapping.[Backyard
ID]) INNER JOIN tblGoals ON tblRVP_Mapping.[Backyard ID] = tblGoals.ID
GROUP BY [Bookings and Pipe by Advertiser - RVP].Rep, [Bookings and Pipe by
Advertiser - RVP].RVP, tblRep_Summary.Region, [Bookings and Pipe by
Advertiser - RVP].Advertiser;



Thanks,
Ryan---

 

[John W. Vinson]

GROUP BY [Bookings and Pipe by Advertiser - RVP].Rep, [Bookings and Pipe by
Advertiser - RVP].RVP, tblRep_Summary.Region, [Bookings and Pipe by
Advertiser - RVP].Advertiser;

I'm not sure how your data is structured... but if you group by these four
fields, you'll get one record for each combination of these four fields. Maybe
you don't want to group by some of them?