Base - calculations from calculated field

[DougKMiles]

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.

DM

[Ratslinger]

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

[DougKMiles]

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?

[Ratslinger]

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]

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!

[DougKMiles]

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!

[charlie.it]

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)

[DougKMiles]

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?