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.
[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!