By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,223 Members | 1,633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,223 IT Pros & Developers. It's quick & easy.

Access Grouping Question

P: 39
Hi

I have a table in which I want to Group Records Together:

Code Process Pages
56 3 S/S
56 3 S/S
56 3 S/S
56 4 S/S
56 4 S/S

I want to group by Code Process and Pages and I want to number the Groups 1, 2, 3 etc

so i want to end up with

Code Process Pages Group No.
56 3 S/S 1
56 3 S/S 1
56 3 S/S 1
56 4 S/S 2
56 4 S/S 2


Can I do this with a query? or do i have to loop thru recordset?


Thanks
Feb 5 '08 #1
Share this Question
Share on Google+
4 Replies


Minion
Expert 100+
P: 108
Both methods would accomplish what you are looking for. Recordset looping would allow you to assign the group value that you were speaking of, but depending on the number of records you have the machine it is running on may take some.

Alternately you can use an update query to assign these values. However, I am unable to determine what types of criteria you might set from what has been posted. Still the generic form of an update query would look something like:

Expand|Select|Wrap|Line Numbers
  1. UPDATE table
  2. SET fieldName = "<<value to update to>>"
  3. WHERE fieldName = "<<criteria>>";
  4.  
Hope this helps. If you would like more specific help please post further information such as what types of criteria you are using for assigning the group numbers.

- Minion -
Feb 5 '08 #2

P: 39
Hi

Thanks for the reply. I basically want to assign each group a number and increment it by one.

So the first group would be group 1 etc.

Code .....Process.....Pages
56............. 3.............S/S
56............. 3.............S/S
56............. 3.............S/S
56............. 4.............S/S
56..............4.............S/S

becomes..........

Code .....Process.....Pages........GroupNumber
56............. 3.............S/S................1
56............. 3.............S/S................1
56............. 3.............S/S................1
56............. 4.............S/S................2
56..............4.............S/S................2

Anyway of doing this with a Query rather than recordset?

Thanks
Feb 5 '08 #3

Minion
Expert 100+
P: 108
Yes this can be done with a query, but there's a couple hitches.

First, the good news. If the group number is direct corilation to the process number as your example shows you can set the update value to [b]Process - 2[/] as it is represented in the example.

Otherwise the other way this will have to be done is manually setting the update value and criteria for each grouping and then run the query before repeating the next grouping.

By far the first method is the best if indeed the group number need to be a set value lower or higher than an existing value such as the process. Below I have added the MS Query SQLish code that I used to successfully acomplish this.

Expand|Select|Wrap|Line Numbers
  1. UPDATE [JC2710's Question] SET [JC2710's Question].GroupNumber = [JC2710's Question]![Process]-"2";
  2.  
All you need do is replace "[JC2710's Question]" with the name of your table providing the field names are the same. To use this just make a query in design view and switch it to an update query. Then view the SQL for the query and replace what ever is there with the above.

Hope this works for you. Let me know.

- Minion -
Feb 5 '08 #4

P: 39
Thanks For your help. But i want the Group number to increment by one on each change of group.

So if I have 10 groups i want them to be called 1,2,3,4,5 etc.

Once again it seems as though SQL is a pain in the neck!!! I sometimes wonder if its of any use at all apart from...."SELECT field FROM table" it seems as though its more trouble than its worth.

I nearly always use recordsets. Takes longer as is inefficient but at least it has some flexibility and u can get what u want.

Thanks again anyway
Feb 5 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.