Need to determine the 8 closest stores in each record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is my story sad but true...

Here is my DB layout imported from Excell:

CONTROL NAME STORE_3 STORE_4 STORE_5 STORE_6 etc.
1 Tim 88.59 38.43 39.22
100.04
2 John 92.07 40.44 61.41
9.75

CONTROL is my key; NAME is a customer; STORE_3 thru STORE_67 are distances
from that store to the customer.

What I need to do is determine the closest eight stores to each of the
33,000 records in my DB and then place those store numbers into one of eight
fields ST_1 thru ST_8 for each of the 33,000 records.

Any HELP will be greatly appriciated.

Thanks
 
Hi Dan,

Can you change the design of your database at this stage?

I Only ask because you might find it easier to have a table a bit like this:

Control StoreId Distance
1 3 88.59
1 4 38.43
1 5 39.22
2 3 92.07
2 4 40.44
2 5 61.41

etc. etc.

This will make your task MUCH easier. Otherwise, I guess you'll need some
huge 'IIF' statement, or some massive UNION query to do what you require.

Cheers,

Chris.
 
This is my story sad but true...

Here is my DB layout imported from Excell:

CONTROL NAME STORE_3 STORE_4 STORE_5 STORE_6 etc.
1 Tim 88.59 38.43 39.22
100.04
2 John 92.07 40.44 61.41
9.75

CONTROL is my key; NAME is a customer; STORE_3 thru STORE_67 are distances
from that store to the customer.

What I need to do is determine the closest eight stores to each of the
33,000 records in my DB and then place those store numbers into one of eight
fields ST_1 thru ST_8 for each of the 33,000 records.
Click to expand...

This is a good spreadsheet design... but a poor relational table
design. "Fields are expensive, records are cheap" - tables should be
tall and thin, not wide and flat!

Try migrating this information into two tables (you actually need
three, one listing the stores):

Customers
CustomerID <your [CONTROL]>
LastName <don't use the reserved word NAME as the NAME>
FirstName <much easier to handle in two fields>

Stores
StoreID <3 for STORE_3, etc.>
StoreName
Address
City
State
PostCode

Distances
CustomerID <link to Customers>
StoreID <link to Stores>
Distance

You can manually fill the Stores table (you may already have this
data); run an APPEND query to migrate the customer information from
your spreadsheet into Customers; and use a 'normalizing UNION query'
to fill the Distances table. It requires creating a new query in the
SQL window:

SELECT [CONTROL] As CustomerID, (3) AS StoreID, [STORE_3] AS Distance
FROM [yourspreadsheet]
WHERE [STORE_3] IS NOT NULL
UNION ALL
SELECT [CONTROL] As CustomerID, (4) AS StoreID, [STORE_4] AS Distance
FROM [yourspreadsheet]
WHERE [STORE_4] IS NOT NULL
UNION ALL
SELECT [CONTROL] As CustomerID, (5) AS StoreID, [STORE_5] AS Distance
FROM [yourspreadsheet]
WHERE [STORE_5] IS NOT NULL
UNION ALL
<etc across all stores>

This will "string out" the spreadsheet; you can save this Union query
and then run an Append query to fill the Distances table from it.

Once you have this, it's a very simple matter of running a Top Values
query on the three linked tables.

John W. Vinson[MVP]
 
Dear Dan:

May I suggest you get a GPS unit or GPS maps of the area where these store
are. Entering a coordinate for each store then allows you a quick
calculation for the distance.

With 8 stores, you must keep 56 distances in the database. If you add
another store, there will be 72 distances. If you eventually have 20
stores, there would be 380 distances.

The way you are storing this now, you would need to add a new column for
each additional store. After 200+ stores you will run out of allowable
columns.

You could normalize this, as has been suggested to you. This would overcome
one problem.

However, given simply the GPS coordinates of each store, the distance is
readily calculated (in air miles, mind you - if you are storing miles by
road distances, then you would need to keep the 56 distances in 56 rows
using the normalized design).

For distances up to several hundred miles, a simple pythagorean calculation
on the differences in latitude and in longitude will do quite well.
Assuming each degree is 360,000 feet you can convert the result to feet or
miles. If you are very far North of the Equator, you can derate the
latitudinal difference by the cosine of the average latitude for a little
more accuracy.

If you are storing road miles, then you can save duplicate entries by
storing only the distance from store A to B, and drop B to A, since the
distance would be symmetrical. For this to work, always use some unique key
to sort the stores so the lookup can be arranged to use that order. For 8
stores, this is then 28 distances to store, instead of 56. By the time you
get to 100 stores, you save 4950 entries.

Tom Ellison
 
Back
Top