User only to b able to see/select shipping options for a particular country

[peskywinnets]

So I'm struggling to work out the approach here, imagine this scenario

I have a sales database which is full of incoming orders to be processed

Order 1 is in the UK (which is where I ship from )...postal options are 1st class, 2nd class

Order 2 is in Germany ....postal options for that country are International Standard, International Tracked.

Here's the thing....I want the end user only to see 1st class & 2nd class for order 1 (no other postal options) & for order 2 only the postal options for that country (so user shouldn't be able to see 1st class & 2nd class). There will be at least two postal services available per country.

At the moment, for the shipping field...I see ALL the postal options for all countries.

How to I approach this?

 

[jdraw]

Hi Pesky,

It seems your restriction is dependent on the Country of the Order ---not sure if that is Country of the Customer, Country of Supplier, or some other option for Country and Order.
Need a little more clarification on all the "attributes/aspects" involved.

Perhaps you could show us some tables and relationships or
some current logic where this new feature might have to fit

 

[RogerCooper]

You are going to need a table which contains a field for country and a field for postal options, listing all possible valid combinations. You will need to use a filter or a query to bring up the restricted list.

 

[peskywinnets]

At the moment I have a table for the customer's address (with shipping address including the country).

I have a table for shipping services (all of the possible shipping services I use - this table is not by country)

I have a table for the invoice ...this is linked to the shipping service table (on ShippingServiceID) & customer table (on CustomerID)

You are going to need a table which contains a field for country and a field for postal options, listing all possible valid combinations

It's not clear how I deploy that, do you mean like this....


Country......PostalService
UK...............1st class
UK...............2nd Class
US...............International Standard
US...............International tracked & signed

That's not how I'd wish to arrange it, I seek something more like this

Country......BasicPostalService.........UpgradedPostalService
UK...............2nd Class.......................1st class
US...............International Standard......International tracked & signed

so in the postage field, I seek it so my end User (using Access) can select either 2nd class or 1st class for any UK orders (but won't see the international postal services)

 

[jdraw]

Pesky,

I agree with Roger, you need a table(s) to represent your business rule(s).

Do you only deal with US and UK based Customers?
What Countries are involved?
What exactly is the Postal thingy you want to relate to each Country?

Just tell us in plain English what you want to do and then readers can offer suggestions for how to do it.

Can Customers in country XXX make an order and have article delivered to a different country (YY)?

Sounds like PostalOptionsForCountry table.

 

[The_Doc_Man]

Pesky, here is what YOU need to decide because it is your problem and we don't fully understand nuances thereof.

You need a table of SOME kind that lists viable shipping services linked to a location. It is possible for this to be a non-unique table, with pairs of data <location,shipper>, where location and shipper are either not indexed or indexed (Dups Allowed). This could become a junction table to your shipper information and to the other thing, which I will address separately.

If you need a shipping service based on destination, then you need to link the ship-to address to that junction table. But if the issue is based on starting point (ship-FROM) address, then you link THAT to the junction table. One of those two is right but we can't tell which one from the information presented in your post.

 

[peskywinnets]

My apologies that I'm not putting my needs over clearly.

I have a table that lists every country (over 200)

When an order arrives, depending on the value, some VBA code decides which postal service should be use ...this bit I can do & works fine with a table structure like this...

(I used Excel in the above jpg - just for speed)

it's just a simple 'if ordervalue > $xx then shipping service = xyz' blah blah. No probs here.

...where I'm struggling is if a user subsequently wants to change the postal class manually (he sees the order list in query format ....bits taken/presented from several tables - yes, I know it should be a form - it will be eventually)...how can I present him/her with a drop down list for the two postal options (Basic & upgraded) per country (filtering out the other postal options)

 

[Minty]

Store it as per this.

Country......PostalService
UK...............1st class
UK...............2nd Class
US...............International Standard
US...............International tracked & signed

Add a column or two for the minimum order value and the shipping cost. You can then automate your standard carriage based on the value, and present the list to your user based on country.

 

[peskywinnets]

Store it as per this.

Country......PostalService
UK...............1st class
UK...............2nd Class
US...............International Standard
US...............International tracked & signed

Add a column or two for the minimum order value and the shipping cost. You can then automate your standard carriage based on the value, and present the list to your user based on country.

Thanks...that'd work

...I guess I was coming at this wrong ...I've a table for shipping service & a table for country...but - as you've suggested - what I need to do is make a bigger combined table & lob on a couple of extra columns .

 

[peskywinnets]

Actually still struggling here. A bit more background info....

I've an API that goes & retrieves an order from Ebay that I need to pack & dispatch. The order info comes into Access in XML...I parse this data & spread it across three tables (I have 3 main tables in play - Customers, Invoice & Invoice Items), the Ebay order XML info has the customer selected 'shippingclass' which I map that to a local ShippingServiceID....which is then stored in an invoice table

After asking my question above & receiving your answers, off I went & set up a new table as recommended (all the countries of the world listed in a table - the two letter country code, along with the ShippingServiceID available for each country...so got example the USA has two ShippingServiceIDs 'standard' or 'tracked' ...but the UK (where I'm based) has quite a few more 1st Class, 2nd class, 1st Class tracked, 2nd class tracked etc.

Hopefully you're all following this!

Anyway, ultimately when I come to process my orders I have a query which pulls in all the order info from the three tables (Customers, Invoice & InvoiceItems) .....the ShippingServiceID in the invoice table is a lookup field ....prior to asking my question, I simply had this field referencing a Shipping table which has all the ShippingServiceIDs within ....but now I only want to see the ShippingServiceIDs filtered per country ...but I can't seem to work out a way of deploying this lookup table in the new way of doing things (only seeing Shipping services per country)

Sorry it's a bit wordy...it's a little hard to describe so perhaps I need a different way of presenting my problem?

 

[jdraw]

I'm not sure of your exact set up, but if are working with a form

- you could use a cascading combo construct
-this really 2 combo/dropdown controls
-in the first, you select the country
then, based on the country selected, you alter/create the rowsource for the second
combo using the Postal/shipping options that relate to the country selected in the first combo
-then you requery the form to have it display appropriate options in the second combo

there are many examples of cascading combo.
Here are 2 videos that I often recommend:

http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

Good luck

 

[peskywinnets]

Thanks jdraw....sound & looks a bit involved! (but will study in detail when I have a bit more quiet time)

They say a picture is worth a thousand words, so let me see if I can put this another way...

[/url]

 

[jdraw]

Do you only have 3 tables in your design??
No order table??

I was talking about doing something on a form.

When you are identifying a Customer's shipping option/preference

you first get the Country, then
you get the shipping options from your ShippingServiceByCountry

BUT
your ShippingServiceByCountry also needs to include the info you showed earlier

Country......PostalService
UK...............1st class
UK...............2nd Class
US...............International Standard
US...............International tracked & signed

How are you gathering/inputting this PostalService info?

Pesky we really don't know your processing steps....

 

[peskywinnets]

I have a fair few tables in my design but have only showed a few of the main ones in play.

Re the design of the ShippingServiceByCountry table ....yes that's what I've done...

[/url]

....i.e. All non UK countries have only two shipping services ...whereas the Uk has a lot more

 

[jdraw]

OK. So do you use a form?
As per the last lines in my previous post

How are you gathering/inputting this PostalService info?

Pesky we really don't know your processing steps....

 

[peskywinnets]

OK. So do you use a form?

At the moment - no - my user interface is a query (as only myself - and occasionally my wife use it) ....I don't need a nice GUI....want to get the basic functionality right first

Here you can see the problem in the query that we use (essentially our 'Orders to be processed' screen')....

[/url]

....ALL shipping services are showing for that order to Australia. (this isn't about laziness/neatness.....it's actually causing errors/problems for us with teh Post Office when we select the wrong shipping service - not all shipping services are available to every country. For example to Australi' there's a 'Tracked' service, but not the more usual 'Tracked & Signed' service - these are two different services each having their own type of tracking sticker to affix to the package - we don't want to put the wrong one on - it would hepl if Access only showed the postal services 'in play' for that country)


& here's how the tables are linked to one another

[/url]

 

[jdraw]

Hmmmm. I think you are doing yourself harm by neglecting Form(s) as the appropriate interface.

When you identify a country, another option would display the shipping/Postal options for that Country.
With a query I would think you would need another table in your joins to show the options by country, but that in itself would not be sufficient (in my view).

From a very quick glance at your tables I think you have multiple subjects in your tables--but I don't know your business, and you aren't complaining of having to make work arounds constantly.

I think you could do a lot of streamlining by adjusting some table designs.

 

[Minty]

As per jDraw - I think the only way would be to use a form to look up the available shipping options and populate the lookup based on that. You then select and store the value. With this approach you can also select the most appropriate shipping method automatically based on your order value.

If you pull those values in to query you'll end up with duplicate records being displayed and no ability to edit the values.

 

[peskywinnets]

Ok gents...thank you for your input...I'll have a bit more dabble - if I'm still stuck (likely), I may slim this down & upload a test database to see if anyone can spot/suggest/solve.