Base - How to display a calculated result from an input value to a form using formulas from table record?

Inkblotr · January 28, 2019, 3:30am

Hello - I’m struggling to explain, but trying my best.

My wife and I own a business, we sell art online through sites including Etsy, eBay, Amazon, etc. Each company charges us fees they subtract from the customer payment for each transaction. The formula is known and different for each company. The formulas have an effective date and do change, though rarely.

During data entry of a sale (using a form) we want to see what our profit is for each sale. I have part of the system working now but since the fees change, I need to incorporate a way of using different formulas based on the effective date compared to sale date.

I’m no expert at database design/programming but I think I’m correct to have a table of “Venues” (our term for the sites we use to sell our art) that would be linked to a secondary table where the fee formulas and effective dates would be values in records. An example of that linked table might be: “Venue_ID”, “Effective_Date” and “Fee_Formula”.

Data entry of a sale would include, date of sale, the item sold, amount of the sale (think customer only buys ONE item), and the Venue where the sale occurred. Before the sale record is saved (and fields cleared for the next sale to be entered) I need to provide on screen the profit of that sale. The calculated profit would not need to be written to the table. I have the cost to produce each piece in another linked table.

I know how to link tables to a form and I’m beginning to understand creating SQL queries. Help with how to use a formula in a linked table record to calculate and display on screen,is what I’m asking. An example of a formula would be “0.035 + 0.50” and would be used like this: “Total_Sale” x “0.035 + 0.50” - “Cost_to_Produce” = “Profit”. The “Profit” value would be displayed in a field on the form.

If I have not written a clear enough explanation, please post a comment of what is confusing and I will edit or reply to clear any confusion.

Thank you in advance!

[Edit 1/28/19 15:20 CST]
I realized last night I was asking for help with a complex problem.

I suspected Macros might be necessary to achieve my goal. I’m not averse to using Macros, I have a learning curve to negotiate in this respect.

I appreciate the answer @Ratslinger provided below. There is more information in the answer than I can assimilate and utilize quickly. So I’ve started by creating an example file where I am attempting to link the tables to display the relevant Venue_Fee record for each sample sale. It isn’t working and I’m not sure what I’m doing wrong.

Step 1 - I’m trying to simply display the first Venue_Fee record (ignoring date evaluation).
Step 2 - I will try to incorporate selecting the correct Venue_Fee record based on the Sale_Date.
To be clear, my intent of the Venue_Fee_Date represents the date when a Venue_Fee_Formula would take effect.

Click here for the File

Thank you!

[Edit 1/30/2019 21.20 CST]

With @Ratslinger help/assistance, I’ve been able to better understand joins & queries and I’ve been able to duplicate the FinalJoin query by using the Base Design View UI. Mine is called “qryFinalJoin_mk” and I used it when creating the “frmFinalJoin_mk” in the new file attached.

One thing though, using the query with joins, I can’t enter a new sales record and I don’t know why…?

Many thanks again to @Ratslinger!

Updated file

Ratslinger · January 28, 2019, 5:11am

Hello,

Excuse me for jumping around a bit, but it may help.

The first concern is your statement:

Before the sale record is saved (and fields cleared for the next sale to be entered) I need to provide on screen the profit of that sale.

So if you consider this carefully, the keyword is “before”. If it is before, then there is no record in the DB. Without a record in the DB you cannot run SQL on it. In order to do this, a macro, specifically for this need, must be written.

Now as for using linked tables in a query,the probable method is to use a JOIN. For more information on this and its’ different types, see this page → SQL Joins. In your case I would suspect a Left Join to be used. There are many posts here in the forum concerning joins. A search on Left Join should get many results with many samples. You can have multiple joins in a select as can be seen in this post → HSQLDB Code.

Now calculations in SQL are simple provided you have access to the data (fields). So if you want Total_Sale * 0.035 then:

Select Total_Sale * 0.035 As MyResult From MyTable

and be careful on how the calculation is done by grouping:

Select (Total_Sale * 0.035) + .50 - Cost_To_Produce As Profit From MyTable

In my answer on this post → Base Form data uneditable there is a sample - UpdateCalculatedFields.odb. This sample has two forms. One form uses a query to display calculated information (this available only after the record is saved) and another form which uses macros to display the calculated information as the record is being entered (before record is saved).

Edit 2019-01-28:

Have taken this one step further, this being a reasonable SQL statement to work with.

Another note here. There is simply not enough planning as happens to many who try implementation before design. At this point, especially since this is not my project and I don’t have the full scope, I can’t say as to whether these fixes will suffice.

Have also now found a data problem. There are sales with dates earlier than any effective Fees dates. Incorrect data causes lost time in providing a solution to a question due to searching as to where the problem lies.

Have modified the Fees table to coincide with my comments and have created a query named FinalJoin. There are a couple of other queries I left in the sample to give you an idea of the steps taken. This FinalJoin query can be used as the basis for a sub form linked with the Sale_ID. It has a calculated Net figure using the data available.

Sample -------- SQLcalculation.odb

Edit 2019-01-30:

Just did a Q&D on your form frmFinalJoin_mk. Can now enter new records.

Sample ------- SQLcalcInk.odb

Ratslinger · January 28, 2019, 10:06pm

Unfortunately there is no “quick” way to get all the knowledge needed unless you have someone else do it for you. If so, in the end you simply have a crutch since you have no knowledge to modify, add, or remove functionality.

Anyone doing this needs to take one step at a time. The first step to create the end result is to obtain the needed data - in this case the SQL (or query). As stated in the answer, Joins are needed when dealing with data as you want. So here is a first step, getting the sales and Venue together:

SELECT "tblSales"."Sale_ID",
       "tblSales"."Sale_Date",
       "tblSales"."Sale_Item_ID",
       "tblSales"."Sale_Amount",
       "tblSales"."Sale_Venue_ID",
       "Venue_Name"
FROM "tblSales"
Left Join (select "Venue_Name", "Venue_ID" from "tblVenues") ON "Sale_Venue_ID" = "Venue_ID"

Now this simply JOINS the venue name from its table to the sale based upon (ON) the ID in one record = to the ID in another record in another table

Ratslinger · January 28, 2019, 10:14pm

More is then added by further joins & selects until all data needed from the form is available. Now your situation is further complicated by the date factor and requires a selection of the needed record bases upon effective date. Each item must be taken one step at a time. What you end up with seems to be a complex statement but in reality is just a bunch of connected simple statements.

Have also seen a further problem in your “Fee” field. This is a text field with multiple figures and won’t work in calculations. It most likely should be two fields - % of Sale & Flat Fee. Then those values would be usable in a calculation.

Many things to analyze.

Ratslinger · January 28, 2019, 10:33pm

Another consideration - the Venue Fees table. First not a fan of multiple fields for a key. Simply use an auto increment integer for the key. Keep the venue ID, the date as Effective Date and add an Expiration Date (a far future date becomes no current expiration). This expiration date will save countless headaches in the SQL.

Inkblotr · January 29, 2019, 7:44pm

@Ratslinger - Wow, you’ve given me even more information to assimilate. Thank you! I’m trying to provide all the information that I can so you can be most effective helping me. Please ask if you have questions.

Data anomaly you mentioned: We started our business in January, 2016. In 2018, Etsy changed their fee formula and made it effective on July 1. I put example sales records in tblSales, before and after the effective date of the fee change to test (when I can get to that step) the system will be using the correct formula.
I was hoping that the character strings in “Venue_Fee_Formula” could somehow be concatenated into the Profit calculation - maybe that’s unrealistic and I’ll have to use separate fields - I understand.
I’ve successfully pushed back on my wife’s insistence that the Profit of the sale must be displayed on screen BEFORE the sale record is saved to the table. Whew!

I’m still working through the other information you’ve provided.

Inkblotr · January 29, 2019, 7:49pm

Using the simple example file, I’ve gotten the Joins working (cool!) and I’ve also gotten a calculated field “Profit” to work and display in a form (though i’m having a problem with using Aliases to calculate the Profit field - no biggie - full field names work).

Ratslinger · January 29, 2019, 7:58pm

@Inkblotr The only new information provided was what I considered problems with your set-up. The actual generation of the SQL information was in the original answer - Left Joins.

You can use the Fee Formula as you had it but it will require you to extract the separate pieces to be usable in SQL. Doing so will require even more SQL changes and further complicate what you have. To me separating the data is the better method.

It is best to avoid macros whenever possible. If you think SQL is difficult, you will find macros many times more difficult. There is a steep learning curve to obtain all the information you would need to accomplish some of what you want. Best to learn SQL as it will eliminate the need for macros in many cases.

Inkblotr · January 30, 2019, 3:43am

@Ratslinger I’m trying to wrap my head around what date information the tblVenueFees records need to contain that would make querying easy. Situation: there will be only one record for each (non-Etsy) Venue with a date of 1-1-2016 (the date our business started) with an appropriate Venue_Fee_Formula. The Venue Etsy, will have (as of today) two records because of their rate change that took effect 7-1-2018. Question: Would it be easier if the records in tblVenueFees had both a “Start Date” and an “End Date” so the values could be compared to the Sale_Date easily? If I incorporate this change, I’m thinking all currently active records for all Venues would have NULL as the “End Date”. For Etsy the first record would have a “Start Date” of 1-1-2016 and an end date of 6-30-2018. The second Etsy record would have a “Start Date” of 7-1-2018 and a NULL “End Date”. I trust your judgement, what do you suggest?

Ratslinger · January 30, 2019, 4:57am

Sorry but I am a bit surprised at this last comment. I had actually mentioned all this in my third comment above and, as my edited answer states, incorporated this into my provided sample. Was this looked at? Spent considerable time in preparing that since I used the date scenario to give you all needed SQL and progressive left join samples.

For very early dates you could use a pre-business date and for unknown future dates use as I did - 2025 or similar. You can use something like 12/31/2099 but be sure to enter 2099 not just 99 since it will think it is 1999. Be sure to test what you use. Do not use NULL (unless you want abnormally long ‘Where’ conditions). Null is not equal to or unequal to anything. Fees and percentages can be zero where appropriate. Again stay away from NULL.

Inkblotr · January 30, 2019, 4:15pm

In your FinalJoin query, I do not understand the use of “Sale_ID2”. Would you please explain?

Inkblotr · January 30, 2019, 5:17pm

In the FinalJoin query, I also do not understand in the last LEFT JOIN, the significance of no commas between the field names {“Venue_ID” “Venue_Fee_ID”} and {“Sale_ID” “Sale_ID2”}. I’ve learned by inserting commas, prevents the query from running…

Thank you.

Ratslinger · January 30, 2019, 7:12pm

These are simply aliases. The AS is not necessary. Using them makes it easier to qualify in Where & On conditions. ‘Venue_ID’ and ‘Sale_ID’ already existed in other selects/joins.

Sale_ID2 (alias) is so this ‘Fee’ portion could be successfully joined to the proper original ‘tblSales’.

Ratslinger · January 31, 2019, 3:42am

It should be somewhat obvious as to why you can’t enter new sales records. The data is not what your table is expecting. As stated in my edited answer, this query information should be used as a sub form. Just link up based upon the sales ID. If you don’t understand this linking, please ask as a new question as this one is growing off topic.

Inkblotr · January 31, 2019, 3:58am

Okay, Thanks.