Grouping Based On What's Selected In List Box

[joesmithf1]

Hi,

I have a form with 1 List Box. This list box contains the names(SSN, Department, Status) of the columns in TableA.

The question is, can I create just ONE query statement, and base the GROUP BY on whichever field the user selects?

For example, if a user choose Department, then the query will group by Department. I know how do this by the long way; meaning I create 3 separate for each,(qrySSN, qryDepartment, qryStatus) and if say the user select SSN, it will open the qrySSN query. This is way too much maintenance. I have a feeling there is a much easier way. Please advice.

Thank you.

Joe

 

[grnzbra]

Seems like the easiest way to do it would be to modify the GroupBy statement of the query's Query Definition.

 

[joesmithf1]

I am thinking I can create a 'control' and then apply VBScript to the control like this:

Private Sub OK_Click()
Me.Visible = False
DoCmd.RunSQL "Select [Department],[SSN],[Status] FROM TableA Group By [" & Category & "];"
End Sub
'Where Category is the name of the list box

Now the question is, what do I need to add to the script codes above to be able to view the result in a report?

 

[grnzbra]

I think GroupBy in a query referrs to some kind of aggregate on one or more fields. It looks like you would need an OrderBy clause.