find value in a table based on value from a crosstab query

[Roalddeswart]

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

 

[liddlem]

I see that you are using the suffix 'D' to identify something (I presume the carrier).
Instead of creating a xtab can you not access a standard table and then
Select * from tableName where WeightValue = WeightField and CarrierCode = CarrierField?

 

[Roalddeswart]

Hi Liddlem,

Yes you're correct, the suffix "D" is the name of the carrier.
I have to create a crosstab query, to combine keys that are identical ( it's possible to ship multiple orders to the same customer on the same dae with the same carrier) Therefore I need the cross-tab to have a "consolidated" weight.
With the consolidated weight I can calculate the correct tariff for the carrier.
So I cannot use a standard table as the tariff is then not correct.

BR, Roald

 

[liddlem]

OK - Could you use a summary query/table instead of a crosstab?

So to paraphrase . . .
Select sum(weight) as MyWeight, Client, Destination
from Mytable
where ShipDate = whateverDate
group by ShipDate, Client, Destination