Comparing Crosstab queries

I have multiple crosstab queries that have identical formats. The crosstabs
are labled Carrier1, Carrier2, Carrier3, Carrier4. The rows represent states
and the columns represent milage in 50 mile increments.. The data represents
the cost of freight to a given state at the specific milage. I would like to
have a query that selects the least cost solution and displays the table name
from which the lowest price comes form.

An example would be Carrier1 table AL 200 miles Output = $325
Carrier2 table AL 200 miles output
= $150
Carrier3 table AL 200 miles
output= $500


The master query would display in the Row=AL Column=200 Carrier2 becasue
the cost is the lowest at $150.

If the data is in ONE table, you may do it in many ways, one of which is
probably quite intuitive is to first make a query to find the minimum value:

SELECT MIN(output)
FROM tableName
WHERE al=200


Then, make a query on top of it which keep only that minimum value:


SELECT *
FROM tablename
WHERE al=200
AND output = (SELECT MIN(output)
FROM tableName
WHERE al=200)




But you have to have your data in ONE table (or ONE query). Why have you
four of them in the first place?



Vanderghast, Access MVP


"Russell P" Russell wrote in message
...
I have multiple crosstab queries that have identical formats. The
crosstabs
are labled Carrier1, Carrier2, Carrier3, Carrier4. The rows represent
states
and the columns represent milage in 50 mile increments.. The data
represents
the cost of freight to a given state at the specific milage. I would like
to
have a query that selects the least cost solution and displays the table
name
from which the lowest price comes form.

An example would be Carrier1 table AL 200 miles Output = $325
Carrier2 table AL 200 miles output
= $150
Carrier3 table AL 200 miles
output= $500


The master query would display in the Row=AL Column=200 Carrier2
becasue
the cost is the lowest at $150.

The final output I am looking for would look similar to this in a query or
crosstab query. The actual table is much larger.

50 100 150 200 250
AL Carrier1 Carrier1 Carrier2 Carrier2 Carrier2
MO Carrier2 Carrier2 Carrier2 Carrier2 Carrier2
GA Carrier3 Carrier3 Carrier3 Carrier2 Carrier2
VA Carrier2 Carrier3 Carrier1 Carrier1 Carrier1


This would tell me that at the 250 mile range Carrier2 is the best choice
in AL, MO, and GA, whereas Carrier 1 would be the best choice for VA. I
don't know if it is possible to produce this type of matirix from data stored
in one table.

"vanderghast" wrote:

If the data is in ONE table, you may do it in many ways, one of which is
probably quite intuitive is to first make a query to find the minimum value:

SELECT MIN(output)
FROM tableName
WHERE al=200


Then, make a query on top of it which keep only that minimum value:


SELECT *
FROM tablename
WHERE al=200
AND output = (SELECT MIN(output)
FROM tableName
WHERE al=200)




But you have to have your data in ONE table (or ONE query). Why have you
four of them in the first place?



Vanderghast, Access MVP


"Russell P" Russell wrote in message
...
I have multiple crosstab queries that have identical formats. The
crosstabs
are labled Carrier1, Carrier2, Carrier3, Carrier4. The rows represent
states
and the columns represent milage in 50 mile increments.. The data
represents
the cost of freight to a given state at the specific milage. I would like
to
have a query that selects the least cost solution and displays the table
name
from which the lowest price comes form.

An example would be Carrier1 table AL 200 miles Output = $325
Carrier2 table AL 200 miles output
= $150
Carrier3 table AL 200 miles
output= $500


The master query would display in the Row=AL Column=200 Carrier2
becasue
the cost is the lowest at $150.

Yes.

The sub-goal is to get the data like:

Origine Mile Who
AL 50 Carrier1
AL 100 Carrier1
AL 150 Carrier2
....
VA 250 Carrier1


since then, you simply make a crosstab to get the final result.


SELECT a.origine, a.mile, a.who
FROM tableNameHere AS a INNER JOIN tableNameHere AS c
ON a.origine=c.origine
AND a.mile = c.mile
GROUP BY a.origine, a.mile, a.who, a.amount
HAVING a.amount = MIN(c.amount)


is a possible way to get that sub-goal. Note that the table name is used
twice and aliased as a and as c. Someone can see this as two fingers, a
and c, running over a single list (the table) and for a given group (finger
a), finger c can run on all the record such that the c.origine and c.mile
match the values pointed by finger a.


There are other ways to do that, see
http://www.mvps.org/access/queries/qry0020.htm for 3 other ways.

I assume your table has the four fields: Origine, Mile (values from 50 to
250 by increment of 50), Who (the carrier) and Amount (the cost to
'minimize').

Note: the sub-goal CAN list two carriers (or more) for one given origine,
and one mile value (in case the two carriers have the same 'amount'
associated to that group) but the crosstab would list only one of them.



Vanderghast, Access MVP


"Russell P" wrote in message
...
The final output I am looking for would look similar to this in a query or
crosstab query. The actual table is much larger.

50 100 150 200 250
AL Carrier1 Carrier1 Carrier2 Carrier2 Carrier2
MO Carrier2 Carrier2 Carrier2 Carrier2 Carrier2
GA Carrier3 Carrier3 Carrier3 Carrier2 Carrier2
VA Carrier2 Carrier3 Carrier1 Carrier1 Carrier1


This would tell me that at the 250 mile range Carrier2 is the best choice
in AL, MO, and GA, whereas Carrier 1 would be the best choice for VA. I
don't know if it is possible to produce this type of matirix from data
stored
in one table.

"vanderghast" wrote:

If the data is in ONE table, you may do it in many ways, one of which is
probably quite intuitive is to first make a query to find the minimum
value:

SELECT MIN(output)
FROM tableName
WHERE al=200


Then, make a query on top of it which keep only that minimum value:


SELECT *
FROM tablename
WHERE al=200
AND output = (SELECT MIN(output)
FROM tableName
WHERE al=200)




But you have to have your data in ONE table (or ONE query). Why have you
four of them in the first place?



Vanderghast, Access MVP


"Russell P" Russell wrote in message
...
I have multiple crosstab queries that have identical formats. The
crosstabs
are labled Carrier1, Carrier2, Carrier3, Carrier4. The rows represent
states
and the columns represent milage in 50 mile increments.. The data
represents
the cost of freight to a given state at the specific milage. I would
like
to
have a query that selects the least cost solution and displays the
table
name
from which the lowest price comes form.

An example would be Carrier1 table AL 200 miles Output = $325
Carrier2 table AL 200 miles
output
= $150
Carrier3 table AL 200 miles
output= $500


The master query would display in the Row=AL Column=200 Carrier2
becasue
the cost is the lowest at $150.

It is possible to do this with one table (and probably that is the best design)

You could use a table (Rates) like this to store the information
CarrierName
Jurisdiction
Distance
Cost

Then a query like this would get the results you want

SELECT RATES.CarrierName, Rates.Jurisdiction, Rates.Distance, Rates.Cost
FROM RATES INNER JOIN
(SELECT Jurisdiction, Distance, Minimum(Cost) as LowCost
FROM Rates
GROUP BY Jurisdiction, Distance) as LowRate
ON Rates.Jurisdiction = LowRate.Jurisdiction
AND Rates.Distance = LowRate.Distance
AND Rates.Cost = LowRate.LowCost

Then you could use the above as the source for a crosstab query

TRANSFORM First(CarrierName) as X
SELECT Jurisdiction
FROM TheQuery
GROUP BY Jurisdction
PIVOT Distance

YOU might be able to do that all in one query that looks like the following

TRANSFORM First(CarrierName) as X
SELECT Jurisdiction
FROM (
SELECT RATES.CarrierName, Rates.Jurisdiction, Rates.Distance, Rates.Cost
FROM RATES INNER JOIN
(SELECT Jurisdiction, Distance, Minimum(Cost) as LowCost
FROM Rates
GROUP BY Jurisdiction, Distance) as LowRate
ON Rates.Jurisdiction = LowRate.Jurisdiction
AND Rates.Distance = LowRate.Distance
AND Rates.Cost = LowRate.LowCost) as Temp
GROUP BY Jurisdiction
PIVOT Distance

If you wanted the Carrier and the Rate returned in the crosstab, you could
change the TRANSFORM clause to read

TRANSFORM First(CarrierName) & " - " & First(Cost) as X
....



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Russell P wrote:
I have multiple crosstab queries that have identical formats. The crosstabs
are labled Carrier1, Carrier2, Carrier3, Carrier4. The rows represent states
and the columns represent milage in 50 mile increments.. The data represents
the cost of freight to a given state at the specific milage. I would like to
have a query that selects the least cost solution and displays the table name
from which the lowest price comes form.

An example would be Carrier1 table AL 200 miles Output = $325
Carrier2 table AL 200 miles output
= $150
Carrier3 table AL 200 miles
output= $500


The master query would display in the Row=AL Column=200 Carrier2 becasue
the cost is the lowest at $150.