Query - Multiply 2 fields from the same table to get an amount

WJoel · July 8, 2024, 8:17pm

I’m creating a query and trying to multiply 2 fields from the same table to get an amount (IE 100x.10=10). In design view (screen shot attached) I can’t figure out how to make it happen. But it does have the option to switch to sql view. When I switch to sql view it gives me:

SELECT “Table_Companies”.“Company Name”, “Table_Companies”.“USDOT #”, “Table_Companies”.“Type”, “Table_Companies”.“Status”, “Table_Companies”.“City”, “Table_Companies”.“State”, “Table_Companies”.“Zip Code”, “Table_Sales_2”.“Product”, “Table_Sales_2”.“Date Bound”, “Table_Sales_2”.“Commissionable Premium”, “Table_Sales_2”.“Agency Commission”
FROM “Table_Sales_2”, “Table_Companies”
WHERE “Table_Sales_2”.“Company ID” = “Table_Companies”.“Company ID”

I’m trying to add a third field in the query with the results of multiplying these two fields:

“Table_Sales_2”.“Commissionable Premium”, “Table_Sales_2”.“Agency Commission”

For clarification:

“Table_Sales_2”.“Commissionable Premium” - Is the gross premium paid to the agency

“Table_Sales_2”.“Agency Commission” - Is the percentage of the gross premium paid to the agency

I’m wanting to add a field with the result of the multiplication of the above two tables such as:

“Table_Sales_2”.“Agency Commission Due”

Any help you can provide (either in design view or sql view) would be greatly appreciated!

Villeroy · July 8, 2024, 8:34pm

SELECT "Table_Companies"."Company Name", "Table_Companies"."USDOT #", "Table_Companies"."Type", "Table_Companies"."Status", "Table_Companies"."City", "Table_Companies"."State", "Table_Companies"."Zip Code", "Table_Sales_2"."Product", "Table_Sales_2"."Date Bound", "Table_Sales_2"."Commissionable Premium", "Table_Sales_2"."Agency Commission",
"Table_Sales_2"."Commissionable Premium" * "Table_Sales_2"."Agency Commission" AS "Agency Commission Due"
FROM "Table_Sales_2", "Table_Companies"
WHERE "Table_Sales_2"."Company ID" = "Table_Companies"."Company ID"

WJoel · July 8, 2024, 8:53pm

Thank you Villeroy. I seem to have an error in my table. My “Table_Sales_2”.“Agency Commission” field will not hold a value. I’ve set the field type to both integer and number but no success. Any thoughts?

nicholas59 · July 8, 2024, 9:56pm

Would you be so kind to make things simplier and upload the file?
Obviously if the data are confidential, you may empty the tables…

Villeroy · July 8, 2024, 10:32pm

t107698.odb (13.0 KB)

WJoel · July 9, 2024, 1:18pm

Thank you Villeroy! Using your example and creating a new Sales_Table. I greatly appreciate the help!

WJoel · July 9, 2024, 5:03pm

Hmmm… I have the same thing but the table is still not holding the data so the query will work. I’m going to strip out the data and post it so you or someone can show me what I’m doing wrong. Thank you very much!
Without_Data.odb (5.2 KB)

nicholas59 · July 9, 2024, 6:33pm

Hello WJoel,

As long as I remember, if you have a field with a VALUE in your table, then you have enough. You could indeed obtain fractions of that VALUE in your query, e.g. SELECT MyTable.myvalue, MyTable.myvalue x10/100, MyTable.myvalue x 5/100 FROM MyTable;

In addition, I took the liberty to establish PK-FK relationship and provide a query as example.

Do hope you would agree…
Without_Data - First Proposal.odb (6.6 KB)

WJoel · July 9, 2024, 7:49pm

Thank you nicholas59. I can indeed make it work that way. My challenge is when the commission changes. So for one sale it may be .10 (10%) while for the next sale it may be .12 (12%). I’m trying to have a solution where I don’t need to change the query amount which effects all entries. So essentially I’m trying to make a query that works like a spreadsheet such as the attached. Change only the “Commissionable Premium” value and optionally change the “Agency Commission” and/or the “Producer Commission” and both the “Agency Commission Due” and “Producer Commission Due” automatically update. Am I doing it wrong with queries? Is there a way to accomplish it in the table instead?
Table_Sales_2.1.ods (12.3 KB)

nicholas59 · July 10, 2024, 5:06am

Have a good Day Wjoel,

I may understand your concern. Do apologize if my schemes are a bit too square.

If I could arrange for a solution, that would be a field that contains parametric values (i.e. 10, 12, 15…) and multiply the Currency field with it…) but then of course I would have to specify criterion with a WHERE clause.

On the other hand, commercially speaking, those granted fees would have though to be confirmed by a sales contract. Don’t you agree?

Thank you for your kindness…

cpb · July 10, 2024, 5:57am

@WJoel,
I copied your table “Table_Sales_2” to “Table_Sales_2_NEW”.
why?
to remove spaces and rename the fields “Agency Commission” and “Producer Commission” to the more descriptive “AgencyPcent” and “ProducerPcent” respectively.

I have included 2 queries “qQuery1_sql” saved in direct mode and the identical “qQuery1” saved in design view.
the sql is yours (“Query_Sales_2”) with alias’s used to reference the tables and 1 line of code added.
Without_Data_Plus.odb (5.8 KB)

WJoel · July 10, 2024, 4:51pm

Thank you cpb! I think I understand and it works Thank you very much!

WJoel · July 24, 2024, 4:03pm

cpb,

I have tried multiple times to duplicate your example. I still keep having issues. My table won’t retain entries in the “AgenctPcnt” and “ProducerPcnt” fields. My query seems to have the same code but doesn’t look like yours. I stripped out all my customers data and it is attached. Can you explain to me what I am doing wrong please?

Sales_Tracker.odb (5.2 KB)

Villeroy · July 24, 2024, 6:01pm

What’s wrong with your query? Any multiplication with 0 gives 0, so the calculation results are correct.
Apart from that, the type of the “Premium” column DECIMAL(100,0) makes no sense to me.

WJoel · July 24, 2024, 6:28pm

I understand a “0” entry will produce a “0” result. The problem is my table will not retain any entry in the “AgenctPcnt” and “ProducerPcnt” fields.

As for my query, I believe mine is correct but I do not write code. cpb’s example query is:

SELECT
“tc”.“Company Name”,
“tc”.“USDOT #”,
“tc”.“Type”,
“tc”.“Status”,
“tc”.“City”,
“tc”.“State”,
“tc”.“Zip Code”,
“ts”.“Product”,
“ts”.“DateBound”,
“ts”.“CommissionablePremium”,
“ts”.“AgencyPcent”,
“ts”.“CommissionablePremium” * “ts”.“AgencyPcent” / 100 as “AgencyCommission”,
“ts”.“ProducerPcent”,
“ts”.“CommissionablePremium” * “ts”.“AgencyPcent” / 100 * (“ts”.“ProducerPcent” / 100.00) as “ProducerCommission”,
“ts”.“DateCommissionReceived”
FROM
“Table_Sales_2_NEW” “ts”,
“Table_Companies” “tc”
WHERE
“ts”.“CompanyID” = “tc”.“Company ID”<

and it produces the desired results because his table retains changes to the “AgencyPcent” and “ProducerPcent” table fields. Mine does not. Here is my query in sql view:

SELECT
“Table_Companies”.“CoName”,
“Table_Sales”.“Insurer”,
“Table_Sales”.“Product”,
“Table_Sales”.“DateBnd”,
“Table_Sales”.“Premium”,
“Table_Sales”.“AgencyPcnt”,
“Table_Sales”.“Premium” * “Table_Sales”.“AgencyPcnt” / 100 AS “AgencyCommission”,
“Table_Sales”.“ProducerPcnt”,
“Table_Sales”.“Premium” * “Table_Sales”.“AgencyPcnt” / 100 * ( “Table_Sales”.“ProducerPcnt” / 100.00 ) AS “ProducerCommission”,
“Table_Sales”.“DateRcvd”
FROM
“Table_Sales”,
“Table_Companies”
WHERE
“Table_Sales”.“CoID” = “Table_Companies”.“CoID”<

WJoel · July 24, 2024, 8:25pm

I’m sorry if I offended you. I’m not a developer. I don’t write code. I’m just a novice just trying to learn something new for my own use. Next time you want to be rude just flip the bird instead of being arrogant and condescending. The latter doesn’t look good on you.

nicholas59 · July 24, 2024, 8:34pm

Ok! Have a lot of fun then…

cpb · July 25, 2024, 12:19pm

@WJoel,
your query works exactly as expected.
it returns 7 table field values plus 2 calculated fields “AgencyCommission” and “ProducerCommission”.
any values which can be calculated should not be stored in tables, that’s a rule of database normalisation.

so the table we’re talking about is “Table_Sales”.
after opening the table I am able to edit field values and insert new records without issue, all/any changes are retained.
.
maybe you use a form when inputting data (as you should) and the forms properties require amendment or possibly you’re misunderstanding something.
.
I think that a step by step guide will be required for us to understand the problem.
.
as highlighted by Villeroy a more realistic column type for “Premium” would be DECIMAL(8,2) i.e. max value of 999999.99.

WJoel · July 25, 2024, 1:15pm

Thank you cpb. Yes, my Table_Sales is the one that will not correctly hold entries. While I’ve continued to work on that table, I also notice that the “Premium” field will not hold the correct value either. When I enter 335.50 it gets automatically rounded down to 335.00. I have tried all numeric field options (number, integer, tiny integer, decimal, etc.) but all have the same issue. If I change those fields to text (VARCHAR) it keeps the entry. But of course then your formula for the “Agency Commission” and “Producer Commission” fields doesn’t work. I have not created a form yet because I was concerned it wouldn’t work correctly until I had the table functioning properly.

cpb · July 25, 2024, 2:23pm

@WJoel,
ref table: “Table_Sales”.
I have made the field type of “Premium” DECIMAL(8.2).
I have changed the field type of “AgencyPcnt” and “ProducerPcent” from integer to DECIMAL(4,2) which is more pragmatic.
.
I can edit the table without issue can you?
Sales_Tracker_amended_1.odb (6.1 KB)