Change group by field in query

  • Thread starter Thread starter Chris Moore
  • Start date Start date
C

Chris Moore

I have a simple table that contains invoices and the associated data (payee,
payee address, date, approver, etc...). I want to create a from that will
allow a user to quickly sum the dollar amounts based on a given field in that
table. So the user will be able to choose which field to group on from a drop
down and then see a list of the unique values in that field and the sum of
the dollar amounts associated with each unique value. The problem is I can't
figure out how to create that pick list of field names in a combo box and
then have the underlying query point to that value. Is there a solution to
this problem or perhaps a different way to approach it? Thanks in advance for
any suggestions.

-Chris
 
Can’t be done. But there is a work around.
Use a form with Option Group instead of combo.

Create a select query like this –
SELECT [Field1] AS [X_Field], [Field9]
FROM YourTable
WHERE [Forms]![YourForm]![Frame1] = 1;

Field1 is the Option Group choice with value of 1.

Then edit it to build union query like this –
SELECT [Field1] AS [X_Field], [Field9]
FROM YourTable
WHERE [Forms]![YourForm]![Frame1] = 1;
UNION ALL SELECT [Field2] AS [X_Field], [Field9]
FROM YourTable
WHERE [Forms]![YourForm]![Frame1] = 2;
UNION ALL SELECT [Field3] AS [X_Field], [Field9]
FROM YourTable
WHERE [Forms]![YourForm]![Frame1] = 3;
 
That works. Thanks Karl!

KARL DEWEY said:
Can’t be done. But there is a work around.
Use a form with Option Group instead of combo.

Create a select query like this –
SELECT [Field1] AS [X_Field], [Field9]
FROM YourTable
WHERE [Forms]![YourForm]![Frame1] = 1;

Field1 is the Option Group choice with value of 1.

Then edit it to build union query like this –
SELECT [Field1] AS [X_Field], [Field9]
FROM YourTable
WHERE [Forms]![YourForm]![Frame1] = 1;
UNION ALL SELECT [Field2] AS [X_Field], [Field9]
FROM YourTable
WHERE [Forms]![YourForm]![Frame1] = 2;
UNION ALL SELECT [Field3] AS [X_Field], [Field9]
FROM YourTable
WHERE [Forms]![YourForm]![Frame1] = 3;


Chris Moore said:
I have a simple table that contains invoices and the associated data (payee,
payee address, date, approver, etc...). I want to create a from that will
allow a user to quickly sum the dollar amounts based on a given field in that
table. So the user will be able to choose which field to group on from a drop
down and then see a list of the unique values in that field and the sum of
the dollar amounts associated with each unique value. The problem is I can't
figure out how to create that pick list of field names in a combo box and
then have the underlying query point to that value. Is there a solution to
this problem or perhaps a different way to approach it? Thanks in advance for
any suggestions.

-Chris
Click to expand...
Click to expand...
 
Back
Top