query help needed - returning more than i want!

[jone]

I have two tables relating to queries sent in by customers and responses made to them. There can be multiple questions asked and multiple responses given.
What i'm trying to achieve is being able to calculate the time taken between question raised and the response.
However, i'm getting results that show all responses to each question asked. Each question and response has a unique identifier - ACTION_ID.

this is my query....

SELECT DISTINCT Question_Created.CUSTOMER_ID, Question_Created.Question_Date, Question_Created.ACTION_ID, Question_Responded.Response_Date, Question_Responded.ACTION_ID
FROM Question_Created LEFT JOIN Question_Responded ON Question_Created.CUSTOMER_ID = Question_Responded.CUSTOMER_ID
ORDER BY Question_Created.CUSTOMER_ID, Question_Created.ACTION_ID, Question_Responded.ACTION_ID;

I've attached the current outcome. The problems are the lines in yellow. This customer asked a Q on 2nd June and received a response the same day. They then asked a second question and received a second reply. However, 4 lines are being displayed. How do I ammend the query so that the "duplicates" in yellow arent outputed.

Hope this makes sense, and thanks in advance.

 

[arnelgp]

use sub-query:

SELECT Question_Created.CUSTOMER_ID, Question_Created.Question_Date, Question_Created.ACTION_ID, (SELECT TOP 1 Question_Responded.Response_Date FROM Question_Responded WHERE Question_Responded.CUSTOMER_ID = Question_Created.CUSTOMER_ID) AS Response_Date, (SELECT TOP 1 Question_Responded.ACTION_ID FROM Question_Responded WHERE Question_Responded.CUSTOMER_ID=Question_Created.CUSTOMER_ID) AS Response_ACTION_ID
FROM Question_Created;

 

[CJ_London]

you are using SELECT DISTINCT but since your records contain different responded actionID's they are distinct records. You can eliminate the duplicates by removing the responded actionID from the query, or if you need it, you need a basis for deciding which one you want. For example change the query to a GROUP BY query and select first/last/max or min for this field based on your requirement

The other issue is possibly your date field. Your post refers to time, but you are not showing any times. This may be due to formatting - the underlying data may be date/time but you are only showing the date part. So it is quite possible that changing to a group by query will still leave this as a problem because it works on the underlying data, not the formatted view you see.

 

[almahmood]

Hi,

As per the data, the query is giving you the correct result. If you see your data closely, you have two Action_ID for same customer in Question_Created table and two in Question_Responded table for the same customer and you are using join on Customer_ID field. Basically, your record is multiplying with the number of records for same customer. In order to fix this, I would suggest you change your architecture a bit like adding a new field to the Question_Created table to store the Question Action_ID.

 

[jone]

thanks guys.
That works very well Arnelgp.

On the original attached example, there is an entry in red where there is only one reponse but 2 questions raised. is it Possible to exclude this one?

 

[arnelgp]

tested the query i gave you but its
not working, i have to make a function
to show it correctly. but then we
cannot eliminate the other record that
has no response because the query
expects same number of records to
be returned (# of records in Question_Created).
the fix is to use Function. so you have to paste
the function in a Standard Module. your query:

SELECT Question_Created.Customer_ID, Question_Created.Question_Date, Question_Created.Action_ID, getResponseID(Customer_ID, Action_ID,"Date") As [Response Date],getResponseID(Customer_ID, Action_ID,"Action") As [Response Action_ID]
FROM Question_Created Order By 1, 2, 3;


the function:

Option Compare Database
Option Explicit

Public Function getResponseID(CustomerID As Variant, ActionID As Variant, Part As String) As Variant

    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim db As DAO.Database
    Dim lCount As Long
    Set db = CurrentDb
    
On Error Resume Next
    Set rs1 = db.OpenRecordset("SELECT Question_Created.Customer_ID, " & _
                            "Question_Created.Question_Date, Question_Created.Action_ID " & _
                            "FROM Question_Created " & _
                            "Where Question_Created.Customer_ID=" & SQLFix(CustomerID) & " " & _
                            "Order By 1,2,3 ASC;", dbOpenSnapshot)
    Set rs2 = db.OpenRecordset("SELECT Question_Responded.Customer_ID, " & _
                            "Question_Responded.Response_Date, Question_Responded.Action_ID " & _
                            "FROM Question_Responded " & _
                            "Where Question_Responded.Customer_ID=" & SQLFix(CustomerID) & " " & _
                            "Order By 1,2,3 ASC;", dbOpenSnapshot)
    
    lCount = 0
    With rs1
        .FindFirst "Action_ID=" & SQLFix(ActionID)
        While Not .BOF
            lCount = lCount + 1
            .MovePrevious
        Wend
        .Close
    End With
    Set rs1 = Nothing
    With rs2
        If Not (.BOF And .EOF) Then .MoveFirst
        lCount = lCount - 1
        While lCount > 0
            .MoveNext
            lCount = lCount - 1
        Wend
        If Part = "Date" Then getResponseID = rs2!Response_Date.value
        If Part = "Action" Then getResponseID = rs2!Action_ID.value
        .Close
    End With
    Set rs2 = Nothing
    Set db = Nothing
End Function


Public Function SQLFix(value As Variant) As String
    Select Case VarType(value)
    Case VbVarType.vbBoolean, VbVarType.vbByte, VbVarType.vbCurrency, _
        VbVarType.vbDouble, VbVarType.vbDecimal, VbVarType.vbInteger, _
        VbVarType.vbLong, VbVarType.vbLongLong, VbVarType.vbSingle
        SQLFix = value
    Case VbVarType.vbDate
        SQLFix = "#" & Format(value, "mm/dd/yyyy") & "#"
    Case VbVarType.vbString
        SQLFix = Chr(34) & value & Chr(34)
    Case VbVarType.vbNull
        SQLFix = "Null"
    End Select
End Function