473,890 Members | 2,038 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 8588
Mihail
759 Contributor
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
8,834 Recognized Expert Expert
@moishy101:
Kindly Post some sample Data reflecting exactly the possible Values for [TransactionDate] and [TransactionMont h].
Feb 12 '12 #12
NeoPa
32,584 Recognized Expert Moderator MVP
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
32,584 Recognized Expert Moderator MVP
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 TransactionMont h 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
8,834 Recognized Expert Expert
@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
moishy101
46 New Member
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
8,834 Recognized Expert Expert
Given the Hebrew Month Abbreviations for the given Year, how do we know the actual 'Day' of a Transaction?
Feb 12 '12 #17
moishy101
46 New Member
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
32,584 Recognized Expert Moderator MVP
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
moishy101
46 New Member
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

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

Similar topics

1
1574
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
3751
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
1441
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
15290
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
5152
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
1476
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
1490
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
4900
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
1947
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
11207
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...
0
10794
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9612
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7153
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
5830
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
6031
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4652
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4251
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3259
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.