Simple Question - Driving me Crazy

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David - 18 May 2005 18:46 GMT

Hello all,
Seems simple, but can't get it to work.
Two tables with a 1 to many relationship.
tbl_Projects (the one)
tbl_Activity_Log (the many)
LINKED FIELD IS SR

Goal: Retrieve a list of projects from tbl_Projects where the words "Merger
Day" appear in the tbl_Activity_Log without having duplicate SR's listed in
the data retrieved from query.

ISSUE: I do not want duplicate projects from the tbl_Projects table, it
lists a single project multiple times. If a project (SR) has the entry
"Merger Day" numerous times in the tbl_Activity_Log, the SR number will be
repeated that many times.

Here is the query in SQL....any assistance is GREATLY appreciated.

SELECT tbl_Projects.SR, tbl_Projects.Project_Name,
tbl_Projects.Issues_Comments AS Scope, tbl_Projects.CCAT_Analyst,
Switch([CCAT_Impact]="1","Yes",[CCAT_Impact]="2","No") AS [CCAT-Impact],
tbl_Activity_Log.Comments, tbl_Projects.SCS_PM, tbl_Projects.ITPM,
Switch([Regression]="1","Yes",[Regression]="2","No",[Regression]="3","?") AS
[CCAT Regression], tbl_Projects.Release
FROM tbl_Projects INNER JOIN tbl_Activity_Log ON tbl_Projects.SR =
tbl_Activity_Log.SR
GROUP BY tbl_Projects.SR, tbl_Projects.Project_Name,
tbl_Projects.Issues_Comments, tbl_Projects.CCAT_Analyst,
Switch([CCAT_Impact]="1","Yes",[CCAT_Impact]="2","No"),
tbl_Activity_Log.Comments, tbl_Projects.SCS_PM, tbl_Projects.ITPM,
Switch([Regression]="1","Yes",[Regression]="2","No",[Regression]="3","?"),
tbl_Projects.Release
HAVING (((tbl_Activity_Log.Comments) Like "*" & "Merger Day" & "*"))
ORDER BY tbl_Projects.SR;

Reply to this Message

Martin J. - 18 May 2005 19:50 GMT

Hi,

the problem is, that you use a field from the tbl_activity_log
(comments). For this reason every match of tbl_activiy_log is added to
the results. Try to
select first (tbl_activity_log.comments) , so only one comment is
returned, or delete this column from your query

Martin

Reply to this Message

John Spencer (MVP) - 19 May 2005 00:55 GMT

Using your current query, do NOT display any fields from the Tbl_Activity_Log.
OR Use First(tableName.Fieldname) to select one of the values in the group randomly.

You can also achieve the result by using a Select query that looks like:

SELECT tbl_Projects.SR, tbl_Projects.Project_Name,
tbl_Projects.Issues_Comments AS Scope, tbl_Projects.CCAT_Analyst,
Switch([CCAT_Impact]="1","Yes",[CCAT_Impact]="2","No") AS [CCAT-Impact],
tbl_Projects.SCS_PM, tbl_Projects.ITPM,
Switch([Regression]="1","Yes",[Regression]="2","No",[Regression]="3","?") AS
[CCAT Regression], tbl_Projects.Release
FROM tbl_Projects
WHERE tbl_Projects.SR IN
(SELECT A.SR FROM tbl_Activity_Log AS A
WHERE (A.Comments) Like "*" & "Merger Day" & "*")
ORDER BY tbl_Projects.SR

That assumes that CCAT_IMPACT and Regression are fields in tbl_Projects and not
in tbl_activity_log

> Hello all,
> Seems simple, but can't get it to work.
[quoted text clipped - 30 lines]
> HAVING (((tbl_Activity_Log.Comments) Like "*" & "Merger Day" & "*"))
> ORDER BY tbl_Projects.SR;

Reply to this Message