Avatar of Benyaw616
Benyaw616
 asked on

Advice on building a query with nearly 6 million records

Hi All

i'm after some advice on the best way to avoid building a query with 6 million records (or close to that)

i have a logistics matrix to build with 3 points, collection point (40,000 of these) Load Port (8 of these) and destination port (650 of these) from 6 different suppliers.

what i am trying to do is work out the cheapest price, there are 2 tables involved.

1st table has 167k records, it's a list of haulage prices from towns in the UK, each supplier will provide a price from most town to every load port, 2nd table has the price from Load Port to destination port.

however many suppliers subsides the haulage tarriff so the supplier with the cheapest sea freight isn't always the cheapest overall (as another company's haulage maybe alot cheaper)


for example..


1. ABC Ltd. will charge $300 to collect from London and $500 to ship to New York = $800
2. DEF Ltd will charge $400 to collect from London and and $450 to ship to New York = $850
3. GHI Ltd will charge $250 to collect from London and $525 to Ship to New York = $775

therefore GHI is the better option.

ideally the user will just put in from London to New York and my data will show option 3 as the one.
DatabasesMicrosoft Access

Avatar of undefined
Last Comment
aikimark

8/22/2022 - Mon
Rey Obrero (Capricorn1)

a sample db with the correct table names and fields will help to achieve a solution to the problem.

upload a sample db.
ste5an

Please post a concise and complete example (database file). It sounds like proper indexing should do it.
ASKER CERTIFIED SOLUTION
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
aikimark

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
aikimark

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes