Variable rate based upon a month

[Lluewhyn]

I have a feeling this is something obvious I'm forgetting, but I want to compare a sales ticket for a given date (from a table) and compare that to the price schedule for that same time period (from a different table).

I have a prompt in the criteria for the ticket date Between [Enter Start Date MM/DD/YYYY]And [Enter End Date MM/DD/YYYY].

So far, so good. I also was able to successfully use the fee schedule to charge the appropriate rate for a given customer * the quantity for that ticket:


Color Fee: IIf([Receipts]![Color#]<27,[Receipts]![Total]*[Fee Schedule]![Color Rate],0)

The problem is setting up different effective date ranges so that it pulls the correct rate for that customer *and* time period. I set up multiple records for a sample customer with an Effective Date and End Date (blank on the most current one). I can get it to reference the most current one if I use Is Null in the criteria for the Fee Schedule End Date, but that's not exactly what I want. That's essentially the same as only having the current rate in the table, and I might want to be able to calculate things historically.

Obviously, a solution would be to just have the fees already calculated and included with the records imported into Access, but thought it might be nice to have a more centralized storehouse of fees over time.

[nrgins]

It's really hard to follow what you're saying here. Perhaps if you shared information about which tables you're using, and what the fields are, and other information.

But, in general, if you set up date ranges, then you can use Between in your selection critera:

[SomeValue] Between [Effective Date] And [End Date]

[Lluewhyn]

It's really hard to follow what you're saying here. Perhaps if you shared information about which tables you're using, and what the fields are, and other information.

But, in general, if you set up date ranges, then you can use Between in your selection critera:

[SomeValue] Between [Effective Date] And [End Date]

I have a table that lists sales of certain items, along with the quantities and dates. For a variety of reasons, there are no financial calculations or dollar figures in this table. I have another table that lists the fee schedule for each customer, as they have different rates. I'm trying not to have a single rate for each customer, as that could change history if I were to rerun a query. So, I'm wanting to not only have a record for each customer, but to also have a start date and end date for that particular date range, in the case that rates were to change.

So, I am wanting to run a query that would calculate the fees for a given record or set of records in time (say I wanted to look at March) by comparing the sales volume data from one table vs. the fee rate appropriate for that customer for that given period of time.

For a mock example, I run a query that shows that customer A delivered us 523 widgets in March. For that particular customer, we charged a cleaning fee of $.05/widget in March, and a polishing fee of $.10/widget during that same time period. We then returned the widgets to the customer, and billed them for services rendered. The following month, let's say that cleaning fees went up $.01 and polishing fees went down $.02. That is why I had a table for widgets rendered to us for various customers (and what service each shipment required), but a separate table for fees with effective date ranges. Right now, we've been handling it in Excel but I'm trying to move it to Access.

Does this make more sense?

[nrgins]

Yes, that makes more sense.

To get the fee for each item, you would create a query with the table of items (say, called "Items") and the table of fees (say, called "Fees"). Fees has, say, Amt, StartDate, and EndDate. And Items has ItemID, ItemDate, and other fields.

In your query, add both tables, but don't join them.

Add the fields you want from each table, including the ItemID from Items; Amt from the Fees table; and the ItemDate field (ItemDate field can have Show unchecked if not needed).

In the Criteria row below ItemDate, put:

Between [Fees].[StartDate] And [Fees].[EndDate]

That will give you a list of items, along with the appropriate fee for each one.

Note that if an item date falls into a range that is not included in the Fees tables, then that item will fall out of the query. The query will only show items that have a matching fee.

You say you have more than one fee, but you don't say whether they're contained in the same table or in different tables.

If in the same table, then just add the other field.

If in different tables, then you'll need a different query for each fee table.

Then, to compile your data, once you've gotten your fee queries done, create a new query; add the Items table to it; add your fee queries to it; join the Items table to each fee query on ItemID to ItemID, using an outer join for each join (all items from Items, and only matching items from the queries). Then add the fields you need from the table and queries.

If the fees in the fee tables don't have unique names, then you can give them aliases in the fee queries by placing an alias before the field name in the query, as in:

alias: fieldname

Hope that helps!

[Lluewhyn]

I tried this, but it doesn't seem to be working. I received an error by removing the joins. "The SQL statement could not be executed because it contains ambiguous outer joins...."

I then rejoined the tables. For reference, I have three tables included: Receipts (your Items table above), Customers, and Fee Schedules. The Customers table is just linking the Fee Schedule to the Receipts table via the Customer ID field on the fee schedule compared to the Customer (Name) field on the Receipts table. I suppose I could add Customer ID to the Receipts table and skip the Customers table but that's not how the information had been coming in. The Customers table has Customer ID as the primary key.

Once I rejoined the query, I ran it, entering the prompts for Effective Date and End Date. This time, the query worked, but is only using the most recent date range.

For testing purposes, I had one customer have two schedules, one with an Effective Date of 5/01/2016 and End Date of 5/31/2016, and the second had an Effective Date of 6/01 with a Null for End Date. All other customers have an Effective Date of 05/01/2016 and no End Date. Receipts range from 5/22/2016 to 6/29/2016.

When running the query, it is always using the schedule for the customer that doesn't have an end-date. The first customer used the June schedule for both May and June Receipts, and the others used the May schedule for all receipts (as intended, since it is not end-dated). I went ahead and changed the effective date on some fee schedules to 07/01/2016, which should have resulted in a $0.00 or an error since there shouldn't have been a fee schedule in the date range of the receipts, but the query still used that fee schedule effective dated 07/01, presumably because it didn't have an End Date.

[Lluewhyn]

I tried this, but it doesn't seem to be working. I received an error by removing the joins. "The SQL statement could not be executed because it contains ambiguous outer joins...."

I then rejoined the tables. For reference, I have three tables included: Receipts (your Items table above), Customers, and Fee Schedules. The Customers table is just linking the Fee Schedule to the Receipts table via the Customer ID field on the fee schedule compared to the Customer (Name) field on the Receipts table. I suppose I could add Customer ID to the Receipts table and skip the Customers table but that's not how the information had been coming in. The Customers table has Customer ID as the primary key.

Once I rejoined the query, I ran it, entering the prompts for Effective Date and End Date. It's actually creating a calculation for each fee schedule, regardless of the Effective and End Dates.

For testing purposes, I had one customer have two schedules, one with an Effective Date of 5/01/2016 and End Date of 5/31/2016, and the second had an Effective Date of 6/01 with a Null for End Date. All other customers have an Effective Date of 05/01/2016 and no End Date. Receipts range from 5/22/2016 to 6/29/2016. I have included a snapshot of the table below. So, the query is calculating two results for Customer #6, one for each Fee Schedule, and one result each for customers 1 and 2.



When running the query, it is always using the schedule for the customer that doesn't have an end-date. Customer #6 used the June schedule for both May and June Receipts, and the others used the May schedule for all receipts (as intended, since it is not end-dated). I went ahead and changed the effective date on some fee schedules to 07/01/2016, which should have resulted in a $0.00 or an error since there shouldn't have been a fee schedule in the date range of the receipts, but the query still used that fee schedule effective dated 07/01, presumably because it didn't have an End Date.

[Lluewhyn]

Ok, I may have figured this out, but it's somewhat opposite of what you said. I leave the Item Date alone (apart from prompting the User for a beginning and ending date using Between function), but then I used the criteria of the Fee Schedule to reference the Item table dates.
Fee Schedule Effective Date: "< [Items].[ItemDate]"
Fee Schedule End Date: "> [Items].[ItemDate]"

To do this, I had to fill in the End Date of the records to 12/31/9999 instead of leaving it blank.