Calculating Sum of values in column

[PavelowFE]

Hi,
I have a table that has one column, which will contain numbers input from the user on the form. I need to run a query or find a way to calculate the sum of the values in the column and be able to show that. The catch is that some of the cells contain multiple values separated by a comma. How can those be included as well?

Thanks,

Vince

 

[GKChesterton]

So the user inputs data such as:[tt]
250 ... or ...
250,3040,5
[/tt]And your field is in text format, presumably.

I'd say you want a table that stores the data in numeric format, and you want to use VBA to translate the input box response accordingly. The second line above would result in 3 new records.

If this change in structure isn't viable, then you have an extra-interesting code problem. A user-defined query function (using VBA) might be good. Problem is, you're still stuck with a highly inefficient table.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[MajP]

I assume you are using a multivalue field in A2007. Then it is the same as any other aggregate query.

SELECT Sum(Table1.val.Value) AS Total_Value
FROM Table1;

Although most purist will disagree with MS implementation of multi value fields, I disagree that it is inefficient.

If this is not a A2007 multi value field, then yes you have a bad design. You could write code to loop the records and sum the values.

 

[PavelowFE]

Yes, it is a multi-value field that is in numeric format. I'm looking to add the values and not the records. I knew when I was developing it that may not be the best idea, but I didn't want to repeat a process for gathering data.

Does that code go in the criteria block for the query of the table/column I want? My Access skills are very rusty from lack of use over the years.

Thanks,

Vince

 

[MajP]

I assume then your data looks like this

ID	val
1	1, 2, 3
2	2
3	5
4	1, 2

and you want

ID	SumOfval_Value
1	6
2	2
3	5
4	3

Then go to the query builder. Select your table. Add the ID field and the Val field. (You will need a unique key for each row like I have (ID).). Choose the summation sign from the menu bar. Then select Group by for the ID field, and Sum for the Val field. The SQL would then be

SELECT Table1.ID, Sum(Table1.val.Value) AS SumOfval_Value
FROM Table1
GROUP BY Table1.ID;

 

[MazeWorX]

By code ... note: I dont use 2007 so im not up on all of its differences

Dim db AS DAO.Database
Dim rs AS DAO.RecordSet
Dim intMyTotal AS Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("YourTableName", dbOpenDynaset)
intMyTotal = 0

Do until rs.EOF
intMyTotal = IntMyTotal + rs!YourFieldName
rs.MoveNext
Loop

' Here you can apply any other math you want to the total
' or assign the value to a control

Debug.print "Total: " & intMyTotal

Code can be attached to any event

HTH



Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[MazeWorX]

Sorry hit submit a little quickly. This differs from MajP's solution in that it sums a single field in a table with vba

explanation: Adds the field value to intMyTotal, moves to the next record, adds that value to the first, rinse and repeat until it reaches the end of the recordset.
HTH

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[MajP]

MazeworX,
Sorry, but your post makes no sense. Your code could only work on a standard field, not a multi value field as the OP suggests. Your code would throw a type mismatch error on a multivalue field. To handle a multivalue field you have to open a seperate recordset on the field. See

http://msdn.microsoft.com/en-us/library/bb258183.aspx

If you are suggesting to write code for a standard field, Why? You can clearly do this far easier and efficiently in SQL query.

 

[PavelowFE]

I assume then your data looks like this
CODE
ID val
1 1, 2, 3
2 2
3 5
4 1, 2
and you want
CODE
ID SumOfval_Value
1 6
2 2
3 5
4 3

Then go to the query builder. Select your table. Add the ID field and the Val field. (You will need a unique key for each row like I have (ID).). Choose the summation sign from the menu bar. Then select Group by for the ID field, and Sum for the Val field. The SQL would then be

SELECT Table1.ID, Sum(Table1.val.Value) AS SumOfval_Value
FROM Table1
GROUP BY Table1.ID;


I followed this example, but did not get the results I was looking for. I get an error message when running the query stating "Data type mismatch in criteria expression". If the cell has a single value in it, then it's fine, but when I have multiple values in it, I get that error.

Any ideas?

Vince

 

[MajP]

What is the datatype of the the multivalue field? Is it text or numeric.

 

[PavelowFE]

The multivalue field is numeric.

 

[MajP]

Can you post your whole sql, and list the datatypes of the fields in the SQL? FYI: In my above example if my field "val" was text, I would get a datatype error since
Sum(Table1.val.Value)
would try to sum a text field.

P.S. I should not have called the field "val" since this is a function in vb and is a reserved word.

 

[PavelowFE]

SELECT [OSJ Mission Report].ID, Sum([OSJ Mission Report].[# of Aircrew Trained]) AS [SumOf# of Aircrew Trained]
FROM [OSJ Mission Report]
GROUP BY [OSJ Mission Report].ID;

The field "# of Aircrew Trained" is a numeric one. It's a lookup to another table so that the user can select multiple aircraft, which has a set number of aircrew members for each one. The value for the field is dependent on the type(s) of aircraft the user selects so the result could be 4, 14, 2. My query should sum those numbers for that field so then I can total up the results from all the fields.

Thanks for your help.
Vince

 

[MajP]

That does not look like a multivalue field summation. Did you use the query builder to make that? If it is a mvf then you should get something like
Sum([OSJ Mission Report].[# of Aircrew Trained].Value)

You have a query for a standard field.

 

[PHV]

Anyway, unless you have to deal with a legacy sharepoint application you shouldn't use multivalue field ...

 

[PavelowFE]

I've abandoned that route. Working on something different.

Thanks to all for your help.

Vince