New Totals Query

[Guest]

This is from famous "Northwind" DB example.

Suppose you have an Orders table that has both a Freight field and a
ShipCity field. You can create a query that displays the minimum freight cost
for orders sent to each city and it's Order ID and CustomerID.

1. Create a query that displays the minimum freight cost for orders sent to
each city. Create a new totals query, and drag the ShipCity field to the
query design grid. The Total cell beneath the ShipCity field should be set to
Group By. Drag the Freight field to the query design grid, and set the Total
cell beneath it to Min. When you run the query, it will display the minimum
freight cost for orders sent to each city. ......... How Can I put OrderID
adn CustomerID in total query design and get the result?
2. In SQL view, SELECT ShipCity, Min(Freight) AS MinOfFreight
FROM Orders GROUP BY ShipCity;
Where can I put OrderID and CustomerID in above query.

Thank you.

 

[Gary Walter]

This is from famous "Northwind" DB example.

Suppose you have an Orders table that has both a Freight field and a
ShipCity field. You can create a query that displays the minimum freight
cost
for orders sent to each city and it's Order ID and CustomerID.

1. Create a query that displays the minimum freight cost for orders sent
to
each city. Create a new totals query, and drag the ShipCity field to the
query design grid. The Total cell beneath the ShipCity field should be set
to
Group By. Drag the Freight field to the query design grid, and set the
Total
cell beneath it to Min. When you run the query, it will display the
minimum
freight cost for orders sent to each city. ......... How Can I put
OrderID
adn CustomerID in total query design and get the result?
2. In SQL view, SELECT ShipCity, Min(Freight) AS MinOfFreight
FROM Orders GROUP BY ShipCity;
Where can I put OrderID and CustomerID in above query.

Hi Jay,

For one solution, try

SELECT
Orders.ShipCity,
Orders.Freight As MinOfFreight,
Orders.OrderID,
Orders.CustomerID
FROM Orders
INNER JOIN
[SELECT
ShipCity,
Min(Freight) AS MinFreight
FROM Orders
GROUP BY ShipCity]. AS Q
ON
(Orders.Freight = Q.MinFreight)
AND
(Orders.ShipCity = Q.ShipCity)
ORDER BY Orders.ShipCity;

or, just save your original query (as say "qryCityMinFreight"),

then join Orders to qryCityMinFreight as above
(instead of using subquery).

good luck,

gary

 

[Gary Walter]

(sorry, I believe you will need to alias Orders
in subquery....)

SELECT
Orders.ShipCity,
Orders.Freight As MinOfFreight,
Orders.OrderID,
Orders.CustomerID
FROM Orders
INNER JOIN
[SELECT
O.ShipCity,
Min(O.Freight) AS MinFreight
FROM Orders As O
GROUP BY O.ShipCity]. AS Q
ON
(Orders.Freight = Q.MinFreight)
AND
(Orders.ShipCity = Q.ShipCity)
ORDER BY Orders.ShipCity;