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.