Last 3 records by group

[moishy101]

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.

[ADezii]

Given the Hebrew Month Abbreviations for the given Year, how do we know the actual 'Day' of a Transaction?

[moishy101]

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]

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.

[moishy101]

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]

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.

[Rabbit]

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]

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 [tblHebrewMonthO rder] with the following setup and data :
Table Name = [tblHebrewMonthO rder]

[B][U]HebrewMonth[/U]  [U]MonthOrder[/U][/B]
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 [tblHebrewMonthO rder] and shows the order required :
Query Name = [qryTransactionH ebrew]

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 [qryTransactionT op], but may be helpful to you for viewing the data and understanding what is what).

Query Name = [qryTransactionT op]

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.

[moishy101]

NeoPa:

I tried what you suggested and I got some errors.
In qryTransactionT op there is a syntax error on Count(qTT1.*) < 4
In qryTransactionH ebrew I get a Enter Parameter Value on tHMO.Order

[NeoPa]

Have you set up the table [tblHebrewMonthO rder] as illustrated on your system?

[moishy101]

Yes I have.
Thanks for your help.

[NeoPa]

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 [tblHebrewMonthO rder].

PS. Let me know if replacing qTT1.* with simply * in line #10 works for [qryTransactionT op] :

HAVING   Count(*) < 4

[moishy101]

Thank you, as you know you've successfully changed the qryTransactionH ebrew, but I still get the syntax error on Count(qTT1.*) < 4 even after changing it to Count(*) < 4

[Rabbit]

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.

[moishy101]

Rabbit:

NeoPa corrected the query as you can see from the last edited on post #23.