Calculated field grouping records by their values in certain fields

1. Novice
Windows 7 64bit Access 2007
Join Date
Dec 2011
Posts
4

Calculated field grouping records by their values in certain fields

I want a calculated field grouping records in a query by their values in certain other fields.

The query has five fields from a table: Num1, Num2, Num3, Text1, Text2

The query is sorted by Num1, Num2, Num3, Text1 (all ascending)

I want a calculated numeric field, GoGroup. Here's what it would do:

With the records sorted as specified above, the first group of records (which could be as few as 1 record) to all have the same value for Num1 AND all have the same value for Num2 AND all have the same value for Num3 will have the value 1 for GoGroup. (For clarity, when I say "same value", I don't mean that the value for Num1 is equal to the value for Num2 or Num3, although that could happen. The value for any of these fields could be any integer. The point is that all the RECORDS have the same set of three numbers for these three fields.)

The next such group of records will have the value 2 for GoGroup. The next one will have the value 3. And so on.

How do I do this?

Anyone who answers this question in the next 24 hours is a dedicated programmer indeed. I await your New Year's wisdom!

2. Show sample of the sorted data and sample of what the output would look like so have something to test on. Can be an Excel spreadsheet.

3. Novice
Windows 7 64bit Access 2007
Join Date
Dec 2011
Posts
4

I've provided a chart below that gives the idea of what the query results would look like. I've also added a column on the right. Let me say a little about the chart.

Notice that --

1) The records are sorted by Num1, Num2, Num3, Text1.

2) This puts the records into groups. In each group, Num1, Num2 and Num3 are the same. For example, in the first group, these fields have the values 2,34,7 for all records.

3) A calculated field, GoGroup numbers these groups.

4) I've added a field on the right, GoGroupCount, which would be another calculated field that gives the number of records in the group to which each record belongs. (More on this below.)

5) Num2 and Num3 can be null. Num2 can only be null if Num3 is null.

After the table, I'll say where I'm going with all this.

Code:
```GoGroup	Num1	Num2	Num3	Text1	Text2	GoGroupCount
1	2	34	7	cat	hello	2
1	2	34	7	dog	hello	2
2	5	64	8	bus	hello	1
3	23	24		pony	hello	1
4	23	24	5	bear	hello	4
4	23	24	5	car	hello	4
4	23	24	5	doll	hello	4
4	23	24	5	king	hello	4
5	23	24	7	arm	hello	1
6	43			leg	hello	1
7	43	1	9	cats	hello	2
7	43	1	9	horse	hello	2
8	64	17	23	goat	hello	2
8	64	17	23	shop	hello	2
9	64	17	81	kids	hello	1
```
Okay, here's where I'm going with this.

1) I'd like to be able to take the records from the query, group by group -- one group at a time -- and do something with them. This must be done manually, and something different must be done to each group, depending on the values of Num1, Num2 and Num3. But this thing can be done simultaneously to all the records in a group, which is why it's an advantage to group them.

2) To simplify the process of taking the records for the manual process, and avoid mistakes, it's important that I be able to see just one group at a time in a query -- not this query, but another one down the road, that's based on this.

3) I want the GoGroup field as a handle for each group. Actually, in another query, I'd just call up each group in turn by its GoGroup value. This way, I'd just have to enter the value for one field (GoGroup), rather than three (Num1, Num2, Num3).

4) It would also be an advantage to prioritize the records, taking the largest groups first. That's why I added the field on the right, GoGroupCount. Maybe it shouldn't be a field in THIS query. But if, in another query, I could sort the groups by GoGroupCount and in that way see the GoGroup field sorted with the biggest groups first, I could then -- in yet another query, perhaps -- call up the groups by their GoGroup value, one group at a time and the biggest groups first -- and then process each group as I need to.

That's what I'm aiming for. Can you help me, June7 who works on Jan1?

Thanks!

4. Ranking and Numbering Records Query

Review these links for info on ranking and numbering records.
http://support.microsoft.com/kb/120608
http://allenbrowne.com/ranking.html
http://forums.aspfree.com/microsoft-...ge-440477.html

Your data and what you want to do don't really fit any of those examples. You can always do a grouping and filtering on value of concatenated Num1, Num2, Num3 fields. I used that idea to accomplish creating a sequential group value with a series of queries which I show here as one nested statement:

SELECT x.GroupID, (SELECT Count(GroupID) +1 As GroupNum FROM
(SELECT Format(Nz([Num1],0),"00") & Format(Nz([Num2],0),"00") & Format(Nz([Num3],0),"00") AS GroupID FROM
(SELECT Format(Nz([Num1],0),"00") & Format(Nz([Num2],0),"00") & Format(Nz([Num3],0),"00") AS GroupID, Table1.* FROM Table1
ORDER BY Format(Nz([Num1],0),"00") & Format(Nz([Num2],0),"00") & Format(Nz([Num3],0),"00")) As Query1
GROUP BY Format(Nz([Num1],0),"00") & Format(Nz([Num2],0),"00") & Format(Nz([Num3],0),"00")
ORDER BY Format(Nz([Num1],0),"00") & Format(Nz([Num2],0),"00") & Format(Nz([Num3],0),"00")) As Query2
WHERE x.GroupID>GroupID) AS GoGroup, x.Num1, x.Num2, x.Num3, x.Text1, x.Text2
FROM (SELECT Format(Nz([Num1],0),"00") & Format(Nz([Num2],0),"00") & Format(Nz([Num3],0),"00") AS GroupID, Table1.*
FROM Table1
ORDER BY Format(Nz([Num1],0),"00") & Format(Nz([Num2],0),"00") & Format(Nz([Num3],0),"00")) AS x;

As pointed out by Allen Browne in the second link above, a ranking or numbering value cannot be used to sort or filter records in the same query but should be possible in queries based on this one.
Last edited by June7; 01-04-2012 at 03:31 PM.

5. Novice
Windows 7 64bit Access 2007
Join Date
Dec 2011
Posts
4

Yes, that worked!

June7,

Yes, that worked. Of course.

Thank you very much!