Ask Your Question
0

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

asked 2017-11-17 22:10:13 +0200

Blacksmithkazuma gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-11-17 22:30:36 +0200

Ratslinger gravatar image

updated 2017-11-18 05:55:27 +0200

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).

edit flag offensive delete link more

Comments

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?

Blacksmithkazuma gravatar imageBlacksmithkazuma ( 2017-11-17 23:36:38 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-11-18 00:05:10 +0200 )edit

Yea, each Total given by the Multiplication is a different Record. This might give you a better idea - https://gyazo.com/37b58a6638671e7f73b... 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.

Blacksmithkazuma gravatar imageBlacksmithkazuma ( 2017-11-18 00:16:00 +0200 )edit

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 gravatar imageRatslinger ( 2017-11-18 01:36:15 +0200 )edit

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")
Ratslinger gravatar imageRatslinger ( 2017-11-18 03:45:34 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-11-17 22:10:13 +0200

Seen: 129 times

Last updated: Nov 18 '17