This one looks like the same problem/question for the 3rd thread.
https://www.access-programmers.co.uk...-query.329988/
https://www.accessforums.net/showthr...862#post520862
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
You question can be paraphrased as 'How to make a query for getting the sum of any random 5 records for each id'. To decide which records from all of records with same ID value must be summarized, you need some additional column to decide which ones to summarize. The reason is, that in Access, there is no way to say a record is before or after of any other, without having a field which determines this order.
Can you upload a copy of your database?
You can PM me if you need further help.
Good Reading https://docs.microsoft.com/en-gb/off...on-description
I think this is what you're looking for: Microsoft Access tips: Subquery basics (allenbrowne.com)
See "Top N Records per Group"
In T-SQL you can do it the easy way using CROSS APPLY... yeah, but this isn't T-SQL.
Oh, this is a HASSLE. You need a "uniquifier" so that the data engine can choose ONE of the tied records. (If you just query for SELECT TOP 1 ... and you just order by Mark, any records with the same mark value will be lumped together! One of the things I really like about the ROW_NUMBER() function in T-SQL!!!)
Last edited by madpiet; 01-25-2024 at 06:22 PM.