Multiplying Two Fields from Different Tables Based on a Criteria

Question

I have one table that is a log of orders, and then I have another table that shows the applicable commission rate depending on dates (for example - For an order that occurs on 9/14/2020, the rate would be 40%, an order that happens on 1/14/2021 would be 50%, and anything after 2/26/2021 is 60% - see below).  I want to be able to create a query that multiples each order by the applicable commission rate to show how much I will make off a particular order.  I am new to Access, but could easily do this in Excel, but I want to keep it all in Access rather than exporting the data to Excel.  With that in mind, know that I am a beginner with limited knowledge in Access, so keep it simple and explain things clearly.

Here are screenshots of my tables:

Log Table

Commission Table

Answer 1

For ease of use, I'd include the start date and end date of each phase in the Commission table:

You can then use a query like this:

SELECT [Log].[ID], [Log].[Order Accepted], [Log].[Amount], [Log].[Amount]*[Commission].[Commission] AS [CommissionAmount]

FROM [Log], [Commission]

WHERE [Log].[Order Accepted] Between [Commission].[StartDate] And [Commission].[EndDate]

(substitute the actual table names and field names)

Answer 2

Perfect.  Thank you!