Adding 6 Fields for Total but some fields will have no Data to Add

[Blacksmithkazuma]

OK so I have been working on a type of shipment form. I am still very very new to Base and SQL so forgive me. What I am attempting to do is Add 6 Fields from the same Table with Numeric Values (Item Amounts/Quantities) to get a total. Not all Fields will have Data in them at times. I am doing this through a Query. I have tried

SELECT 'Amount 1", Amount 2", etc ("Amount 1"+"Amount 2"+"Amount 3"+etc) AS "SUM" FROM "Shipments"`

With Item Grand Total as the Alias as in the form that is where I would like to post the sum. I have been able to do a similar thing with Multiplying two amounts and it showing the total in a certain field. But I am not sure if the Null/No Data is throwing the Adding off or what. Any help would be appreciated please and thank you very much.

[Ratslinger]

Hello,

Where you have an amount which may be NULL, you can use CASEWHEN to determine what value to actually use. For example:

 CASEWHEN("Amount 1" IS NULL,0,"Amount 1") + CASEWHEN("Amount 2" IS NULL,0,"Amount 2 ") As "Sum"

This checks the field in question and if true uses the first value ( 0 in this case) or if false uses the second value (actual field amount here).

Edit:

Aside from the SQL answer in the comments, I finished the demo with a structure which may give you more flexibility. This is not meant as an answer for your current project, but possibly to give you some ideas on what I have been trying to say about structuring your DB better.

The file has 4 tables: Customers, Orders, Items and Filter (used for selection criteria). There are two forms and one Query. The first Form, CUSTOMERS allows for entry of new customers (other information should be added to this record) and order information. Again more information needs to be added such as maybe an order date. The sample separates information based upon the customer and the order number.

Once you have this information go the the form OrderDisplay. Here you can get information on an order for a customer. First select a customer from list & press Select Customer button. This will fill the second listbox with that customers orders. Then select an order number from the second listbox and press the Select Order button. This then fills out the rest of the form. The table grid displays individual items along with a calculated line total and below the grid are totals for the order.

There are no macros involved and no totals are stored in any of the records. All totals are calculated through either queries in the form or the single external query.

Hope this gives you a better idea of where you can progress to.

Sample: ShippingInvoicing.odb

One more thing. The data in the tables can then be used to print the same information using Report Builder. Report Builder has its’ own totaling capabilities.

If this answers your question please click on the (upper left area of answer).

[Blacksmithkazuma]

Will that also work if for example: I have a query with Total 1-6. Each total’s field in the query has the formula “Amount 1”*“Cost 1” with the alias set to Total 1. I know it wont save to the Table but could I still use the same adding formula to calculate the Grand Total from each of the Totals?

[Ratslinger]

I believe this is what you are asking:

CASEWHEN(“Amount 1” IS NULL,0,“Amount 1”) * “Cost” AS “Total 1”

Not sure what you are talking about Grand Total. Is each total from a different record? Please clarify.

[Blacksmithkazuma]

Yea, each Total given by the Multiplication is a different Record. This might give you a better idea - Screenshot - 37b58a6638671e7f73ba832b121e3142 - Gyazo The Total in the form is done by Query, multiplying the two. I was able to figure out the Total Items. Since the Data is saved in the actual table. But with the Total Costs of each item being calculated via the Query I am having a hard time getting it to add. I have had success a couple of times, but it just randomly stops working.

[Ratslinger]

As I just finished another question, give me a bit of time to put together some samples for you. As stated in your previous question, I believe your DB structure is lacking. I’m sure you want to save this information for future reference thus the DB.

[Ratslinger]

Sorry for the delay. I was actually putting together a different structure for you but changed my mind. I’m not certain where you are going with your project so I put a stop to it near the finish. For your actual last question, you create totals in a SELECT statement based upon another SELECT statement. Once you have the A*B results you can just total all up:

SELECT SUM(QUANTITY) "TOTAL QUANTITY", SUM(TOTAL) "SUM"  FROM (SELECT QUANTITY, "Amount 1"*"Cost 1" AS TOTAL FROM "Shipments")