Combining three queries with missing data

[tbonehwd]

First off I would like to thank the people on here that have been so helpful I have learned so much since I started posting however when you see the mess I have below please dont laugh anyway the code below builds and execute three queries. The first is to get a list of customers by cust-type and calculates thier lifetime sales, the second query does the same as above but only current year and the third is for the prior year. I am trying to figure out how to bring them all together and if the customer does not have sales for that year I still want them to display with a zero for that year. But if they have no sales for the current year they won't come up at all then I can't tie them to the lifetime. I hope this make some sense to someone if you have any questions please let me know.

Thanks,

Tbonehwd

Private Sub btnEXECUTE_Click()
' Pointer to error handler
    On Error GoTo cmdOK_Click_err
' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strADVCTYPE As String
    Dim strADVITM As String
    Dim strADVSAC As String
    Dim strADVCPYYEAR As String
    Dim strFRMDATE As String
    Dim strTODATE As String
    Dim strSQL As String
    Dim strFRMDATEM1Y As String
    Dim strTODATEM1Y As String

' Identify the database and assign it to the variable
    Set db = CurrentDb

' Get the values from the combo boxes
        If IsNull(Me.cmbCUSTTYPE.Value) Then
        'MsgBox "You must specify at least one Customer Type. Press OK and enter a Customer Type Code"
        'Exit Sub
    Else
        strADVCTYPE = "='" & Me.cmbCUSTTYPE.Value & "' "
    End If
        If IsNull(Me.txtFRMDATE.Value) Then
        MsgBox "You must specify a Start Date. Press OK and enter the Start Date"
        Exit Sub
    Else
        strFRMDATE = ">=#" & Me.txtFRMDATE.Value & "# "
    End If
    If IsNull(Me.txtTODATE.Value) Then
        MsgBox "You must specify a End Date. Press OK and enter the End Date"
        Exit Sub
    Else
        strTODATE = "<=#" & Me.txtTODATE.Value & "# "
    End If
'This string takes the year the user enters and subtracts 1 year
    strFRMDATEM1Y = ">=#" & DateAdd("YYYY", -1, Me.txtFRMDATE.Value) & "# "
    strTODATEM1Y = "<=#" & DateAdd("YYYY", -1, Me.txtTODATE.Value) & "# "

    If Not QueryExists("qrySALESTYPECUST") Then
        Set qdf = db.CreateQueryDef("qrySALESTYPECUST")
    Else
        Set qdf = db.QueryDefs("qrySALESTYPECUST")
    End If

strSQL = "SELECT Last(CDSADR_M.CMP_NME) As COMPANY, CDSADR_M.CTM_NBR, CDSCTM_M.CTM_TYP " & _
         ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,PROOLN_M.QTY_SHP*-1)) AS LIFE_UNITS " & _
         ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.ITM_NET,PROOLN_M.ITM_NET*-1)) AS LIFE_DOLLARS " & _
         "FROM ((PROOLN_M INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM=PROORD_M.ORD_NUM)"  & _
         "INNER JOIN CDSCTM_M ON PROORD_M.CTM_NBR = CDSCTM_M.CTM_NBR) " & _
         "INNER JOIN CDSADR_M ON CDSCTM_M.CTM_NBR=CDSADR_M.CTM_NBR  " & _
         "WHERE PROORD_M.ORD_STA IN ('F','B') AND PROORD_M.ORD_TYPE IN ('C','I','P','V') " & _
         "AND CDSADR_M.ADR_CDE='STANDARD' AND CDSADR_M.ADR_FLG='0' " & _
         "AND CDSCTM_M.CTM_TYP " & strADVCTYPE & _
         "GROUP BY CDSADR_M.CTM_NBR, CDSCTM_M.CTM_TYP ;"

    qdf.SQL = strSQL
    DoCmd.Echo False

    If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qrySALESTYPECUST") = acObjStateOpen Then
        DoCmd.Close acQuery, "qrySALESTYPECUST"
    End If

'This area is for the second query that provides the prior year data
    If Not QueryExists("qrySALESTYPECUST1") Then
       Set qdf = db.CreateQueryDef("qrySALESTYPECUST1")
    Else
        Set qdf = db.QueryDefs("qrySALESTYPECUST1")
    End If

strSQL = "SELECT Last(CDSADR_M.CMP_NME) As COMPANY, CDSADR_M.CTM_NBR, CDSCTM_M.CTM_TYP " & _
         ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,PROOLN_M.QTY_SHP*-1)) AS CURR_YR_UNITS " & _
         ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.ITM_NET,PROOLN_M.ITM_NET*-1)) AS CURR_YR_DOLLARS " & _
         "FROM ((PROOLN_M INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM=PROORD_M.ORD_NUM) " & _
         "INNER JOIN CDSCTM_M ON PROORD_M.CTM_NBR = CDSCTM_M.CTM_NBR) " & _
         "INNER JOIN CDSADR_M ON CDSCTM_M.CTM_NBR=CDSADR_M.CTM_NBR  " & _
         "WHERE PROORD_M.ORD_STA IN ('F','B') AND PROORD_M.ORD_TYPE IN ('C','I','P','V') " & _
         "AND CDSADR_M.ADR_CDE='STANDARD' AND CDSADR_M.ADR_FLG='0' " & _
         "AND CDSCTM_M.CTM_TYP " & strADVCTYPE & _
         "AND PROORD_M.ACT_DTE " & strFRMDATE & _
         "AND PROORD_M.ACT_DTE " & strTODATE & _
         "GROUP BY CDSADR_M.CTM_NBR, CDSCTM_M.CTM_TYP ;"

    If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qrySALESTYPECUST1") = acObjStateOpen Then
        DoCmd.Close acQuery, "qrySALESTYPECUST1"
    End If
qdf.SQL = strSQL
DoCmd.Echo False
'End second query
'This area is for the THIRD query that provides the prior year data
    If Not QueryExists("qrySALESTYPECUST2") Then
       Set qdf = db.CreateQueryDef("qrySALESTYPECUST2")
    Else
        Set qdf = db.QueryDefs("qrySALESTYPECUST2")
    End If

strSQL = "SELECT Last(CDSADR_M.CMP_NME) As COMPANY, CDSADR_M.CTM_NBR, CDSCTM_M.CTM_TYP " & _
         ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,PROOLN_M.QTY_SHP*-1)) AS PRIOR_YR_UNITS " & _
         ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.ITM_NET,PROOLN_M.ITM_NET*-1)) AS PRIOR_YR_DOLLARS " & _
         "FROM ((PROOLN_M INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM=PROORD_M.ORD_NUM) " & _
         "INNER JOIN CDSCTM_M ON PROORD_M.CTM_NBR = CDSCTM_M.CTM_NBR) " & _
         "INNER JOIN CDSADR_M ON CDSCTM_M.CTM_NBR=CDSADR_M.CTM_NBR  " & _
         "WHERE PROORD_M.ORD_STA IN ('F','B') AND PROORD_M.ORD_TYPE IN ('C','I','P','V') " & _
         "AND CDSADR_M.ADR_CDE='STANDARD' AND CDSADR_M.ADR_FLG='0' " & _
         "AND CDSCTM_M.CTM_TYP " & strADVCTYPE & _
         "AND PROORD_M.ACT_DTE " & strFRMDATEM1Y & _
         "AND PROORD_M.ACT_DTE " & strTODATEM1Y & _
         "GROUP BY CDSADR_M.CTM_NBR, CDSCTM_M.CTM_TYP ;"

    If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qrySALESTYPECUST2") = acObjStateOpen Then
        DoCmd.Close acQuery, "qrySALESTYPECUST2"
    End If
qdf.SQL = strSQL
DoCmd.Echo False


' Open the query
    If (Me.chkEXCEL = True) Then
    DoCmd.OutputTo acOutputQuery, "qrySALESTYPECUST2", A_FORMATXLS, , True
    Else
    DoCmd.OpenQuery "qrySALESTYPECUST"
    End If
cmdOK_Click_exit:
' Turn on screen updating
    DoCmd.Echo True
' Clear the object variables
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub
cmdOK_Click_err:
' Handle errors
    MsgBox "An unexpected error has occurred." & _
        vbCrLf & "Please note of the following details:" & _
        vbCrLf & "Error Number: " & Err.Number & _
        vbCrLf & "Description: " & Err.Description _
        , vbCritical, "Error"
    Resume cmdOK_Click_exit
End Sub

 

[Sashanan2]

I can't really make sense of the queries I'm afraid (the column names alone give me headaches) but if the problem is getting no results back if there are no sales in a given year, you can solve that by using an outer join instead of an inner join.

I see you linking several tables together in your queries, I assume one of them is your sales table where the products that said customer has bought are stored. Rather than do an inner join on that table, try a left outer join (assuming you specify the customer table first and then the sales table; if it's the other way around, do a right outer join or just flip the tables around). An outer join will ensure that the query does return a record for the customer even if there are no associated sales records; whereas an inner join will not return results unless the record exists in both tables. Sounds like that's what you're running into.



"Any fool can defend his or her mistakes; and most fools do." -- Dale Carnegie