Can a group by clause be used with a user input? Please help me.

[Mitchell_Collen]

Can a group by clause be used with an input parmeter?

select count(field1),field3
from Table
Group by field1, @field3 <--user inputs what field

I want the user to be able to decide on specific field group by clause. The
count will be on the same field everytime however, instead of making 6
functions, I thought I could make only one and have the user select from a
list of different groupings. If I am not making sense let me know. I know
this is a kind of weird situation.

If you have done this before will you tell me what you did. I have considered
a macro but I do not know if this will cause lagging everytime the macro runs.
Also, I have never done this with a macro. Any suggestions or brainstorming
ideas about this is greatly appreciated.


Thanks in advance. -MC

 

[Guest]

I think you can do what you are asking by having a combo box with your
choices to select from on a form. Then put a button which runs the query. Do
not close the form upon the button click. Then the query can reference the
combo box info. This would be safer than having the user type anything.

hth,
James Deckert

 

[John Spencer]

Not really, but you could use some vba to select the proper field. Better
would be to build the SQL query on the fly with VBA.
Assumption: You have a two column combobox with column one being a
sequential number and column two being field names. Combobox is bound to
column 1
Then you probably can do something like the following when the form is open
and a value is selected in the combobox

SELECT Count(Field1),
Choose(Forms!FormName!ComboboxControl, Field2, Field3, FieldA) as ThisField
FROM Table
GROUP BY Choose(Forms!FormName!ComboboxControl, Field2, Field3, FieldA)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Mitchell_Collen via AccessMonster.com]

Thank you both. Your advice is very helpful. I am going to get started on
this now.
-Mitchell

Not really, but you could use some vba to select the proper field. Better
would be to build the SQL query on the fly with VBA.
Assumption: You have a two column combobox with column one being a
sequential number and column two being field names. Combobox is bound to
column 1
Then you probably can do something like the following when the form is open
and a value is selected in the combobox

SELECT Count(Field1),
Choose(Forms!FormName!ComboboxControl, Field2, Field3, FieldA) as ThisField
FROM Table
GROUP BY Choose(Forms!FormName!ComboboxControl, Field2, Field3, FieldA)

Can a group by clause be used with an input parmeter?

[quoted text clipped - 18 lines]

Thanks in advance. -MC