SQL Getting duplicates

[mat41]

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;

TYIA

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?

[chazzy]

you're getting a row for each entry in TBL_Call_Staus_History it appears, can you confirm? is there a value in this table that notes whether it's "Closed"or not?

[mat41]

;;;you're getting a row for each entry in TBL_Call_Staus_History it appears, can you confirm?

Yes this is a one to many relationship. Yes there is a value indicating its status.

The solution which seems to be working:

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;

[mat41]

mmmmmm the SQL above was working without issue until today when I run it I know get 317,000 records. I cant for the life of me work out why.

TBL_Support_Call only has 94 records
TBL_FollowUp only has 38 records
TBL_Call_Staus_History has 140 records


my original SQL (which is in my first post) gives me 105 records. Of course the objective of the report is to give as many records as there is in the TBL_Support_Call table.


Any ideas?

[chazzy]

i think you should limit your left join to cases where the history's whatever column that you didn't tell me its name is marked as "CLOSED" since you want a close date. this will give you one status for each call logged.

[mat41]

mmmm the query seems to be working now. Get me out of Access.....:mad: