Ask Your Question

Base - calculations from calculated field

asked 2016-12-25 17:20:47 +0200

DougKMiles gravatar image

updated 2020-08-07 03:23:08 +0200

Alex Kemp gravatar image

Newbie to databases and SQL, so any help will be most appreciated.

I've created a one-to-many DB with a subform that produces subtotals, and a subsubform that provides a Total of the subtotals for each unique master record. (thanks Ratslinger!)

Now I'm trying to create a field that subtracts that Total from a Cost field in the master table. From reading on the forum it appears that has to be done with an SQL command. I've tried a bunch of things, but keep getting "syntax error." Frankly that's where I'm in completely over my head.

Can anyone provide any guidance or suggested tutorials on how to accomplish this?

Many thanks in advance.


edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-12-25 21:26:30 +0200

Ratslinger gravatar image

Here is the same sample with another field added for net. This field is is on a separate subForm and uses a bit more complex SQL. Since this is seemingly an area you may be weak on, it's a good place to start improving upon. If you're going to be working with base & tables, SQL is essential.

The statement used contains a (left) join which basically uses the same SELECT from the totaling routine and combines it with information in the main table allowing the calculation. The best thing to do is understand how this statement gets the information together to allow the displayed data.

Sample: TableTotals.odb

edit flag offensive delete link more


Thanks again Ratslinger!

"Weak on SQL" is putting it mildly. If someone wanted to try to get up to speed in this area, is there any basic reading or "SQL for Dummies" you might recommend?

DougKMiles gravatar imageDougKMiles ( 2016-12-25 21:34:01 +0200 )edit

I can't recommend any books (although I do believe there is a "SQL for Dummies" book). I tend to use this site (click here) and a few others for reference. There is also the HSQL manual (embedded Base DB uses version 1.8) click here.

Ratslinger gravatar imageRatslinger ( 2016-12-25 21:46:14 +0200 )edit

Just realized - with this added statement, the subsubform is no longer needed. A quite simple addition to the new statement (add A.QTYAMT to the topmost SELECT) and you can display it in a field. Good one for you to figure out. This also shows why planning ahead on what a final product should be can save some time and effort. Merry Christmas!

Ratslinger gravatar imageRatslinger ( 2016-12-25 22:20:16 +0200 )edit

Thanks for the Christmas gifts! The W3schools site looks like a great resource. I also watched some Kahn Academy videos last night that were pretty enlightening. A fascinating new world to explore! And Merry Christmas to you too!

DougKMiles gravatar imageDougKMiles ( 2016-12-26 08:52:54 +0200 )edit

answered 2016-12-25 20:09:50 +0200

updated 2016-12-25 20:12:42 +0200

You must create a query to do the calculation you need and then display the field with the result in the form.

If my answer helped you, vote it with ✔ and with ∧ (here on the left)

edit flag offensive delete link more


Thanks for the reply.

I get that. But I can't figure out how to create a query that uses the calculated field from another query to complete another calculation. How could I set that up?

DougKMiles gravatar imageDougKMiles ( 2016-12-25 20:38:41 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-12-25 17:20:47 +0200

Seen: 904 times

Last updated: Dec 25 '16