Table to replace conditional statements

[Leesha]

Hi and Happy New Year!

I have a user who does royalty billing on [Finalsales]. The billing is based a variety of rules and conditions that took me forever to build into a query. It's been running great for the past 3 years, however now they want to change the amounts being billed on an annual basis. The thought of having to try to update all the conditional statements that are in the query, esp. on a yearly basis has me insane. What I'd like to do is set up a table where I can enter the rules and then have a query with [finalsales] that splits out the fees based on the rules in the table. I've been at this for two days and nothing I've done works. I'm including a database that has two tables. tblBillingRateTable has the rules for each tier and tblGenerateInvoice-temp has a few stores with different [finalsales]. There are 5 possible tiers per store. The query needs to return the dollar value for each Tier as well as the % charge for each Tier. My goal is to only have to update the dollar amounts and percent charge each year vs having to rewrite the conditional statements. I set up tblBillingRateTable just so I had a visual of the definitions for each of the conditions.

Thanks,
Leesha

[HansV]

In the attached version, I have created a table tblTiers with three fields:

x441.png

Limit (currency, primary key): this is the cumulative sales amount for each tier.
Cumulative (currency): this is the cumulative royalties amount for each tier. I calculated these amounts "by hand".
Percentage (number, double): the percentage for the remainder of the sales amount.

Next, I created a totals query based on tblGenerateInvoice-temp and tblTiers. It calculates the highest Limit amount less than the FinalSales amount for each ID.

x442.png

I saved it as qryStep1.

Finally, I created a query that uses qryStep1 to link tblGenerateInvoice-temp and tblTiers and to calculate the total royalties:
Subtract MaxLimit from FinalSales to get the remainder of the amount.
Multiply with the appropriate Percentage, and add to the Cumulative amount for MaxLimit.

x443.png

This query is not updateable because it is based (among others) on a totals query.

Here is the database:

Eileens.zip

[Leesha]

Hi Hans,
All I can say is WOW. I'm always so amazed with what you do with queries. I really appreciate the info on the process as well. My problem is that this store wants to see each Tier broken out vs a cummulative one. Right now I'm using a query that calculates the total amount of money in each tier, and then a second query that calculates the percentage owned. They work, however there are sooooooooooo many conditional statements in each its a nightmare to update them which is why I was hoping there was a way to do it with a definition table. Your approach would be perfect if I didn't have to show the individual tiers. I'm attaching a spreadsheet that show ther two queries that I use exported to excel.

Thanks,
Leesha

[Leesha]

Hi Hans!

You set me on the right track with your example. I spent the afternoon trying to "think like Hans" and I got it!!! I'm posting back what I came up with. I'm tickled.

Thanks so much,
Leesha

[HansV]

Good for you!

[Leesha]

Thank you! I used a combination of all the things you been teaching me all these years, along with your approach of tables/queries tht contained the rules NOT linked to anything in the query. Prevoious to seeing your appraoch, I had been trying to come up with a query that linked the tables together and nothing was working.

Happy New Year!
Leesha