New Totals Query

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.

SELECT Orders.*
FROM Orders
WHERE Orders.OrderID In
(SELECT TOP 1 OrderID
FROM Orders O
WHERE O.ShipCity = Orders.ShipCity
ORDER BY Freight);


--
Duane Hookom
MS Access MVP


"Jay" wrote in message
...
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.

"Duane Hookom" wrote:
Thank you. Duane.
Would you please explain your SQL query especially in :WHERE Orders.OrderID In
(SELECT TOP 1 OrderID
FROM Orders O
WHERE O.ShipCity = Orders.ShipCity
ORDER BY Freight);

SELECT Orders.*
FROM Orders
WHERE Orders.OrderID In
(SELECT TOP 1 OrderID
FROM Orders O
WHERE O.ShipCity = Orders.ShipCity
ORDER BY Freight);


--
Duane Hookom
MS Access MVP


"Jay" wrote in message
...
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.

The criteria is a subquery that attempts to select one record from the table
where the shipcity is matched. The table in the subquery could be any table
or query.

--
Duane Hookom
MS Access MVP


"Jay" wrote in message
...


"Duane Hookom" wrote:
Thank you. Duane.
Would you please explain your SQL query especially in :WHERE
Orders.OrderID In
(SELECT TOP 1 OrderID
FROM Orders O
WHERE O.ShipCity = Orders.ShipCity
ORDER BY Freight);

SELECT Orders.*
FROM Orders
WHERE Orders.OrderID In
(SELECT TOP 1 OrderID
FROM Orders O
WHERE O.ShipCity = Orders.ShipCity
ORDER BY Freight);


--
Duane Hookom
MS Access MVP


"Jay" wrote in message
...
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.