By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,153 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Last 3 records by group

P: 46
How can I retrieve the last 3 records from the following table. The table has the following fields and sample data:

ClientID TransactionID TransactionDate

1 6511216 01/02/2012
1 5332573 18/05/2011
1 9849528 11/02/2012
1 5374530 24/08/2010
1 5711675 26/04/2009
1 4001184 01/09/2011
2 9087526 15/07/2011
2 6524824 06/08/2009
2 5376892 26/07/2011
2 5327891 28/10/2008
2 6423568 11/11/2011
2 5379827 16/06/2012

the results should be (order by TransactionDate):

ClientID TransactionID TransactionDate

1 4001184 01/09/2011
1 6511216 01/02/2012
1 9849528 11/02/2012
2 5376892 26/07/2011
2 6423568 11/11/2011
2 5379827 16/06/2012


Note: I can't use 'Select Top 3' a. because of the month format b. this must work for a table where TransactionDate is a different field with text data.
Feb 11 '12 #1
Share this Question
Share on Google+
69 Replies


yarbrough40
100+
P: 320
if you know which client IDs you want ahead of time. you could use:
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 3 * FROM MyTable
  2. WHERE ClientID = 1
  3. UNION ALL
  4. SELECT TOP 3 * FROM MyTable
  5. WHERE ClientID = 2
  6. ORDER BY ClientID,TransactionDate
  7.  
  8.  
Feb 11 '12 #2

P: 46
I want the query to show all ClientId's (there are some 150000).
Feb 11 '12 #3

ADezii
Expert 5K+
P: 8,616
Shouldn't the results for Client# 1 be:
Expand|Select|Wrap|Line Numbers
  1. ClientID TransactionID TransactionDate
  2.     1       4001184      18/05/2011
  3.     1       6511216      01/02/2012
  4.     1       9849528      11/02/2012
Feb 11 '12 #4

ADezii
Expert 5K+
P: 8,616
There is a relatively simple solution using VBA, but I'll see what the SQL Gang comes up with first.
Feb 11 '12 #5

P: 46
@ADezii
ADezii,

You are right, my mistake.
Feb 11 '12 #6

NeoPa
Expert Mod 15k+
P: 31,307
@Moishy101.
Unless and until you complete the question by explaining under which circumstances 'the last three' has meaning, we're left guessing what you mean. This is not a good way to proceed, so if you want meaningful help, then I suggest you clarify the question by explaining what you should have included in your original question.
Feb 12 '12 #7

ADezii
Expert 5K+
P: 8,616
@moishy101:
The key, as I see it, is NOT to perform a Secondary Sort on the [TransactionDate] itself since it must remain Text, but on the Expression
CDate([TransactionDate]). The Records that you want returned would be the 'BOTTOM' 3 for each ID ASC with CDate([TransactionDate]) ASC.
Feb 12 '12 #8

P: 46
@NeoPa:
I'm sorry but I don't understand what you meant when you wrote: "explaining under which circumstances 'the last three' has meaning"
the table contains thousands of records and I need a report to show the last 3 records for each id. What else can I write to clarify matters?
Feb 12 '12 #9

P: 46
@ADezii:
Your suggestion may work in the sample table but as I wrote "this must work for a table where TransactionDate is a different field with text data" I meant that TransactionDate is replaced by TransactionMonth who's values are Jan, Feb, Mar, Apr etc.
Feb 12 '12 #10

100+
P: 759
I am not sure I understand well your problem so forget me if this is not a help for you:
Create a query with all your fields and one more. Use the new one field to keep the numeric values for months. Something like this:
Expand|Select|Wrap|Line Numbers
  1. NewField:IIF(TD="Ian",1,IIF(TD="Feb",2,IIF(TD="Mar",3,IIF......
This way you can sort by months in this NewField and use what ADezii teach you.
Feb 12 '12 #11

ADezii
Expert 5K+
P: 8,616
@moishy101:
Kindly Post some sample Data reflecting exactly the possible Values for [TransactionDate] and [TransactionMonth].
Feb 12 '12 #12

NeoPa
Expert Mod 15k+
P: 31,307
Moishy101:
I'm sorry but I don't understand what you meant when you wrote: "explaining under which circumstances 'the last three' has meaning"
The last (or last three) has a meaning that depends on the ordering of the items. So, for instance, the following data would have different 'last' records depending on whether it was ordered by [Date Arrived], [Date Left], or even [Name] :
Expand|Select|Wrap|Line Numbers
  1. Name     [Date Arrived]  [Date Left]
  2. Angus      1 Jan 2012    1 May 2011   Last by [Date Left]
  3. Barnaby    1 Feb 2012    1 Apr 2011   Last by [Date Arrived]
  4. Charles    1 Oct 2011    1 Mar 2011
  5. David      1 Nov 2011    1 Feb 2011
  6. Edward     1 Dec 2011    1 Jan 2011   Last by [Name]
PS. There is also the possibility of considering the last records that were entered into the table, but that is not illustrated easily, and if I understand correctly how Access manages its data, nor is it reliably determined without creating a field in the data to monitor it.
Feb 12 '12 #13

NeoPa
Expert Mod 15k+
P: 31,307
Moishy101:
Your suggestion may work in the sample table but as I wrote "this must work for a table where TransactionDate is a different field with text data"
Your original question was minimal. Very little explanation of what you want, or even what data you're working with. The only really useful part, from an experts point of view, was the example data. Now you explain that the example data is misleading. This makes trying to answer your question much more difficult than it should be.

You're responding well and sensibly to questions so I guess this is simply a mistake that shows limited understanding for what makes a good question, and some difficulty communicating. I'm sure that will come in time, but you will have to learn from this for future questions.

Moishy101:
I meant that TransactionDate is replaced by TransactionMonth who's values are Jan, Feb, Mar, Apr etc.
If your [TransactionDate] field is of type Date, then, and I'm guessing here as you haven't had a chance to reply yet to my question now (hopefully) you understand what I meant, if the sort order you are using is the transaction date, why are you replacing this with a string value reflecting only the month part? It seems a somewhat bizarre choice from the perspective of someone that only knows the little you've so far shared about the problem (IE. Me).

If your requirement is simply to select the three records with the maximum value of the [TransactionDate] field then you would surely be looking at using the TOP 3 predicate and sorting by [TransactionDate] DESC.
Feb 12 '12 #14

ADezii
Expert 5K+
P: 8,616
@NeoPa:
What makes this confusing, at least to me, is that you actually need the TOP 3 Records by [ClientID] ASC, [TransactionDate] DESC, but only reversing the order of the 3.
Feb 12 '12 #15

P: 46
Thank you folks for your time and assistance, as NeoPa pointed out the question was unclear and for that I apologize, in any event I'll try to clarify the question.

Here's the situation:
I have a mdb that is used to keep track of monthly transactions. The months are Hebrew months (why they use Hebrew months, I haven't got a clue, but I can't question my superiors) the function used to convert from the Gregorian date returns a string containing the Hebrew month, which is essentially all we need since the transactions are monthly, and there is no more than one transaction per client per Hebrew month.

and so the question is:

How can I retrieve the last 3 transactions (based on the order of the Hebrew months) to take place for each ID.

The names of the Hebrew months are the following (and for my purpose in this order) Tishrei, Cheshvan, Kislev, Tevet, Shvat, Adar I, Adar II, Nissan, Iyar, Sivan, Tammuz, Av, Elul.

Sample data would be:
Expand|Select|Wrap|Line Numbers
  1. ClientID    TransactionNumber     TransactionHMonth
  2.  
  3.    1             6511216               Kislev
  4.    1             5332573               Tishrei
  5.    1             9849528               Sivan
  6.    1             5374530               Av
  7.    1             5711675               Nissan
  8.    1             4001184               Iyar
  9.    2             9087526               Iyar
  10.    2             6524824               Kislev
  11.    2             5376892               Nissan
  12.    2             5327891               Sivan
  13.    2             6423568               Av
  14.    2             5379827               Tishrei
  15.  
the results returned should be (and in this order):

Expand|Select|Wrap|Line Numbers
  1. ClientID    TransactionNumber     TransactionHMonth
  2.  
  3.    1             4001184                Iyar
  4.    1             9849528                Sivan
  5.    1             5374530                Av
  6.    2             9087526                Iyar
  7.    2             5327891                Sivan
  8.    2             6423568                Av
  9.  
I was hoping to avoid the need to get into the real details since the Hebrew months are not what we use, but if this will help clarify my question it was worth it.
Feb 12 '12 #16

ADezii
Expert 5K+
P: 8,616
Given the Hebrew Month Abbreviations for the given Year, how do we know the actual 'Day' of a Transaction?
Feb 12 '12 #17

P: 46
ADezii:

The actual day does not matter, all that is being tracked is the Hebrew month of the transaction. The year is determined by the Gregorian year (another field in the table).
Feb 12 '12 #18

NeoPa
Expert Mod 15k+
P: 31,307
ADezii:
What makes this confusing, at least to me, is that you actually need the TOP 3 Records by [ClientID] ASC, [TransactionDate] DESC, but only reversing the order of the 3.
I wasn't even aware of that extra stipulation my friend, but it seems you have interpreted the question correctly as has been confirmed by the very helpful post #16 from the OP. That's clear now though.

@Moishy101.
It's important to understand that :
  1. Accessing the TOP 3 records PER GROUP, is a vastly different proposition from accessing the TOP 3 overall. The latter is handled easily, but the former requires a separate subquery be run for each client. The SQL code is doable - if quite fiddly, but the performance immediately shoots through the floor (I would expect delays to be appreciable for the numbers mentioned).
  2. Sorting by date or by month using standard names or abbreviations is handled automatically. By which I mean there are facilities available one can make use of to do this easily. Sorting by Hebrew months is, as far as Access is concerned, sorting by random and meaningless strings. To use this one would require the data to be defined for use. Probably in a separate table that would need to be linked into the query.
  3. While months within a single year fall in a particular order, those same months across multiple years can fall in any order. I'm not really sure exactly why you want this as specified, but it still makes little sense to me.

Post #16 does clarify your situation greatly though, and does leave an answerable question, even if some may wonder if that question is adequately considered.
Feb 12 '12 #19

P: 46
NeoPa:

You supplied me with much food for thought... I'll sleep over it and maybe in the morning I'll get some new ideas or inspirations.

You wrote: "Sorting by Hebrew months is, as far as Access is concerned, sorting by random and meaningless strings. To use this one would require the data to be defined for use. Probably in a separate table that would need to be linked into the query."

Wouldn't something like Mihail's suggestion (in post #11) work?
Feb 12 '12 #20

NeoPa
Expert Mod 15k+
P: 31,307
It would indeed. I would consider that as fitting within "the data to be defined for use". However, as a solution for this problem, it's not a very strong one, as it involves the data for each record being processed through a function. Fine for small numbers of records, but you say the numbers are large in this case, so I would advise use of a separate table to manage the ordering for you.
Feb 12 '12 #21

Rabbit
Expert Mod 10K+
P: 12,341
Try something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT T1.groupField, T1.orderField, T1.otherField
  2. FROM someTable T1,
  3.    someTable T2
  4. ON T1.groupField = T2.groupField
  5.    AND T1.orderField < T2.orderField
  6. GROUP BY T1.groupField, T1.orderField, T1.otherField
  7. HAVING COUNT(*) < 3
Feb 13 '12 #22

NeoPa
Expert Mod 15k+
P: 31,307
Assuming we start with your table (for convenience we'll refer to it as [tblTransaction] unless/until we hear otherwise) and a new table called [tblHebrewMonthOrder] with the following setup and data :
Table Name = [tblHebrewMonthOrder]
Expand|Select|Wrap|Line Numbers
  1. HebrewMonth  MonthOrder
  2. Tishrei              1
  3. Cheshvan             2
  4. Kislev               3
  5. Tevet                4
  6. Shvat                5
  7. Adar I               6
  8. Adar II              7
  9. Nissan               8
  10. Iyar                 9
  11. Sivan               10
  12. Tammuz              11
  13. Av                  12
  14. Elul                13
Next we have a query called [qryTranData] which is simply a query that links the data in [tblTransaction] with that in [tblHebrewMonthOrder] and shows the order required :
Query Name = [qryTransactionHebrew]
Expand|Select|Wrap|Line Numbers
  1. SELECT   tT.ClientID
  2.        , tT.TransactionNumber
  3.        , tHMO.MonthOrder
  4. FROM     [tblTransaction] AS [tT]
  5.          INNER JOIN
  6.          [tblHebrewMonthOrder] AS [tHMO]
  7.   ON     tT.TransactionHMonth = tHMO.HebrewMonth
  8. ORDER BY tT.ClientID ASC
  9.        , tHMO.MonthOrder DESC
We now have a dataset which contains the requisite information as well as being sorted in the correct order (which will be ignored when we use it again in [qryTransactionTop], but may be helpful to you for viewing the data and understanding what is what).

Query Name = [qryTransactionTop]
Expand|Select|Wrap|Line Numbers
  1. SELECT   qTT1.*
  2. FROM     [qryTransactionTop] AS [qTT1]
  3.          INNER JOIN
  4.          [qryTransactionTop] AS [qTT2]
  5.   ON     (qTT1.ClientID = qTT2.ClientID)
  6.  AND     (qTT1.MonthOrder >= qTT2.MonthOrder)
  7. GROUP BY qTT1.ClientID
  8.        , qTT1.TransactionNumber
  9.        , qTT1.MonthOrder
  10. HAVING   Count(qTT1.*) < 4
  11. ORDER BY qTT1.ClientID ASC
  12.        , qTT1.MonthOrder DESC
The idea at this point is to link the query to itself on [ClientID] and [MonthOrder] and treat each record of the first query as a GROUP. With the AND line (#6) limiting which records of the second query are included, the HAVING clause can pick out only those GROUPs where the number of records is up to three.
Feb 13 '12 #23

P: 46
NeoPa:

I tried what you suggested and I got some errors.
In qryTransactionTop there is a syntax error on Count(qTT1.*) < 4
In qryTransactionHebrew I get a Enter Parameter Value on tHMO.Order
Feb 13 '12 #24

NeoPa
Expert Mod 15k+
P: 31,307
Have you set up the table [tblHebrewMonthOrder] as illustrated on your system?
Feb 13 '12 #25

P: 46
Yes I have.
Thanks for your help.
Feb 13 '12 #26

NeoPa
Expert Mod 15k+
P: 31,307
Found it. My mistake. I changed the design half way through and missed updating one of the references. I've updated it no so you can try it again if you re-copy/paste the contents of [tblHebrewMonthOrder].

PS. Let me know if replacing qTT1.* with simply * in line #10 works for [qryTransactionTop] :
Expand|Select|Wrap|Line Numbers
  1. HAVING   Count(*) < 4
Feb 13 '12 #27

P: 46
Thank you, as you know you've successfully changed the qryTransactionHebrew, but I still get the syntax error on Count(qTT1.*) < 4 even after changing it to Count(*) < 4
Feb 13 '12 #28

Rabbit
Expert Mod 10K+
P: 12,341
Where is the tHMO.Order that you mentioned in your error? I don't see it in NeoPa's code so it shouldn't be in your code either.
Feb 13 '12 #29

P: 46
Rabbit:

NeoPa corrected the query as you can see from the last edited on post #23.
Feb 13 '12 #30

Rabbit
Expert Mod 10K+
P: 12,341
Then the syntax error you're getting shouldn't be the same. What's the new syntax error?
Feb 13 '12 #31

P: 46
The syntax error is on Count(qTT1.*) < 4
Feb 13 '12 #32

NeoPa
Expert Mod 15k+
P: 31,307
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 ;-)
Feb 13 '12 #33

NeoPa
Expert Mod 15k+
P: 31,307
@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.
Feb 13 '12 #34

P: 46
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!)
Feb 13 '12 #35

ADezii
Expert 5K+
P: 8,616
@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.
Feb 13 '12 #36

P: 46
@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.
Feb 13 '12 #37

ADezii
Expert 5K+
P: 8,616
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_Client:) 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 tblHebrewMonthOrder, 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:
    Expand|Select|Wrap|Line Numbers
    1. SELECT DISTINCT tblTransaction.ClientID, fCalcTOP3([ClientID]) AS TOP_3_Per_Client
    2. FROM tblTransaction
    3. GROUP BY tblTransaction.ClientID
    4. ORDER BY tblTransaction.ClientID;
    5.  
  2. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcTOP3(bytClientID As Byte)
    2. Dim MyDB As DAO.Database
    3. Dim rst As DAO.Recordset
    4. Dim strSQL As String
    5. Dim strBuild As String
    6.  
    7. strBuild = ""       'INITIALIZE
    8.  
    9. 'Are there at least 3 Records for the Passed ClientID
    10. If DCount("*", "tblTransaction", "[ClientID] = " & bytClientID) < 3 Then
    11.   fCalcTOP3 = "N/A"
    12.     Exit Function
    13. End If
    14.  
    15. strSQL = "SELECT tblTransaction.ClientID, tblTransaction.TransactionID, tblHebrewMonthOrder.MonthName, " & _
    16.          "tblHebrewMonthOrder.Priority FROM tblHebrewMonthOrder INNER JOIN tblTransaction ON " & _
    17.          "tblHebrewMonthOrder.MonthName = tblTransaction.TransactionMonth WHERE tblTransaction.ClientID = " & _
    18.           bytClientID & " ORDER BY tblTransaction.ClientID,tblHebrewMonthOrder.Priority;"
    19.  
    20. Set MyDB = CurrentDb
    21. Set rst = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
    22.  
    23. rst.MoveLast: rst.MoveFirst
    24.  
    25. 'The TOP 3 for this ClientID will actually be the Last 3 Records, Move to 3rd from Bottom
    26. rst.Move rst.RecordCount - 3        'TOP of the TOP 3
    27.  
    28. Do While Not rst.EOF
    29.   strBuild = strBuild & rst!TransactionID & "," & rst!MonthName & " | "
    30.     rst.MoveNext
    31. Loop
    32.  
    33. fCalcTOP3 = Left$(strBuild, Len(strBuild) - 3)
    34.  
    35. rst.Close
    36. Set rst = Nothing
    37. End Function
  3. Query OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. ClientID    TOP_3_Per_Client
    2. 1           9870561,Adar I | 5374530,Nissan | 2323456,Tammuz
    3. 2           5376892,Nissan | 5327891,Sivan | 6423568,Tammuz
    4. 3           N/A
    5. 4           N/A
    6. 5           991254,Cheshvan | 345678,Sivan | 987654,Av
    7.  
OOPs!: Code Line# 23 can be removed from the Attachment, since it was there for testing purposes only.
Attached Files
File Type: zip Hebrew Months.zip (24.8 KB, 73 views)
Feb 13 '12 #38

P: 46
@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?
Feb 13 '12 #39

ADezii
Expert 5K+
P: 8,616
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.
Feb 13 '12 #40

Rabbit
Expert Mod 10K+
P: 12,341
Try modifying Neo's final query to this.
Expand|Select|Wrap|Line Numbers
  1. SELECT   qTT1.ClientID 
  2.        , qTT1.TransactionNumber 
  3.        , qTT1.MonthOrder 
  4. FROM     [qryTransactionHebrew] AS [qTT1] 
  5.          INNER JOIN 
  6.          [qryTransactionHebrew] AS [qTT2] 
  7.   ON     (qTT1.ClientID = qTT2.ClientID) 
  8.  AND     (qTT1.MonthOrder >= qTT2.MonthOrder) 
  9. GROUP BY qTT1.ClientID 
  10.        , qTT1.TransactionNumber 
  11.        , qTT1.MonthOrder 
  12. HAVING   Count(*) < 4 
  13. ORDER BY qTT1.ClientID ASC 
  14.        , qTT1.MonthOrder DESC
Feb 13 '12 #41

ADezii
Expert 5K+
P: 8,616
Revised Code to allow for 0, >0 and <3, and >=3 Records per ClientID:
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalcTOP3(bytClientID As Byte)
  2. Dim MyDB As DAO.Database
  3. Dim rst As DAO.Recordset
  4. Dim strSQL As String
  5. Dim strBuild As String
  6. Dim lngNumOfRecs As Long
  7.  
  8. strBuild = ""       'INITIALIZE
  9.  
  10. lngNumOfRecs = DCount("*", "tblTransaction", "[ClientID] = " & bytClientID)
  11.  
  12. strSQL = "SELECT tblTransaction.ClientID, tblTransaction.TransactionID, tblHebrewMonthOrder.MonthName, " & _
  13.          "tblHebrewMonthOrder.Priority FROM tblHebrewMonthOrder INNER JOIN tblTransaction ON " & _
  14.          "tblHebrewMonthOrder.MonthName = tblTransaction.TransactionMonth WHERE tblTransaction.ClientID = " & _
  15.           bytClientID & " ORDER BY tblTransaction.ClientID,tblHebrewMonthOrder.Priority;"
  16.  
  17. Set MyDB = CurrentDb
  18. Set rst = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
  19.  
  20. Select Case lngNumOfRecs
  21.   Case 0            'Should never happen
  22.     fCalcTOP3 = ""
  23.   Case Is < 3
  24.     With rst
  25.       Do While Not rst.EOF
  26.         strBuild = strBuild & rst!TransactionID & "," & rst!MonthName & " | "
  27.           rst.MoveNext
  28.       Loop
  29.     End With
  30.   Case Else
  31.     'The TOP 3 for this ClientID will actually be the Last 3 Records
  32.     rst.Move rst.RecordCount - 3        'TOP of the TOP 3
  33.  
  34.     Do While Not rst.EOF
  35.       strBuild = strBuild & rst!TransactionID & "," & rst!MonthName & " | "
  36.         rst.MoveNext
  37.     Loop
  38. End Select
  39.  
  40. fCalcTOP3 = Left$(strBuild, Len(strBuild) - 3)
  41.  
  42. rst.Close
  43. Set rst = Nothing
  44. End Function
Feb 13 '12 #42

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

Expand|Select|Wrap|Line Numbers
  1. ClientID    TOP_3_Per_Client
  2. 1           9870561,Adar I | 5374530,Nissan | 2323456,Tammuz
  3. 2           5376892,Nissan | 5327891,Sivan | 6423568,Tammuz
  4. 3           1123456,Tishrei | 8754310,Adar II | 
  5. 4           9875415,Shvat |  | 
  6. 5           991254,Cheshvan | 345678,Sivan | 987654,Av
  7.  
Feb 13 '12 #43

ADezii
Expert 5K+
P: 8,616
You are really making me work for this one! (LOL)
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalcTOP3(bytClientID As Byte)
  2. Dim MyDB As DAO.Database
  3. Dim rst As DAO.Recordset
  4. Dim strSQL As String
  5. Dim strBuild As String
  6. Dim lngNumOfRecs As Long
  7.  
  8. strBuild = ""       'INITIALIZE
  9.  
  10. lngNumOfRecs = DCount("*", "tblTransaction", "[ClientID] = " & bytClientID)
  11.  
  12. strSQL = "SELECT tblTransaction.ClientID, tblTransaction.TransactionID, tblHebrewMonthOrder.MonthName, " & _
  13.          "tblHebrewMonthOrder.Priority FROM tblHebrewMonthOrder INNER JOIN tblTransaction ON " & _
  14.          "tblHebrewMonthOrder.MonthName = tblTransaction.TransactionMonth WHERE tblTransaction.ClientID = " & _
  15.           bytClientID & " ORDER BY tblTransaction.ClientID,tblHebrewMonthOrder.Priority;"
  16.  
  17. Set MyDB = CurrentDb
  18. Set rst = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
  19.  
  20. Select Case lngNumOfRecs
  21.   Case 0            'Should never happen
  22.     fCalcTOP3 = ""
  23.   Case Is < 3
  24.     With rst
  25.       Do While Not .EOF
  26.         If lngNumOfRecs = 1 Then
  27.           strBuild = !TransactionID & "," & !MonthName & " |   |"
  28.         Else
  29.           strBuild = strBuild & !TransactionID & ", " & !MonthName & " |"
  30.         End If
  31.           .MoveNext
  32.       Loop
  33.     End With
  34.   Case Else
  35.     With rst
  36.       'The TOP 3 for this ClientID will actually be the Last 3 Records
  37.       rst.Move rst.RecordCount - 3        'TOP of the TOP 3
  38.  
  39.       Do While Not .EOF
  40.         strBuild = strBuild & !TransactionID & "," & !MonthName & " | "
  41.           .MoveNext
  42.       Loop
  43.       strBuild = Left$(strBuild, Len(strBuild) - 3)
  44.     End With
  45. End Select
  46.  
  47. fCalcTOP3 = strBuild
  48.  
  49. rst.Close
  50. Set rst = Nothing
  51. 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.
Feb 13 '12 #44

NeoPa
Expert Mod 15k+
P: 31,307
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 [qryTransactionTop] (twice) as the input for the query [qryTransactionTop] 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) :
Expand|Select|Wrap|Line Numbers
  1. SELECT   qTH1.*
  2. FROM     [qryTransactionHebrew] AS [qTH1]
  3.          INNER JOIN
  4.          [qryTransactionHebrew] AS [qTH2]
  5.   ON     (qTH1.ClientID = qTH2.ClientID)
  6.  AND     (qTH1.MonthOrder >= qTH2.MonthOrder)
  7. GROUP BY qTH1.*
  8. HAVING   Count(*) < 4
  9. ORDER BY qTH1.ClientID ASC
  10.        , 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 :-)
Feb 13 '12 #45

P: 46
Rabbit:

I just noticed your post now, it works great! Thank you. :)

NeoPa:

I tried copying your new query but I get an error when I try to open it: Invalid use of '.', '!' or '()' in query expression 'qTH1.'.

the exact query is (as posted):
Expand|Select|Wrap|Line Numbers
  1. SELECT qTH1.*
  2. FROM qryTransactionHebrew AS qTH1 INNER JOIN qryTransactionHebrew AS qTH2 ON (qTH1.ClientID=qTH2.ClientID) AND (qTH1.MonthOrder>=qTH2.MonthOrder)
  3. GROUP BY qTH1.*
  4. HAVING Count(*)<4
  5. ORDER BY qTH1.ClientID, qTH1.MonthOrder DESC;
  6.  
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.
Does that mean that it is accounted for in your solution? If not can be incorporated?

ADezii:

Thank you very much for all your efforts in helping me. :)

I've tested the performance on 100 rows and then multiplied the result by 1500 (total of 150000). Here are my findings:

based on QueryPerformanceCounter takes approx.
433.752523784223 seconds
or
7.22920872973705 minutes

and based on winmm.dll it takes approx.
436.5 seconds
or
7.275 minutes
Feb 14 '12 #46

NeoPa
Expert Mod 15k+
P: 31,307
Moishy101:
I tried copying your new query but I get an error when I try to open it: Invalid use of '.', '!' or '()' in query expression 'qTH1.'.
So, did you read the PS, as it seems to me that was a possibility I had anticipated, so I included how to fix it there. I didn't feel it would be necessary to include both versions in the post as the change to the other version was so trivial and straightforward, but if you need it :
Expand|Select|Wrap|Line Numbers
  1. SELECT   qTH1.*
  2. FROM     [qryTransactionHebrew] AS [qTH1]
  3.          INNER JOIN
  4.          [qryTransactionHebrew] AS [qTH2]
  5.   ON     (qTH1.ClientID = qTH2.ClientID)
  6.  AND     (qTH1.MonthOrder >= qTH2.MonthOrder)
  7. GROUP BY qTH1.ClientID
  8.        , qTH1.TransactionNumber
  9.        , qTH1.MonthOrder
  10. HAVING   Count(*) < 4
  11. ORDER BY qTH1.ClientID ASC
  12.        , qTH1.MonthOrder DESC
Feb 14 '12 #47

Rabbit
Expert Mod 10K+
P: 12,341
Could you run the same statistics using the pure SQL? I would be interested in knowing which one is faster.
Feb 14 '12 #48

P: 46
NeoPa:

Then I get a Cannot group on fields selected with '*' (qTH1). error.

Rabbit:

Using pure SQL based on QueryPerformanceCounter takes approx.
396.941530410723 seconds
or
6.61569217351205 minutes

and based on winmm.dll it takes approx.
415.5 seconds
or
6.925 minutes
Feb 14 '12 #49

ADezii
Expert 5K+
P: 8,616
@Rabbit:
I would have thought that the SQL approach would have been 'significantly' faster, what is your opinion?
Feb 14 '12 #50

69 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.