473,836 Members | 2,130 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Last 3 records by group

46 New Member
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
69 8570
Rabbit
12,516 Recognized Expert Moderator MVP
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
8,834 Recognized Expert Expert
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
moishy101
46 New Member
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
8,834 Recognized Expert Expert
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
32,584 Recognized Expert Moderator MVP
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) :
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
moishy101
46 New Member
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 QueryPerformanc eCounter takes approx.
433.75252378422 3 seconds
or
7.2292087297370 5 minutes

and based on winmm.dll it takes approx.
436.5 seconds
or
7.275 minutes
Feb 14 '12 #46
NeoPa
32,584 Recognized Expert Moderator MVP
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
12,516 Recognized Expert Moderator MVP
Could you run the same statistics using the pure SQL? I would be interested in knowing which one is faster.
Feb 14 '12 #48
moishy101
46 New Member
NeoPa:

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

Rabbit:

Using pure SQL based on QueryPerformanc eCounter takes approx.
396.94153041072 3 seconds
or
6.6156921735120 5 minutes

and based on winmm.dll it takes approx.
415.5 seconds
or
6.925 minutes
Feb 14 '12 #49
ADezii
8,834 Recognized Expert Expert
@Rabbit:
I would have thought that the SQL approach would have been 'significantly' faster, what is your opinion?
Feb 14 '12 #50

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

Similar topics

1
1572
by: Matt | last post by:
I have a project where I need to have navigation buttons. Now I got them working but I found a problem that occurs when there are records deleted from the DB. The DB I am accessing has an ID column that is the primary key and uses AutoNumber. My program also allows the user to delete records. My problem occurs when someone deletes a record other than the first or last record in the DB. The way my Next navigation button works is that it...
10
3742
by: Lyn | last post by:
I have a form set to Single Form mode with which I can cycle through the records in a table via Next and Previous buttons. To avoid users pressing the Previous button on the first record and the Next button on the last record, I would like to disable one or both buttons when the first and/or last record is displayed. I am not sure how to do this when the RecordSource is simply the table. I know that if the record source were a...
5
1440
by: booksnore | last post by:
I am reading some very large files greater than 10 GB. Some of the files (not all) contain a header and footer record identified by "***" in the first three characters of the record. I need to delete the header or footer record before reading the file into a database. Whats the best way to do this in C#? Any help appreciated. Joe *** Sent via Developersdex http://www.developersdex.com ***
2
15288
by: schapopa | last post by:
Hi, I want to create query where I could group records by quarters, and get the last record in each group. e.g Create Table MyTable ( Value , date )
4
5150
by: Tomas | last post by:
I'm creating MS Access database and I need to have in query an average of 3 last records. How to do it? Maybe here is some function or sql expresion? Thanks
2
1475
by: perryche | last post by:
I don't know where to begin search for this... I want to add a record, but still able to see the page, without bringing it to a new page and have to scroll back to see other records. I hope I explain myself here. Or, is there a way to add record being on the top of other records and not go to the bottom? Thanks. Perry
1
2265
by: jith87 | last post by:
hi, i am importing a text file into oracle database using sql loader.i need to ommit the first and last records of the text file while importing... can anybody help???? this is my text file... A: Removable 0.0% C: Fixed NTFS 39.06 GB 26.10 GB 66.8% D: Fixed NTFS 43.95 GB 30.44 GB 69.3% E:...
4
1488
by: PeteM | last post by:
Using SQL Server 2005 I have on a number of occasions, succesfully used the following code to create tables with the last dated results or observations for each Patient. However I am now being asked to do the same in Access 2003 and all the variations I can think of return the error message “At most one record can be returned by this subquery”. Do I have to give up with Access? SELECT Patient, (SELECT TOP 1 t1.LabResult1 FROM ...
4
4897
by: Greg (codepug | last post by:
I want to display the last 22 records in my continuous form. I have writen the following code and it works, but was wondering if there were any better suggestions for accomplishing this. My code scrolls the screen as it goes to bottom and than scrolls out the 22 recs. My code: Private Sub GoToBottom()
5
1942
by: asdasd10 | last post by:
i tired do it alone but its gives me an eror like that: "r object cant move back" here is my code : <% path = Server.mappath("../db/ServerData.mdb") set con = Server.createobject("ADODB.Connection") con.open "driver={Microsoft Access Driver (*.mdb)};DBQ="&path set r = con.execute("select * from News") Dim i
0
9811
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10822
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10577
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10241
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7774
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6975
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5642
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5812
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4003
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.