Results 1 to 9 of 9
  1. #1
    ChrisA1967 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    7

    Ecommerce shipping problem

    Hoping someone can help me with a problem I've got. Please bear with me it is a long problem...



    I want our database to be able to choose the correct courier for a product automatically by matching the marketplace expected delivery time with the courier delivery time, then choosing the cheapest of the couriers.

    So first of all we have several different marketplaces in several different countries. So I have created a marketplace table listing with fields for marketplace and base country. Each marketplace will have up to 4 delivery services (domestic standard, domestic expedited, EU, Rest of world) with each service having a maximum expected delivery time. So I have created another table marketplace shipping services with fields marketplace Id, shipping services, maximum delivery time. Every service won't delivery to every country, so I have created another table Marketplace shipping service countries containing the fields marketplace shipping service Id and country.

    Next we have a products table which obviously includes the product code and the weight of each product. Now the shipping weight of each product can depend on the country where it's shipped because some of the products contain power cords, so the power cords for different countries have different weights. Also on some products cheaper packaging is used for the UK (where we are based) than every other country, because it's cheaper shipping (using large letter format) in the UK and the product is less likely to get damaged. So need some help setting this up?

    Next we have the couriers. I have created a couriers table, which contains 4 different couriers. Then each courier offers different services e.g signed for, tracked, standard etc. So I've set up a courier services table with fields for the courier id then the name of the service. Each courier service has different delivery zones they will deliver to. So I have set up a courier service delivery zones table containing the fields courier service Id and the delivery zone. Each delivery zone contains several different countries so I have set up a table courier delivery zone countries containing the fields courier delivery zone Id, country, delivery time. Each courier service can also have different weight zones (e.g 101 - 250g, 251 - 500g etc) so I have set up another table weight zones which contains the fields courier service id and weight from and weight to. The final delivery cost depends on the delivery zone and the weight zone so I have created another table courier prices which contains the fields delivery zone id, weight zone id and price.

    So I have got all my data in each table and what I want to do is in a query is show the marketplace, the marketplace service the product code and the cheapest delivery service and price, but whilst making sure the courier service delivery time is equal to or less than the marketplace service delivery time. I've linked all the fields in a query but every time I try to run it crashes saying the query is too large over 2gb.

    Just wondering if anyone can help me get it to work?

    Thanks in advance.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    how about an example database, this looks like an interesting problem. Also, some sample 'input' with what your eventual algorithm should find as the appropriate carrier/service

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    ChrisA1967 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    7
    How do you add a database? I've pressed managed attachments then uploaded it, but nothing appears in the "Home" box?

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    To add an file to your post
    --do a Compact and Repair and then
    --ZIP up the db and
    --attach it to a post with the GoAdvanced area

    See the buttons below the post create/edit section

  6. #6
    ChrisA1967 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    7
    I just get the upload failed everytime I try to upload anything.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you have to click the 'insert in line' button to attach it to your post after you upload it (lower window of the attachments interface)

  8. #8
    ChrisA1967 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    7
    Quote Originally Posted by rpeare View Post
    you have to click the 'insert in line' button to attach it to your post after you upload it (lower window of the attachments interface)
    it doesn't even get to that stage. As soon as it's finished uploading, it says upload failed.

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Try zipping it up first, it may be too large.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Append Recent Shipping History
    By nick243 in forum Queries
    Replies: 2
    Last Post: 05-13-2016, 06:19 AM
  2. Calculate shipping costs by zip code
    By joecamel9166 in forum Access
    Replies: 19
    Last Post: 04-19-2016, 01:35 PM
  3. Shipping by Month
    By NateSmith in forum Access
    Replies: 2
    Last Post: 05-06-2014, 04:07 PM
  4. Shipping database with references?
    By Drak in forum Programming
    Replies: 1
    Last Post: 01-27-2012, 12:21 AM
  5. Creating a load list for shipping
    By Reaper in forum Access
    Replies: 9
    Last Post: 11-13-2011, 07:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums