How to add values in a cell?

[Durga]

Hi there,



I am looking for help in adding values in a cell in Access 2003. The cell content looks like this 6481.14+4212.6+143.5. I need to total up these values and display it in a separate cell using a query or other alternative ways. Some cells contain a single value which is not a problem, but a cell can have upto 6 different values separated by '+' sign, which I need totalled and displayed using a query if possible (or in excel). I would appreciate any help with regards to this.

Many thanks,

DD

[Homegrownandy]

https://exceljet.net/excel-functions...-left-function

Just going on the information you have posted. I would recommend using the left and right function in excel to separate your data and then use the normal excel features to do what you want.

I would: copy everything to the right of the first + symbol. make sure you copy all the data in this new column and then PASTE VALUES so its not pasting the code. Repeat until there's no more + symbols. Then you can use a similar method to delete the data in each cell you don't want.

OR even better. wait for someone to answer on here that's smarter than me

This would work but I doubt its the best solution.

[Durga]

https://exceljet.net/excel-functions...-left-function

Just going on the information you have posted. I would recommend using the left and right function in excel to separate your data and then use the normal excel features to do what you want.

I would: copy everything to the right of the first + symbol. make sure you copy all the data in this new column and then PASTE VALUES so its not pasting the code. Repeat until there's no more + symbols. Then you can use a similar method to delete the data in each cell you don't want.

OR even better. wait for someone to answer on here that's smarter than me

This would work but I doubt its the best solution.

Thanks for your quick reply. I did consider this prior to posting but after having seen your post, I tried the 'Text to columns' option to separate the values in to various cells and used the 'SUM' function which does give the total, but this seems a long process as I need it instantaneous if possible. I understand some functions will be limited in the Access package but was wondering if this can be done in Access via a query. I know I have asked for solutions in Excel too which I have tried and it has worked, thanks Andy.

[Durga]

Thanks Andy for your reply. I considered this prior to my original post but after having read your post I went ahead and tried the 'Text to column' option to separate the values and used the SUM function to add the values which did give me the total. However still wondering if this is possible in Access via a query as I realise I need this to be calculated instantly as soon as one of the values in a cell changes, so it would be so time consuming to do this every time.

[Homegrownandy]

May I ask why the data is stored in such a way? I'm thinking this isn't the ideal solution to your problem.

But if you could briefly describe what it is you're doing and trying to achieve you may get some better help on here.

[ssanfu]

It sounds like you are familiar with Excel, since you keep using Exel terminology.
Access does not have "cells". Access has "fields". Access and Excel are two very different animals.

In saying that, I would guess you have designed your Access tables like an Excel spreadsheet.

I would suggest learning about Normalization, then redesigning your tables, first using pencil and paper, before using the computer.

If you want a review, post back with a description of what you are trying to accomplish and your table structures and relationships.

[Micron]

You might solve the problem with the Eval function, but I agree that big red flags have been raised regarding the design of this db. Better to fix all of the underlying design issues or many little tasks are going to be a headache.

[Durga]

May I ask why the data is stored in such a way? I'm thinking this isn't the ideal solution to your problem.

But if you could briefly describe what it is you're doing and trying to achieve you may get some better help on here.

My database is designed to manage data on clients. They are prescribed an item which costs so much, but when they are prescribed more than one item, I need to be able to get the total cost. As you know I Access each item will be listed in a separate row, but a query has been designed to display all the costs in a single field per client separated with the '+' sign. Do you get me now? I have made many design changes to the database but the original was done by another person.

[Durga]

It sounds like you are familiar with Excel, since you keep using Exel terminology.
Access does not have "cells". Access has "fields". Access and Excel are two very different animals.

In saying that, I would guess you have designed your Access tables like an Excel spreadsheet.

I would suggest learning about Normalization, then redesigning your tables, first using pencil and paper, before using the computer.

If you want a review, post back with a description of what you are trying to accomplish and your table structures and relationships.

Thanks for yourreply. Yes Access does have fields asopposed to cells, but as working with the datasheet view, I was referring tothe fields as cells.

The database isnot designed like an Excel spreadsheet, it has a very complex structure withlots of many-to-many relationships. Thedatabase is 15 years old (so a bit late to start with pencil and paper) and itsuits us perfect except for this calculation.

Say a clienthas so many accounts in a bank with a balance and you want to get the total ofall balances in accounts, is this possible in Access (2003)?

[Micron]

a query has been designed to display all the costs in a single field per client separated with the '+' sign

That would be your primary problem. If it has to be that way, then find something else to work with, or as already suggested, try the Eval function on your string of numbers and + signs.

Say a clienthas so many accounts in a bank with a balance and you want to get the total ofall balances in accounts, is this possible in Access

Definitely. With a Totals query. You can even separate the totals by account number by Grouping on client and account number, and Summing the balance.

[Durga]

That would be your primary problem. If it has to be that way, then find something else to work with, or as already suggested, try the Eval function on your string of numbers and + signs.
Definitely. With a Totals query. You can even separate the totals by account number by Grouping on client and account number, and Summing the balance.

Thanks for your reply. Will try this and hopefully work it out. Thanks guys for taking the time out to read and reply. Much appreciated.

[ssanfu]

From Post #1

The cell content looks like this 6481.14+4212.6+143.5. I need to total up these values and display it in a separate cell using a query or other alternative ways.

Another thought: I think you said that you have/use a query. You could write a UDF to take the text field ("6481.14+4212.6+143.5"), split it on the "+" into an array, add the array values and return the sum.


It would really help to see/use your dB.... would it be possible to post your dB with 3 - 5 made up clients (with records) to see what you have/what the problem is?
Do a "Compact & Repair", then Zip it......

Or a picture of your Relationship window........

[Micron]

Gents; if you add these numbers 6481.14+4212.6+143.5 you get 10837.24

I created a table tblTestEval, entered 6481.14+4212.6+143.5 into one field (testEval) as text, created a query with a calculated field and got 10837.24
The query sql is SELECT Eval([testEval]) AS Result FROM tblTestEval;
I did this because there doesn't seem to be much in the way of dismissing or accepting the idea I proposed back in post #7
Is that because I'm missing something about the original issue that makes the solution invalid?
@Durga: don't concern yourself with the fact that my data is in a table. A select query's results are a form of table, so it shouldn't matter that you don't have an actual table. The calculated field could be in the query that returns your concatenated string, as long as it doesn't create a circular reference.

[Durga]

Gents; if you add these numbers 6481.14+4212.6+143.5 you get 10837.24

I created a table tblTestEval, entered 6481.14+4212.6+143.5 into one field (testEval) as text, created a query with a calculated field and got 10837.24
The query sql is SELECT Eval([testEval]) AS Result FROM tblTestEval;
I did this because there doesn't seem to be much in the way of dismissing or accepting the idea I proposed back in post #7
Is that because I'm missing something about the original issue that makes the solution invalid?
@Durga: don't concern yourself with the fact that my data is in a table. A select query's results are a form of table, so it shouldn't matter that you don't have an actual table. The calculated field could be in the query that returns your concatenated string, as long as it doesn't create a circular reference.

OMG!! Micron, you're a legend. I tried as per your idea above and it worked a treat. I was doing this yesterday in Excel and managed to get the results in the format I needed after several hours but this EVAL function did all that in a few seconds.. this is what I was hoping for. I didn't quite understand when u had stated about the evaluation function before but with this example it was straightforward. So happy I could give you a hug... Hope good Karma comes your way very soon!! Thanks ever so much!!!

Thanks also to others who replied, it is very much appreciated guys. Hope you all have a nice festive period.

[Micron]

You're welcome, and I'm glad you were able to make use of it. Suggest if you don't understand a suggestion from a forum member that you ask for clarification or Google it. Otherwise, we (I?) am liable to think the suggestion is either unsuitable for the issue or simply being ignored. I was baffled as to why the thread was continuing on with difficulty when I knew it would work on a string like the one you posted. Good luck with your project!