Permutations

  • Thread starter Thread starter MR
  • Start date Start date
M

MR

I have a table with approximately 250 city names. I want
to create unique origin-destination permutations of those
city name pairings to use in a navigation route testing
application. Anyone know a simple way?
 
You can do this with two queries, one to give distict cities, then another
to cross join them and eliminate pairs of the same city:

SELECT DISTINCT city FROM tblCities; -- (call this qryUniqueCities)

SELECT qryUniqueCities.city AS [From], qryUniqueCities_1.city AS [To]
FROM qryUniqueCities, qryUniqueCities AS qryUniqueCities_1 where
qryUniqueCities.city <> qryUniqueCities_1.city;

Doug
 
Assuming that the CitiNames are unique in your Table, you
can simply use a Cartesian self-joint like:

SELECT Ori.CityName, Dest.CityName
FROM tblCity As Ori, tblCity As Dest

This will create a dataset of 62,500 rows.

If you don't want the same-City pair (e.g. New York - New
York) then add criteria like:

SELECT Ori.CityName, Dest.CityName
FROM tblCity As Ori, tblCity As Dest
WHERE Ori.CityName <> Dest.CityName

This should give 62,250 rows.

Now, the question is whether you do mean permutations or
you actually mean combinations?

HTH
Van T. Dinh
MVP (Access)
 
Back
Top