Last 3 records by group

[moishy101]

Is it was possible to have a separate auto-numbered field for each ClientID, if so would grabbing the last three records for each ClientID (based upon the auto-numbered field) be any more effective?

If the answer the above in yes, how can I implement it?

[Rabbit]

In response to post 60, you don't have to change your table setup, you just have to use different sql, mine was just a syntactically correct example of how to use the code.

In response to post 61, that's what my code does.

[NeoPa]

Moishy101:
but both of those issues can be solved quite simply albeit not in a smart or professional manner, in tblHebrewMonthO rder I can add the values for the coming years (expected lifetime of the mdb).

That makes sense actually. Sometimes you just have to have a kludgy solution when the data you're dealing with doesn't fit anywhere neatly. Clearly the [MonthOrder] values will go beyond thirteen in that case ;-)

For Rabbit's SQL you need to treat the references as :

1. [someTable] == [qryTransactionH ebrew]
2. [partitionField] == [ClientID]
3. [orderField] == [MonthOrder]
4. [otherField] == [TransactionNumb er]

Thus, the SQL would look something like :

SELECT   [ClientID]
       , [TransactionNumber]
       , [MonthOrder]
FROM     [tblTransactionHebrew]
WHERE    (RowNumber([ClientID]) < 4)
ORDER BY [ClientID] ASC
       , [MonthOrder] DESC

[NeoPa]

I'd be surprised if that works exactly as intended. Hear me out on this, because I may be off-base and need correcting.

The WHERE clause is applied to the incoming data. As such, the ORDER BY clause has not had a chance to effect the order. I would expect this to reflect the original order, rather than that applied depending on the Hebrew month (which, I expect, would explain why Rabbit's test threw up no flags).

Is it possible to use a HAVING clause in a query which has no GROUP BY clause and no aggregation functions?
In case it is, try the following :

SELECT   [ClientID]
       , [TransactionNumber]
       , [MonthOrder]
FROM     [tblTransactionHebrew]
HAVING   (RowNumber([ClientID]) < 4)
ORDER BY [ClientID] ASC
       , [MonthOrder] DESC

I'm happy to be enlightened either way :-)

[Rabbit]

Yes, I was worried about that being the case. But after creating the code and testing, it actually does take the order by into account before running the code. Otherwise, I was prepared to use a subquery to do the sorting before filtering.

[moishy101]

NeoPa:

For Rabbit's SQL you need to treat the references as :

1. [someTable] == [qryTransactionH ebrew]
2. [partitionField] == [ClientID]
3. [orderField] == [MonthOrder]
4. [otherField] == [TransactionNumb er]

[MonthOrder] is in a separate table so the sql will have to be adapted a little bit.

Unfortunately it is not possible to use a HAVING clause in a query which has no GROUP BY clause and no aggregation functions, and so I get a "HAVING clause (RowNumber([ClientID])<4) without grouping or aggregation." error.

The WHERE clause is applied to the incoming data. As such, the ORDER BY clause has not had a chance to effect the order. I would expect this to reflect the original order, rather than that applied depending on the Hebrew month (which, I expect, would explain why Rabbit's test threw up no flags).

The order is fine, the problem is that it shows all records instead of the last three only.

[Rabbit]

It looks like I was wrong. The WHERE clause is using the original order in the data source. I was just tricked because I included it as an output field and that seemed to be numbering the rows correctly. And no method of subquerying would fix the issue.

The only workaround would be to write out to a temporary table the sorted recordset before running the query. But that may be more trouble than it's worth. It should still be faster but it just becomes more complicated.

[NeoPa]

Moishy101:
[MonthOrder] is in a separate table so the sql will have to be adapted a little bit.

No. It may be in a separate table from the transactions, but the source for the SQL ([someTable]) is [qryTransactionH ebrew], which contains that data already.

Rabbit:
The only workaround would be to write out to a temporary table the sorted recordset before running the query. But that may be more trouble than it's worth. It should still be faster but it just becomes more complicated.

Unfortunate, after all that thinking and working out :-(

An alternative might be to add a field to the table (You may as well do two actually) to handle the ordering, which get updated when, and only when, the report is required. In all other circumstances, they should be considered to contain no valid data. With the relative data updated I don't expect the processing for the report would take too long. The updating might take some care to avoid making it take too long though.

[moishy101]

Thank you all for your help, I'm busy with several other projects right now, I hope to get back to this issue in a week or so.

[NeoPa]

No worries. I'm sure we can look at this again when you're next ready.

BTW. I missed a point from an earlier post which warrants a reply :

Moishy101:
The order is fine, the problem is that it shows all records instead of the last three only.

This shows that you haven't understood the point I was making from post #64. You may want to read it again, but it's a technical reason for why selecting the last three in the correct order doesn't work as intended. There's no point in selecting the last three of a set of records which are not already ordered in the correct sequence as that would simply result in three random records.