I am workng with an Access app and have run into some SQL behaviour I normally sort out using VBS in my ASP page.......I can not seem to get this issue sorted:

TBL_Support_Call is the main tble. For each record in this tble they can be multiple records in the TBL_Call_Staus_History table. EG if a call gets opened a records gets written containing the Status and the date. Then the records status may change four times therefore four records will be written the history table. How can I ensure the query only returns one TBL_Support_Call record no matter how many entries it has in the TBL_Call_Staus_History table?

Here is the working query which insists giving me duplicates according to the number of records in the history table


SELECT DISTINCT Format([DateTime],"dd/mm/yyyy") AS [Date Time], TBL_Support_Call.Ref, TBL_Support_Call.[Issue Summary],
TBL_Support_Call.Comments, TBL_FollowUp.Comments AS [Follow up Comments], TBL_Support_Call.[User Name],
TBL_Support_Call.BU, TBL_Support_Call.Issue, TBL_Support_Call.Link, TBL_Support_Call.Category,
TBL_Support_Call.[Functional Area], TBL_Support_Call.[Contact Source], TBL_Support_Call.Status,
Format([TBL_Call_Staus_History.CloseDate],"dd/mm/yyyy") AS [Closed Date], TBL_Support_Call.[Logged BY],
TBL_Support_Call.Owner FROM (TBL_Support_Call
LEFT JOIN TBL_FollowUp ON TBL_Support_Call.Ref = TBL_FollowUp.[Original Call Ref])
LEFT JOIN TBL_Call_Staus_History ON TBL_Support_Call.Ref = TBL_Call_Staus_History.refID
ORDER BY TBL_Support_Call.Ref DESC;


Further to my post if I remove:

Format([TBL_Call_Staus_History.CloseDate],"dd/mm/yyyy") AS [Closed Date], TBL_Support_Call.[Logged BY],

from the query my duplicate issue goes away. Can a better join or better SQL overcome this?

TYIA

__________________
Wind is your friend
Matt

Seems to work......

SELECT Format([DateTime],"dd/mm/yyyy") AS [Date Time]
, TBL_Support_Call.Ref
, TBL_Support_Call.[Issue Summary]
, TBL_Support_Call.Comments
, TBL_FollowUp.Comments AS [Follow up Comments]
, TBL_Support_Call.[User Name]
, TBL_Support_Call.BU
, TBL_Support_Call.Issue
, TBL_Support_Call.Link
, TBL_Support_Call.Category
, TBL_Support_Call.[Functional Area]
, TBL_Support_Call.[Contact Source]
, TBL_Support_Call.Status
, Format([mmmm.maxdate],"dd/mm/yyyy") AS [Closed Date]
, TBL_Support_Call.[Logged BY]
, TBL_Support_Call.Owner
, datediff("d",[DateTime],[mmmm.maxdate]) AS [Days Opened]
FROM (
TBL_Support_Call
LEFT OUTER
JOIN TBL_FollowUp
ON TBL_FollowUp.[Original Call Ref] = TBL_Support_Call.Ref
)
LEFT OUTER
JOIN ( SELECT refID
, MAX(CloseDate) AS maxdate
FROM TBL_Call_Staus_History
GROUP
BY refID ) AS mmmm
ON mmmm.refID = TBL_Support_Call.Ref
ORDER
BY TBL_Support_Call.Ref DESC;

__________________
Wind is your friend
Matt