Hi, how can I add the values from a table using the query function?
Example:
User Jan Feb March April
User1 2 5 7 4
User2 7 6 8 10
I want the total for User1 and User2 respectively.
Sorry, I’m a new LO user.2021 Directory of Members_sample.odb
Formatted question for clarity
Thanks. Hope you can open the attachment now.
Please look at comments at end of answer. Need to click on see more comments as all are not shown immediately.
Hello,
You should always include what database you are using as this can affect the use of SQL. Base is simply the front end to the database you are using.
For HSQLDB embedded:
Edit 2021-01-30:
Have given you three different ways to fix your problem.
The easiest is to fill the empty TithesX fields with zeroes. Sample ---- OrigFieldTotalZeroFix.odb
The second method was SQL. coalesce uses the first non NULL value. For further information refer to the HSQLDB manual found here. Sample — OrigFieldTotalSQLfix.odb
The last method is using default values. This has situations you need to fully understand to use. Your setting shown in the question does not actually set the value in the table on a new recrod when nothing is entered. See → tdf#104375. To accomplish this, and as noted in the bug report, I used an SQL statement (Alter Table "Members" Alter Column "TithesX" set default '0' where X = 1 thru 12). This is entered through main Base screen menu item Tools->SQL. Sample ---- OrigFieldTotalUseDefault.odb
Finally, there was some other major problem with your sample. Stated there was macros there but each time I attempted to view them the file crashed. These sample are new files without this problem.
If this answers your question please tick the 
(upper left area of answer). It helps others to know there was an accepted answer.
Thank you. Where can I see the the database that I am using?
I used the same formula, but the totals are not showing.
Saw it already. Yes I am using HSQLDB embedded.
By the way, the field type of fields “Jan, Feb, March and April” are NUMBER(NUMERIC).
@fredprom@yahoo.de,
Your state it is not showing totals but do not know what you have done. Edit your question and post a sample file containing the problem but eliminate any personal or confidential information.
Thank you. May I send you the complete file to your Email? Of course without personal infos.
My email address: fredprom@yahoo.de
How do I post a sample file here?
Edit your question - edit lower right corner of your question. Then use toolbar (upper left in question) and select paperclip icon. See → How do I attach a file to my question/answer?
Hi, were you able to open the attached file? Thanls.
There is no attached file in your question. Have been waiting. I was the last to edit your question on 2021-01-25
Oh sorry. I thought I’ve sent it already. Anyway, here it is. I hope it works.
Oh sorry. I thought I’ve sent it already. Anyway, here it is. I hope it works.
Personally I do not use/like the Query Design mode. Has some problems and takes away from using/learnng actual SQL.
The problem you have is that you have numeric fields which contain NULL. A NULL is nothing. NULL with anything is NULL. Causes much problems. If you place zeroes in your empty “TithesXX” fields, the totals work fine.
You may want to read up on setting default values. This can also be overcome with SQL but that is another journey.
I attached the file already. Did you see any problem with the file? Thanks.
Please look at the comment just above yours. There I already posted the problem and answer almost two days ago. It was posted shortly after your posting of the sample.
You need to check your notifications (messages). New are signified to right of your User name as a red envelope. See → Notification icon.
Have directed you to that post before for attaching a file. You probably should review the entire post.
