A problem that I have had lurking around for ages now is how to extract or calculate the number of records in a sequence - e.g. in a table/query ordered by ID and date, the number of consecutive records by date for a given ID that have a value >= 50 in a given field. Is there a way of doing this purely within a query, or would I have to resort to a VBA loop through a recordset and keep a count of the consecutive records that match that criteria? It isn't a particularly difficult thing to code, but I always prefer the most elegant solution wherever possible!
Queries to extract sequences
- Thread starter Chiggers5
- Start date
Yes you can do this solely with queries, but when you bring elegancy into the equation, VBA might be better. In fact the way I would accomplish this would be to write a VBA function and then use it in a query.
You really want a specialized running total query. Search the forum for that: 'running total query'.
You really want a specialized running total query. Search the forum for that: 'running total query'.
Thanks guys - looks like vba and loop will be the best way, and I will be able to extract many different sequence items in the same process with a bit of thought. Just got to dust off my old nested Do...While skills now!
