Last 3 records by group

[Rabbit]

Then the syntax error you're getting shouldn't be the same. What's the new syntax error?

[moishy101]

The syntax error is on Count(qTT1.*) < 4

[NeoPa]

Moishy101:
but I still get the syntax error on Count(qTT1.*) < 4 even after changing it to Count(*) < 4

Just the Count() problem now Rabbit. I'm just rushing out now, so if you can find my error before I get back you'll save me a task ;-)

[NeoPa]

@Moishy101.
Actually, why don't you post all of your SQL, exactly as you have it now using Copy and Paste, and give us the names of all relevant objects if different from what I used in my suggestion. That way we can see if anything's amiss.

[moishy101]

Copy and paste (plus removing the formatting) is exactly what I did to your sql and table in post #23 (after the correction), so that post will show exactly what I have. Names of relevant objects are as you suggested (I don't ask not to listen!)

[ADezii]

@moishy101:
I actually have a Hybrid (Query/VBA) Solution that you may/may not wish to utilize. I'll Post all of the relevant Details later on should you be interested. If you are not interested in this approach, and wish to go strictly SQL, simply let me know before then.

[moishy101]

@ADezii:

I'm always open to all options, there's more than one way to skin a cat;-)

I actually would like to see it, I believe in broadening my horizons.

[ADezii]

Using NeoPa's Post# 23 as a foundation, I created a simple Query that will accomplish the Task, and produce the desired results. The Query consists of only 2 Fields: (UNIQUE [ClientID]s) ASC and a Calculated Field (TOP_3_Per_Clie nt:) fCalcTOP3([ClientID]) that passes the [ClientID] to a Public Function. This Function (fCalcTOP3) contains all the Logic, and returns a Delimited String consisting of the Transaction Numbers and Hebrew Months for the TOP 3 Months sorted ASC by Month. I'll Post the SQL, Function Definition, and the Results based on my Sample Data. To further simplify matters, I've attached the Demo Database that I used for this Thread.

The obvious Disadvantages are:

1. Because of the JOIN involved, the actual Hebrew Month Names must match exactly with those in tblHebrewMonthO rder, or they will not be included in the Analysis. A couple of simple, Update Queries, can easily fix this problem so that all Months conform.
2. Processing Time may be significant for a large Data set, which you have indicated is the case.

Advantage:

1. OUTPUT can easily be converted to a number of Formats, namely: a Results Table, Delimited String in a Query, Debug Window, Text File, etc.

1. SQL:
   

   SELECT DISTINCT tblTransaction.ClientID, fCalcTOP3([ClientID]) AS TOP_3_Per_Client
   FROM tblTransaction
   GROUP BY tblTransaction.ClientID
   ORDER BY tblTransaction.ClientID;

2. Function Definition:
   

   Public Function fCalcTOP3(bytClientID As Byte)
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim strBuild As String
   
   strBuild = ""       'INITIALIZE
   
   'Are there at least 3 Records for the Passed ClientID
   If DCount("*", "tblTransaction", "[ClientID] = " & bytClientID) < 3 Then
     fCalcTOP3 = "N/A"
       Exit Function
   End If
   
   strSQL = "SELECT tblTransaction.ClientID, tblTransaction.TransactionID, tblHebrewMonthOrder.MonthName, " & _
            "tblHebrewMonthOrder.Priority FROM tblHebrewMonthOrder INNER JOIN tblTransaction ON " & _
            "tblHebrewMonthOrder.MonthName = tblTransaction.TransactionMonth WHERE tblTransaction.ClientID = " & _
             bytClientID & " ORDER BY tblTransaction.ClientID,tblHebrewMonthOrder.Priority;"
   
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
   
   rst.MoveLast: rst.MoveFirst
   
   'The TOP 3 for this ClientID will actually be the Last 3 Records, Move to 3rd from Bottom
   rst.Move rst.RecordCount - 3        'TOP of the TOP 3
   
   Do While Not rst.EOF
     strBuild = strBuild & rst!TransactionID & "," & rst!MonthName & " | "
       rst.MoveNext
   Loop
   
   fCalcTOP3 = Left$(strBuild, Len(strBuild) - 3)
   
   rst.Close
   Set rst = Nothing
   End Function

3. Query OUTPUT:
   

   ClientID	TOP_3_Per_Client
   1	       9870561,Adar I | 5374530,Nissan | 2323456,Tammuz
   2	       5376892,Nissan | 5327891,Sivan | 6423568,Tammuz
   3	       N/A
   4	       N/A
   5	       991254,Cheshvan | 345678,Sivan | 987654,Av

OOPs!: Code Line# 23 can be removed from the Attachment, since it was there for testing purposes only.

[moishy101]

@ADezii:

I greatly appreciate your help. There seems to be one problem, if a client has less than 3 records it won't show up (N/A), is it possible if there are more than 0 records and less than 3 records, to show the records and nothing where empty?

[ADezii]

moishy101:

is it possible if there are more than 0 records and less than 3 records, to show the records and nothing where empty?

I can build that into the Logic and get to you with the Revisions. Special conditions such as these should have been stated earlier, especially since it effects the gentlemen working on the SQL approach. The changes to the Code are relatively easy, not so for the SQL involved.

[Rabbit]

Try modifying Neo's final query to this.

SELECT   qTT1.ClientID 
       , qTT1.TransactionNumber 
       , qTT1.MonthOrder 
FROM     [qryTransactionHebrew] AS [qTT1] 
         INNER JOIN 
         [qryTransactionHebrew] AS [qTT2] 
  ON     (qTT1.ClientID = qTT2.ClientID) 
 AND     (qTT1.MonthOrder >= qTT2.MonthOrder) 
GROUP BY qTT1.ClientID 
       , qTT1.TransactionNumber 
       , qTT1.MonthOrder 
HAVING   Count(*) < 4 
ORDER BY qTT1.ClientID ASC 
       , qTT1.MonthOrder DESC

[ADezii]

Revised Code to allow for 0, >0 and <3, and >=3 Records per ClientID:

Public Function fCalcTOP3(bytClientID As Byte)
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strBuild As String
Dim lngNumOfRecs As Long

strBuild = ""       'INITIALIZE

lngNumOfRecs = DCount("*", "tblTransaction", "[ClientID] = " & bytClientID)

strSQL = "SELECT tblTransaction.ClientID, tblTransaction.TransactionID, tblHebrewMonthOrder.MonthName, " & _
         "tblHebrewMonthOrder.Priority FROM tblHebrewMonthOrder INNER JOIN tblTransaction ON " & _
         "tblHebrewMonthOrder.MonthName = tblTransaction.TransactionMonth WHERE tblTransaction.ClientID = " & _
          bytClientID & " ORDER BY tblTransaction.ClientID,tblHebrewMonthOrder.Priority;"
          
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)

Select Case lngNumOfRecs
  Case 0            'Should never happen
    fCalcTOP3 = ""
  Case Is < 3
    With rst
      Do While Not rst.EOF
        strBuild = strBuild & rst!TransactionID & "," & rst!MonthName & " | "
          rst.MoveNext
      Loop
    End With
  Case Else
    'The TOP 3 for this ClientID will actually be the Last 3 Records
    rst.Move rst.RecordCount - 3        'TOP of the TOP 3

    Do While Not rst.EOF
      strBuild = strBuild & rst!TransactionID & "," & rst!MonthName & " | "
        rst.MoveNext
    Loop
End Select

fCalcTOP3 = Left$(strBuild, Len(strBuild) - 3)

rst.Close
Set rst = Nothing
End Function

[moishy101]

Thank you!
Can it be modified so the output is so:

ClientID    TOP_3_Per_Client
1           9870561,Adar I | 5374530,Nissan | 2323456,Tammuz
2           5376892,Nissan | 5327891,Sivan | 6423568,Tammuz
3           1123456,Tishrei | 8754310,Adar II | 
4           9875415,Shvat |  | 
5           991254,Cheshvan | 345678,Sivan | 987654,Av

[ADezii]

You are really making me work for this one! (LOL)

Public Function fCalcTOP3(bytClientID As Byte)
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strBuild As String
Dim lngNumOfRecs As Long

strBuild = ""       'INITIALIZE

lngNumOfRecs = DCount("*", "tblTransaction", "[ClientID] = " & bytClientID)

strSQL = "SELECT tblTransaction.ClientID, tblTransaction.TransactionID, tblHebrewMonthOrder.MonthName, " & _
         "tblHebrewMonthOrder.Priority FROM tblHebrewMonthOrder INNER JOIN tblTransaction ON " & _
         "tblHebrewMonthOrder.MonthName = tblTransaction.TransactionMonth WHERE tblTransaction.ClientID = " & _
          bytClientID & " ORDER BY tblTransaction.ClientID,tblHebrewMonthOrder.Priority;"
          
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)

Select Case lngNumOfRecs
  Case 0            'Should never happen
    fCalcTOP3 = ""
  Case Is < 3
    With rst
      Do While Not .EOF
        If lngNumOfRecs = 1 Then
          strBuild = !TransactionID & "," & !MonthName & " |   |"
        Else
          strBuild = strBuild & !TransactionID & ", " & !MonthName & " |"
        End If
          .MoveNext
      Loop
    End With
  Case Else
    With rst
      'The TOP 3 for this ClientID will actually be the Last 3 Records
      rst.Move rst.RecordCount - 3        'TOP of the TOP 3

      Do While Not .EOF
        strBuild = strBuild & !TransactionID & "," & !MonthName & " | "
          .MoveNext
      Loop
      strBuild = Left$(strBuild, Len(strBuild) - 3)
    End With
End Select

fCalcTOP3 = strBuild

rst.Close
Set rst = Nothing
End Function

P.S. - If you intend on using this approach, or at least test it, I would like to know how long it takes to process your 150,000 Records.

[NeoPa]

ADezii:
Special conditions such as these should have been stated earlier, especially since it effects the gentlemen working on the SQL approach. The changes to the Code are relatively easy, not so for the SQL involved.

True indeed. As it happens though, I did anticipate this when looking at the problem earlier.

Thanks Rabbit. The original SELECT line should still be fine, but using [qryTransactionT op] (twice) as the input for the query [qryTransactionT op] was a big mistake. Actually, as the abbreviated query names (ALIASes) are related to the full names it would also require changing of these to maintain internal consistency, but your post was certainly technically correct. Here is how I would have written it (for full consistency) :

SELECT   qTH1.*
FROM     [qryTransactionHebrew] AS [qTH1]
         INNER JOIN
         [qryTransactionHebrew] AS [qTH2]
  ON     (qTH1.ClientID = qTH2.ClientID)
 AND     (qTH1.MonthOrder >= qTH2.MonthOrder)
GROUP BY qTH1.*
HAVING   Count(*) < 4
ORDER BY qTH1.ClientID ASC
       , qTH1.MonthOrder DESC

PS. I also changed the GROUP BY clause in a way I'm not absolutely sure of, but which I find neater if it does work. If not then simply revert to listing the fields as before.
PPS. This uses a technique that I learnt on here from young Mr Rabbit originally, so I'm pleased he's also involved in the thread. Just as I'm pleased he's accepted the mantle of moderator again. Good to have you back :-)