Results 1 to 2 of 2
Thread: Numbering form subgroups x of y
2009-07-20, 00:23 #1
- Join Date
- Nov 2001
- Sydney, New South Wales, Australia
- Thanked 0 Times in 0 Posts
I am setting up a client list of quotes and whereas a client might have a multitude of quotes over a period of time, they are actually in groups as a client will ask for variations to get the best deal. My question is, how can I get x of y numbering within a subgroup of all forms? For example, I might ask for quotes on house insurance and car insurance but I might consider three or four variations of both before I decide which to quote to accept. There could be 4 quotes for the house and 3 for the car making 7 quotes altogether, but I want the 4 house quotes to display as 1 of 4, 2 of 4, 3 of 4, and 4 of 4 and the car quotes to display as 1 of 3, 2 of 3, 3 of 3. If I decide to get an additional variation on the house quotes, then the new numbering would need to change to 1 of 5, 2 of 5 etc. A few years later when I upgrade my car, I might need to consider 4 more variations making 12 records altogether but having each group numbered separately.
I can set up numbering to do all forms as per http://www.mvps.org/access/forms/frm0026.htm but the sub numbering has me stumped.
Any help would be greatly appreciated. Thanks.
2009-07-20, 00:57 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Grouping is not a "natural" concept for forms, so it will not be easy to accomplish, especially if you want to restart numbering within the same category as in your car example.
If you have an AutoNumber field, you could play with DCount expressions, e.g.
=DCount("*", "tblSomething", "CategoryID=" & [CategoryID] & " And RecordID<=" & [RecordID])
for the "x" part, where tblSomething is the record source, CategoryID is the (number) field specifying the category and RecordID is the AutoNumber field. And
=DCount("*", "tblSomething", "CategoryID=" & [CategoryID])
for the "y" part. But this won't enable restarting numbering within the same category.
In reports, grouping is much more natural. You can use a text box with control source =1 and RunningSum set to Over Groups or Over All to get a running count per group.