Can access use the SQL SUM function?

[thrainpa]

This issue has been resolved elsewhere, but thanks go to plog for his help and insight.

I'm trying to run a query as a rowsource in a combo box as

SELECT Table.[Field1], Sum(Table.Field2) AS Total_Sum
FROM Table
WHERE (((Table.[Field3])=[Number]));

If I remove 'sum()' then the box shows the first results from Field2, but as soon as I add 'sum()' it returns Field1 instead, I assume this is because Field2 returns null.

Can anyone help me with this?

 

[plog]

What you are running is an aggregate query. In an aggregate query any fields you bring in the SELECT clause that you are not doing a calculation on (SUM, MAX, MIN, COUNT, etc.) you need to include in a GROUP BY clause.

This SQL should make your query work:

SELECT Table.[Field1], Sum(Table.Field2) AS Total_Sum
FROM Table
GROUP BY Table.[Field1]
WHERE (((Table.[Field3])=[Number]));

 

[thrainpa]

Thank you very much for your help but I have discovered a seperate working method to my problem.

All contributors to this thread and my knowledge will be thanked. :3

 

[MSAccessRookie]

Thank you very much for your help but I have discovered a seperate working method to my problem.

All contributors to this thread and my knowledge will be thanked. :3

I too would have suggested that you use GROUP BY as a method to resolve your situation. I would be interested to find out the resolution that you have opted to use, as I am always interested in learnin about new and different approaches to solving problems.

-- Rookie

 

[thrainpa]

I too would have suggested that you use GROUP BY as a method to resolve your situation. I would be interested to find out the resolution that you have opted to use, as I am always interested in learnin about new and different approaches to solving problems.
-- Rookie

Don't get me wrong, the GROUP BY method worked but there was an unintended side effect to it; since the SQL statement was for a combo box, regardless of the column widths, the first column would have to be the control source, which means if the SQL statement in rowsource (column 2) returns null or 0, then the field is filled in by the control source (column 1), whereas I needed it to show blank for that situation.

I got around it by, instead of using a combo box and SQL statement in rowsource, i used a textbox that referred to a public function which was able to return specifically what I wanted from my SQL statement.