Getting Transport Rate based on date ranges due to fuel price floats

[calvinyoo]

Hi ALL,

Happy New Year!
I have tried search through some forums and but still not able to get desire results.

Table Customer Agreed Transport Rate
Customer > Eff Date > Route > Rate
A > 1 June 2014 > AAA > MYR 99
A > 15 Aug 2014 > AAA > MYR 88
A > 1 June 2014 > BBB > MYR 77
A > 1 Sept 2014 > BBB > MYR 66
B > 5 June 2014 > CCC > MYR 88
B > 20 Oct 2014 > DDD > MYR 100
C > 1 Oct 2014 > EEE > MYR 222

My concerns:
If there is order from Customer A for Route AAA loading on 2 July 2014, I can easy to get answer "MYR 99".
How if I wish to make Route as primary key? (as I understand, Primary key must be unique and no repeat, pls correct me if wrong)

Thanks.

 

[calvinyoo]

Due to lack of solution, now I am doing with this way:
Route > Customer > "1 June 2014" > "1 Sept 2014"
AAA > A > MYR 88 > MYR 77
BBB > C > MYR 99 > MYR 98

Hence, if there is too many different effective date, I could not solve these by IIF.

 

[jdraw]

Step back and tell us in as simple English as possible, WHAT are you trying to do? There may be options, but readers have to understand what you re doing before they can advise options for how to do it.

 

[calvinyoo]

Thanks JDraw,

Table 2 DATA ENTRY
Trip No > Truck No > Loading Date > Customer > Route > Item > QTY
1 > XYZ 123 > 2 July 2014 > A > AAA > Concrete > 100 unit
2 > ABC 123 > 5 July 2014 > B > CCC > Canned food > 200 unit


I need advice on QUERY, how to get RATE from above table where DATE is 2 July 2014 and CUSTOMER is A and ROUTE is AAA (Answer MYR 99)

Hope it could be understanding. Sorry for confusing.

 

[jdraw]

Let's start with what tables do you have? What fields in each table? Where is the Rate field? Does Date really mean LoadingDate?

If you aren't clear on what you're doing, you can not expect readers to understand.

Seems you have something along these lines.


Customers
Orders
Items
Quantity
Routes
Trips
Trucks
Rates.

Can you tell us what these are and how these things fit together?

My guess, and please adjust/change as necessary:

You have Customers who Order 1 or more Items in various Quantities. You Load the Quantity of Item on a LoadingDate onto a Truck. The Truck is scheduled to make a Delivery Trip using Route X. The Rate for the Trip varies depending on the Route and Customer and LoadingDate.

 

[calvinyoo]

Let's start with what tables do you have?
>> 2 Tables: Customer Agreed Transport Rate , and, Data Entry

What fields in each table?
Customer Agreed Transport Rate:
- Route
- Customer
- Effective Date
- Rate

Data Entry:
- Trip D/O No
- Truck No
- Loading Date
- Customer
- Route
- Item
- Quantity

Where is the Rate field?
This 'Rate' refer to Table Customer Agreed Transport Rate

Does Date really mean LoadingDate?
Yes, transport charge is depend on Effective Date and Loading Date.

Thanks again.

 

[calvinyoo]

Let's start with what tables do you have?
>> 2 Tables: Customer Agreed Transport Rate , and, Data Entry

What fields in each table?
Customer Agreed Transport Rate:
- Route
- Customer
- Effective Date
- Rate

.

Hi, just add more details. for Table of Customer Agreed Transport Rate, four fields are might be repeated. Example, Route (from A to B) Customer A, Rate is USD 1 with Effective Date 1/1/2015. While, Rate is USD 2 with Effective Date 2 February 2015. Thanks.

 

[jdraw]

This is untested. And I have removed embedded spaces from your tables and field names. DataEntry seems a very poor choice of table name. I could see a DataEntry form, but not a table.
Also, I think you have a variety of hidden entities in your tables.

Based on what you have shown, I think this would get you the proper Rate, but it is untested.

SELECT Rate from 
CustomerAgreedTransportRate INNER JOIN DATAEntry ON
CustomerAgreedTransportRate.Route = DATAEntry.Route AND
CustomerAgreedTransportRate.Customer = DATAEntry.Customer
WHERE DATAEntry.LoadingDate >= 
(
SELECT MIN(CR.EffectiveDate) from 
CustomerAgreedTransportRate CR INNER JOIN DATAEntry  DE ON
CR.Route = DE.Route AND
CR.Customer = DE.Customer
WHERE
CR.EffectiveDate <=DE.LoadingDate
)

 

[mdubravic]

Hi jdraw I have similar issue but little more complex than that and I posted it here access-programmers.co.uk/forums/showthread.php?t=273070

 

[calvinyoo]

Hi Jdraw,

Thanks for your help. But I still need further helps:
We put s simple dates>> Delivery Date is 5 Jan 2015, while A effective date is 1 Jan 2015 and B effective date is 1 Feb 2015.

Print Screen link: docs.google.com/document/d/17mvbv5IKCXVy5zbdm9UCDCtvEM7DUkFD0iE9KJDLYfQ/edit?usp=sharing

When I try use EffStartDate and EffEndDate, (A EffEndDate is 31 Jan 2015 , B EffEndDate is 31 Dec 2099) it was solved and get my answer, but it was messy jobs when I need to update/amend OLD EffEndDate when there is new Effective Date.

Then, but I was NOT able get answer when I tried total MAX (<=[DeliveryDate]) in query of EffectiveDate, I get wrong answer : it show all older effective date including the correct effective date.
If I put MAX (<=[DeliveryDate]) in criteria , I get error msg.

So, now I think I have two options:
1. thinking to have auto/easy way to update old effective date's END DATE
2. correction on effective date query without EffEndDate way.

Thanks again ... and appreciate your great help!

 

[calvinyoo]

Hi ALL,

Even though I couldn't get the answer from here, but I managed get help from other forum.
Just share with you all what I get:
For Effective Rate by Period, should have EffectiveStartDate and EffectiveEndDate
Then, in Query, the criteria need show up "<=[TransactionDate]" or ">=[TransactionDate] will trigger the Rate need.
Thanks.