If query help

[KenshiroUK]

Okay my boss has requested that I create query based on price and country.

For instance if an order is 20 to 40 pounds it returns a shipping code of TRD, if orders are 40 pounds and over it returns TRDS. This is based in the UK.

However if an order is based in EU such as France this returns a shipping code of ASI, and non EU will return code of IST.

Is this possible, or do I need create a table with codes in it?? Really stuck on this.

 

[pr2-eugin]

Best to have a table, as the codes may change in future.. so instead of hard coding everything you can just update a table..

 

[KenshiroUK]

Best to have a table, as the codes may change in future.. so instead of hard coding everything you can just update a table..

Codes never changed, because they're what we've created.

 

[RainLover]

Never say never.

 

[pr2-eugin]

Let me rephrase, you 'create' a new code.. then instead of adding a new IIF in the Query you can just add a code in the table.. say if your table is something like..

C_ID    CountryName        CountryCode
1        France            EUPC
2        Germany           EUPC
3        UK                UKPC
4        USA               USPC
5        Qatar             QAPC

So if you want to add India, then instead of hard coding you can just add it to the list..

C_ID    CountryName        CountryCode
1        France            EUPC
2        Germany           EUPC
3        UK                UKPC
4        USA               USPC
5        Qatar             QAPC
6        India             INPC

 

[KenshiroUK]

Let me rephrase, you 'create' a new code.. then instead of adding a new IIF in the Query you can just add a code in the table.. say if your table is something like..

C_ID    CountryName        CountryCode
1        France            EUPC
2        Germany           EUPC
3        UK                UKPC
4        USA               USPC
5        Qatar             QAPC

So if you want to add India, then instead of hard coding you can just add it to the list..

C_ID    CountryName        CountryCode
1        France            EUPC
2        Germany           EUPC
3        UK                UKPC
4        USA               USPC
5        Qatar             QAPC
6        India             INPC

My UK orders will be different because if my first order its £20 to £40 this will be TRD, if it is £40 and more than TRDS. I hope that makes sense.

 

[pr2-eugin]

I do understand that Ken, basic idea behind this would be to first lock the code.. then after that you can have a simple If to see if the (basic country) code is TRD, if so then check the price.. something like..

IIF([countryCode]='TRD', IIf([amount]>=20 AND [amount]<40, 'TRD', 'TRDS'), [countryCode]) AS NewCode

Hope this makes sense..

 

[KenshiroUK]

I do understand that Ken, basic idea behind this would be to first lock the code.. then after that you can have a simple If to see if the (basic country) code is TRD, if so then check the price.. something like..

IIF([countryCode]='TRD', IIf([amount]>=20 AND [amount]<40, 'TRD', 'TRDS'), [countryCode]) AS NewCode

Hope this makes sense..

Your're a legend mate! It worked, Any ides on how to include the the EU as ASI and non EU as IST?

 

[KenshiroUK]

Expr3: IIf([ship-country] DE, AT, BE,"CAI","ICS")

Something like that, not sure what the missing piece is, when query is run the other countries will be ICS and not CAI. Anybody have any ideas?