Can this be done with a query?

[Skirrow]

Whats the best way to arrange my data for the following, and can a query do it or do I need to use VBA with an "if" command?

I have the following data in my products table....

[B]Products[/B]         [B]Courier[/B]          [B]Weight (g)[/B]
Guitar Case       DHL             700
Guitar Strings    Royal Mail      89
Tambourine        Royal Mail      150

Courier is a lookup for my courier table which is like this....

[B]Courier[/B]      [B] 0-100g Cost[/B]       [B]100-250g Cost[/B]      [B]250g+ Cost[/B]
DHL           £4                £4                 £6
Royal Mail    £0.93             £1.19              £1.53

So my query or whatever needs to return the Shipping price for each product. eg. Guitar case it needs to first determine it's DHL (which I've done withe the lookup) but then, and this is the bit I'm struggling with, it needs to determine that it's over 250g and return the correct value (£6 in this case)

Have I got my data set out in the optimum way? Do I need VBA to do this - I find myself wanting to type IF.... into the query

Any help much appreciated.

Cheers,
Dave

 

[boblarson]

So my query or whatever needs to return the Shipping price for each product. eg. Guitar case it needs to first determine it's DHL (which I've done withe the lookup) but then, and this is the bit I'm struggling with, it needs to determine that it's over 250g and return the correct value (£6 in this case)

Have I got my data set out in the optimum way? Do I need VBA to do this - I find myself wanting to type IF.... into the query

If you want to do it the RIGHT way, then redesigning your database so that it is normalized would make it so much easier to pull the right data. You have a one-to-many situation (one courier to many possible prices) so you would need to have another table, at least, which would store the courier, the weight, and the price for that weight. Then you can find the correct price easily.

If you ever find yourself with repeating fields within a record (in your case the price for a weight) then you know your database is not designed properly.

 

[Rabbie]

add an extra expression to your query that links the two tables

iif( weight < 100,cost0100,iif(weight>250,cost250plus,cost100250)) as shipping cost.

 

[Skirrow]

Rabbie, that's just what I need. I was looking for the function IF, didn't cross my mind that it might be IIF..... I'll give it a try now.

Bob, I think my data is set up as you say, the 2 bits in code I put in my original post are separate tables. Didn't realise you could use IF in queries, I'm new to access and tried IF but it gave me an error, IIF looks like it works how I expect it to, I'll try it now.

Cheers!

 

[Skirrow]

Works a treat! Cheers!