Roalddeswart
New member
- Local time
- Today, 08:24
- Joined
- Feb 6, 2017
- Messages
- 4
Hello,
I am currently working on a database that creates a transport data sheet.
We are adding data about all deliveries send to customers every day.
It also contains a transport carrier, which the delivery has shipped with.
What I would like to create in access is a query where I can see the real transport-costs per day to the customer.
Therefore, I created a unique key to have the weight summarized to a specific customer.
The key is built with customer-id, postal code, date, transport carrier.
an example: 1234-9999AA-42771-D
There are different carriers (now seven, but it could be more) to do the transport, and we have to find the correct rate, based on the weight and the carrier. (see attachment)
I would like to have the correct rate from the correct carrier based on the unique key.
So when we have the key: 1234-9999AA-42771-D and it has got a weight from 13,7kg in the crosstab query, the rate € 4,40 has to be found.
Now I created a nested formula in excel, but with a large datasheet it takes ages before the correct rate has been found (even with index/match.
Hope you have a nice solution to this calculation in access with a query or VBA.
BR, Roald
I am currently working on a database that creates a transport data sheet.
We are adding data about all deliveries send to customers every day.
It also contains a transport carrier, which the delivery has shipped with.
What I would like to create in access is a query where I can see the real transport-costs per day to the customer.
Therefore, I created a unique key to have the weight summarized to a specific customer.
The key is built with customer-id, postal code, date, transport carrier.
an example: 1234-9999AA-42771-D
There are different carriers (now seven, but it could be more) to do the transport, and we have to find the correct rate, based on the weight and the carrier. (see attachment)
I would like to have the correct rate from the correct carrier based on the unique key.
So when we have the key: 1234-9999AA-42771-D and it has got a weight from 13,7kg in the crosstab query, the rate € 4,40 has to be found.
Now I created a nested formula in excel, but with a large datasheet it takes ages before the correct rate has been found (even with index/match.
Hope you have a nice solution to this calculation in access with a query or VBA.
BR, Roald