Summing up records in a Form

  • Thread starter Thread starter Hajo von Kracht
  • Start date Start date
H

Hajo von Kracht

(Sorry for cross-posting. I accidentally posted to the "General Questions"
track, but it fits obviously better here:)

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.
 
You need to use an alias for the field Sum(mytable.myNum)

Try:
SELECT mytable.myTxt, Sum(mytable.myNum) AS SumOfmyNum FROM mytable WHERE
mytable.myFilter = "f1" GROUP BY mytable.myTxt;


You can use any legal name for the alias name:

Sum(mytable.myNum) AS SumOfmyNum
Sum(mytable.myNum) AS myNumTotal
Sum(mytable.myNum) AS abcdef1234

just not the same name as another field.

HTH
 
Back
Top