How to create auto-grouping based on change in previous record

[xav78]

Good Day All,

Looking for some support with this example below, I have a column "CAT" each time that CAT switches between 0 and 1, I would like my query to auto-create a "grouping" and increment the group by 1. What I am looking for is the output as shown below. Any suggestions would be wonderful.


ID CAT GROUP
67 0 1
68 0 1
69 0 1
70 1 2
71 1 2
72 0 3
73 0 3
74 0 3
75 0 3
76 1 4
77 1 4

 

[MarkK]

That is hard enough to do in a query that in most cases it isn't really worth it. I would do that step later in the output process if possible. If this data, for instance, is eventually displayed in a report, do this step in the report, not in the query.

Alternatively, you could add the Group field to the table, open a recordset on that table--sorted as your query would be sorted--and then calculate the numbers into the table. Then open the query. But that presents problems too, and it's a sort of make-work project in a lot of ways, because the sequential numbers are volatile, right? They are not unalterably part of the raw data, they are a sort of secondary calculation that could change with every edit of the raw data, so you can never trust them. In a pure sense they are not good candidates to store with your actual data, but this approach would be workable.

But I would try to do it after the query is run, and before the final presentation is complete, as I mentioned in the first paragraph.

 

[xav78]

That is hard enough to do in a query that in most cases it isn't really worth it. I would do that step later in the output process if possible. If this data, for instance, is eventually displayed in a report, do this step in the report, not in the query.

Alternatively, you could add the Group field to the table, open a recordset on that table--sorted as your query would be sorted--and then calculate the numbers into the table. Then open the query. But that presents problems too, and it's a sort of make-work project in a lot of ways, because the sequential numbers are volatile, right? They are not unalterably part of the raw data, they are a sort of secondary calculation that could change with every edit of the raw data, so you can never trust them. In a pure sense they are not good candidates to store with your actual data, but this approach would be workable.

But I would try to do it after the query is run, and before the final presentation is complete, as I mentioned in the first paragraph.

Ironically enough it is the last step I need. The output of this is required as it becomes the criteria for a running total query. :banghead:

 

[plog]

Here's a way to do it all via queries:

First, you didn't provide the name of your table, so I used "YourTableNameHere" as the name of your table. To make this code work for you, replace all instances of that with the name of your actual table.

Second, its going to take 3 queries total. This is the SQL of the first:

SELECT YourTableNameHere.ID, 1*DMax("[ID]","YourTableNameHere","[ID]<" & [ID]) AS PriorRecord
FROM YourTableNameHere;

Paste that into a query and name it 'Groupings_sub1'. It determines the ID of the prior record for each record. With that you can determine if the CAT changed between the two. This SQL will do that:

SELECT YourTableNameHere.ID
FROM (Groupings_sub1 INNER JOIN YourTableNameHere ON Groupings_sub1.ID = YourTableNameHere.ID) INNER JOIN YourTableNameHere AS YourTableNameHere_1 ON Groupings_sub1.PriorRecord = YourTableNameHere_1.ID
WHERE (((IIf([YourTableNameHere].[CAT]<>[YourTableNameHere_1].[CAT],1,0))=1));

Name that query 'Groupings_sub2'. Lastly, the below query will produce the results you want:

SELECT YourTableNameHere.ID, YourTableNameHere.CAT, 1+1*DCount("[ID]","Groupings_sub2","[ID]<=" & [ID]) AS [GROUP]
FROM YourTableNameHere;

 

[JHB]

Use an UDF together with your query, I've made an example in the attached database. Run the query SetGroup.

 

[xav78]

Thank you very much to both PLOG and JHB, I apologize for not thanking you sooner! solutions both worked beautifully!

 

[spikepl]

For this kind of manipulation, and for running totals in queries, I also normally write UDF's using a Static to remember relevant value from last time (for running sums this remembers the sum so far).

Any opinion as to when it would be silly to use UDF here? I do not want to start a war, am just asking for opinions.