Problem in query

[mustish1 via AccessMonster.com]

Hi: Can any one please tell me why this condition dont work:
DHGErrorOutlet.Outlets=[tbl_Events].[PPVVOD_Outlet]

There is one outlet in DHGErrorOutlet and two in PPVVOD_Outlet on same
ticketNum but it print two instead of print one

SELECT tbl_PPVResearch.TicketNum, LastValidTech.Account1, tbl_Events.txt,
LastValidTech.LstVldTech, tech_id.CORP, LastValidTech.ReportID
FROM (DHGErrorOutlet INNER JOIN (((LastValidTech INNER JOIN tbl_PPVResearch
ON LastValidTech.TicketNum = tbl_PPVResearch.TicketNum) INNER JOIN tbl_Events
ON tbl_PPVResearch.TicketNum = tbl_Events.TicketNum) INNER JOIN tech_id ON
LastValidTech.LstVldTech = tech_id.TECH) ON DHGErrorOutlet.TicketNum =
tbl_PPVResearch.TicketNum) INNER JOIN tbl_ValidDisputes ON tbl_PPVResearch.
TicketNum = tbl_ValidDisputes.TicketNum
WHERE (((tech_id.CORP)=Val(Left([LastValidTech].[Account1],5))) AND (
(LastValidTech.ReportID)="DHG") AND ((tbl_PPVResearch.Status)="Complete") AND
((DHGErrorOutlet.Outlets)=[tbl_Events].[PPVVOD_Outlet]))
GROUP BY tbl_PPVResearch.TicketNum, LastValidTech.Account1, tbl_Events.txt,
LastValidTech.LstVldTech, tech_id.CORP, LastValidTech.ReportID,
DHGErrorOutlet.Outlets;

Thanks.

 

[[MVP] S.Clark]

Your GROUP BY has these fields:
tbl_PPVResearch.TicketNum,
LastValidTech.Account1,
tbl_Events.txt,
LastValidTech.LstVldTech,
tech_id.CORP,
LastValidTech.ReportID,
DHGErrorOutlet.Outlets;

This means that the result is grouped by ALL FIVE of the pieces of data in
these fields. This is not very common, as many times, the data is going to
be unique across all 5. So, maybe re-evaluate and cut it down to 2 or 3.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html