Looking for Form that does GroupBy

  • Thread starter Thread starter Gerhard Weiss
  • Start date Start date
G

Gerhard Weiss

When we add on new clients we have to do data conversions.
I normally importing the data into Access then use
the Queries Groupby feature to see the number of different
values in a column.
i.e. I might have a Class field and the results would be
Cls Count
A 30
B 25
F 50

Right now this takes a lot of clicks to manually analyze all
the fields in one table.
I am looking to automate this process a little by having one
Form that will allow you to pick a Table and then a Field
inside of that table. It would then execute the Groupby query
showing the values in a the field plus a count (the number of
time it shows up in the field.)

I have to believe lots of other people use Access in a similar
fashion and that this code or Form should be available
somewhere out their on the net. I just do not know where to
look. Any help would me much appreciated.
Thanks
 
Gerhard said:
When we add on new clients we have to do data conversions.
I normally importing the data into Access then use
the Queries Groupby feature to see the number of different
values in a column.
i.e. I might have a Class field and the results would be
Cls Count
A 30
B 25
F 50

Right now this takes a lot of clicks to manually analyze all
the fields in one table.
I am looking to automate this process a little by having one
Form that will allow you to pick a Table and then a Field
inside of that table. It would then execute the Groupby query
showing the values in a the field plus a count (the number of
time it shows up in the field.)

I have to believe lots of other people use Access in a similar
fashion and that this code or Form should be available
somewhere out their on the net.


Well, I'm unfamiliar with anyone having done this (for
whatever that's worth), but it doesn't sound too complicated
(once you figure out how ;-))

You can use VBA code to construct the form's Record Source
query using an alias for the field so you don't have to fool
around with the bound text box.

Assuming that combo box, cboField, has the name of the field
you want to group on, you can use code like the following in
the combo box's AfterUpdate event, or, maybe better, a
command button's Click event.

strSQL = "SELECT " & cboField & " As TheField" _
& ", COUNT(*) As FieldCount " _
& "FROM yourtable " _
& "GROUP BY " & cboField
Me.RecordSource = strSQL

The two text boxes in the form's detail section would be
bound to the query fields TheField and FieldCount.
 
Back
Top