Hello experts,
I'm very new to Access so I reached out to some people with experience but nobody could help me even though they were all sure that Access could solve my problem. I'm hoping that someone in this forum can share some input and send me down the right track.
What I'm trying to do is to calculate a price for a delivery service based on 3 parameters: origin, destination and weight. Sounds simple, right? Origin is expressed as 'Store number', destination as 'zip code' and weight as 'KG'. However, the zip code must be translated to a 'zone' category based on the distance from the origin/store, and the weight needs to be translated to a weight class (1-50, 51-100 and so on). Both these 'translations' follow a fixed pattern agreed with the delivery company, so it's quite a straight lookup once you know where to look. Finally, the price model for the service is a fixed ladder where each zone has a price for each weight class.
Example) A 75 kg delivery is sent from store 001 to zip code 12345. Zip code 12345 qualifies as zone 2 from store 001 and the weight 75 KG is within the limits of the 51-100 KG class, which for zone 2 costs EUR 50.
My problem is that I don't know how to:
A) have Access lookup the correct zone based on the store number and zip code provided. The same zip code would return different values depending on the origin/store so how can I tell Access where to look?
B) have Access lookup the correct price based on the zone number and weight. Much like a), the same weight would return different values depending on the zone number...
Only knowing a little bit of Excel-talk, I would express this as asking Access to do several lookups at once, but I don't know how that can be done and I haven't found any Query tutorials online that covers this...
I understand that this might be basics to some of you, but really - any help would be most appreciated!