Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 counts by nested groupings

Author  Topic 

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2005-02-15 : 13:21:33
Esteemed friends, Is there a way to write a Query to produce something like the following?

My table looks like this (sort of).

Customer,Event,Error,Error_Desc


I need a query to produce something like this:

Event_one | 19
Error_one | 4
error_desc1 | 1
error_desc2 | 2
error_desc3 | 1
Error_two | 6
error_desc1 | 1
error_desc2 | 4
error_desc3 | 1
Error_three | 7
error_desc1 | 1
error_desc2 | 5
error_desc3 | 1
Error_four | 2
error_desc1 | 1
error_desc2 | 1

Event_Two |
Error_one | 1
error_desc3 | 1

Event_Three |....


you get the idea? I want to count by a nested grouping or something like that.
What's the best way to do this?

Thanks a million.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-15 : 14:10:23
You can simply create an Access report to do the grouping as needed.

- Jeff
Go to Top of Page

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2005-02-15 : 14:36:27
Ok, but there a SQL way? I may wind up converting my app to MSSQL or MYSQL down the road - I'd like to learn how to do this sort of thing in SQL alone. Thanks!
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-15 : 15:03:50
SQL isn't designed for presentation -- its job is to return raw, unformatted data in a simple, standard row/column format. The presentation layer -- in this case, Access's Report writer -- is what must do the indenting, grouping and formtating of the raw data.

Once you move to MSSQL or MySQL, you find out that they don't HAVE a presentation layer! they are server applications. so you will use another product -- even Access -- to present the raw data returned from your SQL statements. SQL itself and databases in general are not designed to present data in a nice readable format -- they are designed to store, alter and retrieve data.

This is a very, very important distinction to understand. Access blurs the line a bit, because it is not only a database, but also a presentation tool and often the functionality of the two is interwoven and hard to separate. But even within the framework of Access, there are database aspects vs. presentation aspects that you should be aware of.

- Jeff
Go to Top of Page

JodyWood
Starting Member

3 Posts

Posted - 2005-02-15 : 15:24:49
SELECT Event, Error, Error_Desc, COUNT(1) As Cnt
FROM table
GROUP BY Event, Error, Error_Desc
Go to Top of Page

sanu_s
Starting Member

2 Posts

Posted - 2005-02-16 : 12:26:50
Thanks a million.'
Can you elaborate about the table structure and the way you want to retrive the data.
According to my understanding you need to convert columns into rows that is pivote table.
Go to Top of Page

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2005-02-19 : 15:32:10
Thanks for your help. I was used to working with SQLserver and MYsql in the past, but never had to do any sort of presentation data.. I could do that in php or whatever.. now I have to use access and, your right,its a whole different animal. Anyhow, the solution I arrived at, that was actually clean and easy was a simple select query like posted above, populating and excel pivot table... looks perfect, and will work down the road too.

Thanks again. all of you.
Go to Top of Page

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2005-02-19 : 15:35:52
PS. JodyWood! That was excellent. Perfect. Thanks.
Go to Top of Page
   

- Advertisement -