473,326 Members | 2,126 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Access Grouping Question

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
4 1298
Minion
108 Expert 100+
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
JC2710
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
108 Expert 100+
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
JC2710
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

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

Similar topics

2
by: Paxton | last post by:
Hi, I'm trying to display the total page views per page within a given date range, but the correct SQL is seemingly beyond me. I get the correct result with a straightforward Group By and Count...
2
by: Andreas Håkansson | last post by:
Seeing how my previous post seem to have fallen between the cracks, I thought I would have a second, more direct, go at it. So my question is "Is it possible to group (Muenchian method) over...
5
by: Robert | last post by:
Hello Accessors I have some reports created in Access that are very good for what they do. However, it seems to me that when you are displaying information you don't need to print out that a...
5
by: Bob | last post by:
Hi Everybody Difficult question Has anyone else used the "Using the Tab control as a container" database example that comes with the above book chapter 7 on the accompanying disc. It is a...
11
by: James P. | last post by:
Hello, I have a report with the Priority field is used as sort order and grouping. The problem is the data in this Priority field if sorted in ascending order is: High, Low, and Medium. How...
8
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At...
4
by: ApexData | last post by:
I have one table used to maintain information on Service Events. Each record contains a Repair, Maint, and Battery checkbox (bound/boolean). Any one of these fields may be checked or left empty....
16
by: JoeW | last post by:
I'm utilizing a database that I created within MS Access within a program I've created in VB.NET. I am using the VB front end to navigate the information, but want to be able to print a report,...
6
by: craig.buchinski | last post by:
Ok, i have a Access Database (which is used with my ESRI Arcmap) and i have written a query for a report to pull out our streetlights and group them by billing wattage. The problem is that it is...
9
by: prakashwadhwani | last post by:
Hi !! I'm about to develop a new project for a client. Should I go about it in Access 2003 or 2007 ? Purchasing it either for me or for my client is not a major consideration here ... what I'd...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.