Access newbie needs your help: building a Query

[mrlins]

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!

[ranman256]

your tWeights table would have 4 fields:

[zone],[MinWt], [MaxWt],[price]
z1, 1, 50, 30
z2, 51, 100, 50

in a query, qsWtChrg, bring in 2 tables, tShoppingCartDetail and tWeights
DO NOT JOIN the tables,
criteria: where tShoppingCartDetail.ItemWt >= tWeights.MinWt and tShoppingCartDetail.ItemWt <= tWeights.MinWt

this query is an added charge, as well as qsZoneChrg,
a table of EVERY zip code possible. Even if they dont exist, fill them in.

then add all queries in a union query,
qsShoppingCartChrg
qsZoneChrg
qsWtChrg

[June7]

Would need a table with every store and zip code combination and the associated zone. A non-normalized matrix might be appropriate for this situation. That means the zip codes would be one field then each store would be a field with the zones under each.

Similarly for the weight classes and zones with pricing data. Have the weight class represented by 2 fields (BeginWt, EndWt), fields for the zones with pricing under each.

Expressions with DLookup() domain aggregate function can find the zone and pricing. Use code (VBA or macro - I use only VBA) behind a form if you want to save the price to record.

Do you want to round weights to integer or should the range be 1-50.99 and round to 2 decimals?

[mrlins]

Thank you both for that surprisingly quick input! I knew I would get some expert advice if I reached out - thank you very much! Now all that remains is for me to learn enough Access to make it happen
@June7 - the weight data will have decimals, but the price table does not so the values could be rounded down to integers.

Wish me luck actually building this!

[orange]

You also have to know the Zipcode of the store and destination in order to get the "approximate" distance.
I say approximate since zip codes represent an area, where as the store and destination are points. So a store could be on the eastern boundary of a zip code area and the destination on the western boundary of its zip code. The distance could be significantly different than that when a store is on western boundary of its zip code and destination on the eastern boundary ---even though the zip codes are he same in each case.

I wrote a routine to calculate driving distance between zip codes (using Google maps) that may apply.

A lot depends on exactly what tables and data are available to you.

Good luck