Query JOIN issue

There really is no way to do this easily with a query. You would really have to build a function to populate a table and then report/export/whatever off that table.

Can you give me some ideas as to how to do that?

I'm out on vacation until Monday.  I'll try to get some eyes on this to help you out, if not I'll write something up Monday.  BTW, how much experience do you have w/ VBA?

Thanks.  Moderate amount of VBA experience I'd say.  Been doing quite a bit of it for the last couple of years.

Thanks.  I'm making progress.  Done the first part, but am stuck on "If the CaseOwnerText changes, stop moving to the next record" because I'm getting this table at the moment, and the last 3 rows should still be MotOps - CustSer.

CaseOwner      MonthYear      Quantity
Mot Ops - Cust Ser      Jan-06      0
Mot Ops - Cust Ser      Feb-06      0
Mot Ops - Cust Ser      Mar-06      1
Mot Ops - Cust Ser      Apr-06      4
Mot Ops - Cust Ser      May-06      2
Mot Ops - Cust Ser      Jun-06      5
Mot Ops - Cust Ser      Jul-06      0
Mot Ops - Cust Ser      Aug-06      1
Mot Ops - Cust Ser      Sep-06      1
Mot Ops - Fleet      Oct-06      0
Mot Ops - Fleet      Nov-06      0
Mot Ops - Fleet      Dec-06      0

The whole sub is posted below.

Private Sub OutstandingCases()

Dim strOutstandingSQL As String
Dim rst As ADODB.Recordset
Dim i As Integer
Dim strTempSQL As String
Dim strCaseOwner As String
Dim strMonthYear As String
Dim intQuantity As Integer

strOutstandingSQL = "SELECT co.CaseOwnerText, FORMAT(DateDue,'mmm-yy') AS [Month], Month(DateDue) AS MonthVal, Count(oc.CRN) AS Quantity " & _
                 "FROM dbo_tbl_CaseOwner AS co LEFT JOIN " & _
                 "(SELECT * FROM dbo_Vi_OutstandingCases " & _
                 "WHERE ( DATEPART('m', DateDue) < 10) AND (DATEPART('yyyy', DateDue) = 2006) ) AS oc ON oc.CaseOwnerID=co.CaseOwnerID " & _
                 "GROUP BY co.CaseOwnerText, Month(DateDue), FORMAT(DateDue,'mmm-yy') " & _
                 "HAVING co.CaseOwnerText In ('Mot Ops - Cust Ser','Mot Ops - Fleet','Mot Ops - Legal & Sec','Motability')"

Set rst = New ADODB.Recordset
rst.Open strOutstandingSQL, CurrentProject.Connection

rst.MoveFirst
DoCmd.SetWarnings False
For i = 1 To 12
    If i = rst.Fields(2) Then
        strCaseOwner = rst.Fields(0)
        strMonthYear = rst.Fields(1)
        intQuantity = rst.Fields(3)
       
        strTempSQL = "INSERT INTO tblTempOutstandingCases (CaseOwner, MonthYear, Quantity) " & _
                  "VALUES ('" & strCaseOwner & "' , '" & strMonthYear & "' , " & intQuantity & ")"
        DoCmd.RunSQL strTempSQL
        rst.MoveNext
    Else
        Select Case i
            Case 1
                strMonthYear = "Jan-06"
            Case 2
                strMonthYear = "Feb-06"
            Case 3
                strMonthYear = "Mar-06"
            Case 4
                strMonthYear = "Apr-06"
            Case 5
                strMonthYear = "May-06"
            Case 6
                strMonthYear = "Jun-06"
            Case 7
                strMonthYear = "Jul-06"
            Case 8
                strMonthYear = "Aug-06"
            Case 9
                strMonthYear = "Sep-06"
            Case 10
                strMonthYear = "Oct-06"
            Case 11
                strMonthYear = "Nov-06"
            Case 12
                strMonthYear = "Dec-06"
        End Select
       
        strCaseOwner = rst.Fields(0)
        intQuantity = 0
               
        strTempSQL = "INSERT INTO tblTempOutstandingCases (CaseOwner, MonthYear, Quantity) " & _
                  "VALUES ('" & strCaseOwner & "' , '" & strMonthYear & "' , " & intQuantity & ")"
        DoCmd.RunSQL strTempSQL
    End If
Next i
DoCmd.SetWarnings True

End Sub

You may need to adjust it to use

  Do
    ..
  Loop Until .. Or rst.EOF

and use a counter within the loop. My idea is that the counter can move to 13 which will bring you passed a CaseOwner with entries for all 12 months. If the loop encounters a new CaseOwner, the counter should be reset to 1 as for January.

On a minor note I would advice on storing the month-year as a true datevalue like 2006-10-1 as it can be formatted to your liking and you could avoid the lengthy Select .. End statement.

/gustav

I've tried changing it so I've got

i = 1
Do Until i = 13 Or rst.EOF
...
...
i = i + 1
Loop

But the problem is in the line rst.MoveNext because it moves onto a new CaseOwner before all the months have been run through ... I don't understand how to sort that.

Sorted it ... not sure how elegant it is but it works.  I put the Do Loop inside a For Next (as I know there will only ever be 4 CaseOwners) and added a test to see if the new CaseOwner is different from the last.  Any comments?  I'll try to remove the Select statement like you said.

For j = 1 To 4
    i = 1
    Do Until i = 13 Or rst.EOF
        If i = rst.Fields(2) Then
            strCaseOwner = rst.Fields(0)
            strMonthYear = rst.Fields(1)
            intQuantity = rst.Fields(3)
           
            strTempSQL = "INSERT INTO tblTempOutstandingCases (CaseOwner, MonthYear, Quantity) " & _
                      "VALUES ('" & strCaseOwner & "' , '" & strMonthYear & "' , " & intQuantity & ")"
            DoCmd.RunSQL strTempSQL
           
            rst.MoveNext
            If rst.Fields(0) <> strCaseOwner Then
                rst.MovePrevious
            End If

That's a nice method!

What I further would recommend is to open another recordset with tblTempOutstandingCases and add new records to this directly in VBA. That will save you all the DoCmd.RunSQL statements which are slow.

/gustav

Thanks Gustav.  Appreciate your help.

You are welcome!

/gustav

Thanks for jumping in Gustav.