Summing up records in a Form

[Hajo von Kracht]

I have a table with four fields: (1) a unique ID, (2) a filter field, (3) a
text (not unique) and (4) a number.
In my form I want to filter the table by a combox selection (in this case
the value "f1") and collapse the remaining records with identical text and
show the sum of the number.

Records (myId, myFilter, myTxt, myNum):
1, f1, texta, 9
2, f1, texta, 8
3, f2, texta, 7
4, f1, textb, 6
....

I want to filter only the "f1" records and see:
texta, 17
textb, 6
....

Looks fairly easy. I know the SQL syntax for this, and with some string
concatenation acrobatics I can plug it into the RecordSource attribute of my
table:

SELECT myTxt, SUM(myNum) FROM table WHERE myFilter = "f1" GROUP BY myTxt;

But how do I get the aggregated data ("SUM(myNum)") into the darn Text boxes
of my Form? I have been fighting with this for hours, so far with no success.
The system yells at me with "NAME?" and "ERROR", and I see no way how I can
tell either to the SQL side how to call the resulting aggregate, so the Form
gracefully accepts it, nor can I tell the form to accept what SQL sends.

Any help appreciated.

 

[Douglas J. Steele]

Alias the field (using the AS keyword):

SELECT myTxt, SUM(myNum) AS TotalNum
FROM table
WHERE myFilter = "f1" GROUP BY myTxt;

You now refer the field as TotalNum

 

[Tom Wickerath]

Hi Hajo,

Try using a listbox control instead of a textbox. Set the row source for the
list box programmatically, using the after update event procedure for your
combo box.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Hajo von Kracht]

Thanks Doug.
Hard to believe but it really works. Like walking through an imaginary wall
of bricks:
The Forms Designer doesn't offer you this alias as an option, and when you
enter it by hand, it even warns you with a yellow exclamation mark that this
is all wrong.
And when you do it anyway, --boof-- it works.
Cheers
Hajo