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.
69 8462
if you know which client IDs you want ahead of time. you could use: -
SELECT TOP 3 * FROM MyTable
-
WHERE ClientID = 1
-
UNION ALL
-
SELECT TOP 3 * FROM MyTable
-
WHERE ClientID = 2
-
ORDER BY ClientID,TransactionDate
-
-
I want the query to show all ClientId's (there are some 150000).
Shouldn't the results for Client# 1 be: -
ClientID TransactionID TransactionDate
-
1 4001184 18/05/2011
-
1 6511216 01/02/2012
-
1 9849528 11/02/2012
There is a relatively simple solution using VBA, but I'll see what the SQL Gang comes up with first.
@ADezii
ADezii,
You are right, my mistake.
NeoPa 32,556
Expert Mod 16PB
@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.
@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.
@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?
@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.
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: - 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.
@moishy101:
Kindly Post some sample Data reflecting exactly the possible Values for [TransactionDate] and [TransactionMonth].
NeoPa 32,556
Expert Mod 16PB 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] : - Name [Date Arrived] [Date Left]
-
Angus 1 Jan 2012 1 May 2011 Last by [Date Left]
-
Barnaby 1 Feb 2012 1 Apr 2011 Last by [Date Arrived]
-
Charles 1 Oct 2011 1 Mar 2011
-
David 1 Nov 2011 1 Feb 2011
-
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.
NeoPa 32,556
Expert Mod 16PB 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.
@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.
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: -
ClientID TransactionNumber TransactionHMonth
-
-
1 6511216 Kislev
-
1 5332573 Tishrei
-
1 9849528 Sivan
-
1 5374530 Av
-
1 5711675 Nissan
-
1 4001184 Iyar
-
2 9087526 Iyar
-
2 6524824 Kislev
-
2 5376892 Nissan
-
2 5327891 Sivan
-
2 6423568 Av
-
2 5379827 Tishrei
-
the results returned should be (and in this order): -
ClientID TransactionNumber TransactionHMonth
-
-
1 4001184 Iyar
-
1 9849528 Sivan
-
1 5374530 Av
-
2 9087526 Iyar
-
2 5327891 Sivan
-
2 6423568 Av
-
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.
Given the Hebrew Month Abbreviations for the given Year, how do we know the actual 'Day' of a Transaction?
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).
NeoPa 32,556
Expert Mod 16PB 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 : - 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).
- 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.
- 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.
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?
NeoPa 32,556
Expert Mod 16PB
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.
Try something like this: - SELECT T1.groupField, T1.orderField, T1.otherField
-
FROM someTable T1,
-
someTable T2
-
ON T1.groupField = T2.groupField
-
AND T1.orderField < T2.orderField
-
GROUP BY T1.groupField, T1.orderField, T1.otherField
-
HAVING COUNT(*) < 3
NeoPa 32,556
Expert Mod 16PB
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] - HebrewMonth MonthOrder
-
Tishrei 1
-
Cheshvan 2
-
Kislev 3
-
Tevet 4
-
Shvat 5
-
Adar I 6
-
Adar II 7
-
Nissan 8
-
Iyar 9
-
Sivan 10
-
Tammuz 11
-
Av 12
-
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] - SELECT tT.ClientID
-
, tT.TransactionNumber
-
, tHMO.MonthOrder
-
FROM [tblTransaction] AS [tT]
-
INNER JOIN
-
[tblHebrewMonthOrder] AS [tHMO]
-
ON tT.TransactionHMonth = tHMO.HebrewMonth
-
ORDER BY tT.ClientID ASC
-
, 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] - SELECT qTT1.*
-
FROM [qryTransactionTop] AS [qTT1]
-
INNER JOIN
-
[qryTransactionTop] AS [qTT2]
-
ON (qTT1.ClientID = qTT2.ClientID)
-
AND (qTT1.MonthOrder >= qTT2.MonthOrder)
-
GROUP BY qTT1.ClientID
-
, qTT1.TransactionNumber
-
, qTT1.MonthOrder
-
HAVING Count(qTT1.*) < 4
-
ORDER BY qTT1.ClientID ASC
-
, 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.
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
NeoPa 32,556
Expert Mod 16PB
Have you set up the table [tblHebrewMonthOrder] as illustrated on your system?
Yes I have.
Thanks for your help.
NeoPa 32,556
Expert Mod 16PB
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] :
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
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.
Rabbit:
NeoPa corrected the query as you can see from the last edited on post #23.
Then the syntax error you're getting shouldn't be the same. What's the new syntax error?
The syntax error is on Count(qTT1.*) < 4
NeoPa 32,556
Expert Mod 16PB 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 32,556
Expert Mod 16PB
@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.
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!)
@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.
@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.
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: - 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.
- Processing Time may be significant for a large Data set, which you have indicated is the case.
Advantage: - OUTPUT can easily be converted to a number of Formats, namely: a Results Table, Delimited String in a Query, Debug Window, Text File, etc.
- SQL:
-
SELECT DISTINCT tblTransaction.ClientID, fCalcTOP3([ClientID]) AS TOP_3_Per_Client
-
FROM tblTransaction
-
GROUP BY tblTransaction.ClientID
-
ORDER BY tblTransaction.ClientID;
-
- 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
- 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.
@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?
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.
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
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
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
-
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 32,556
Expert Mod 16PB 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) : - 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 :-)
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): -
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, qTH1.MonthOrder DESC;
-
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
NeoPa 32,556
Expert Mod 16PB 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 : - SELECT qTH1.*
-
FROM [qryTransactionHebrew] AS [qTH1]
-
INNER JOIN
-
[qryTransactionHebrew] AS [qTH2]
-
ON (qTH1.ClientID = qTH2.ClientID)
-
AND (qTH1.MonthOrder >= qTH2.MonthOrder)
-
GROUP BY qTH1.ClientID
-
, qTH1.TransactionNumber
-
, qTH1.MonthOrder
-
HAVING Count(*) < 4
-
ORDER BY qTH1.ClientID ASC
-
, qTH1.MonthOrder DESC
Could you run the same statistics using the pure SQL? I would be interested in knowing which one is faster.
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
@Rabbit:
I would have thought that the SQL approach would have been 'significantly' faster, what is your opinion?
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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
)
|
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
|
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...
|
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...
...
|
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...
|
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...
|
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 =...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |