Database Questions: Query/Commands

Solution Preview

See attachments.

Query #5

1. Create a query with an aggregate function that lists customers and the total amount ordered by each customer. [Use the Discounted Price calculation that you created in query #4.]

There should be only 2 fields in the query: One for the Customer Name, and the other for the total amount that they have ordered. Each customer should be listed only once in the dynaset.

2. Display the Total Amount Ordered with dollar signs and 2 decimal places.

3. Sort in Ascending order by Customer Name.

4. Change the heading of the Total Order column to read Total Order.

5. Name the query qry#5CustomerTotalOrder.

Notes: This one was a first. I did not have to use any sub queries because the sort key was the first field. To show the total bar, go to the view menu and check totals. The customer name column should have "grouped by" as the entry for the totals row. Set the totals row for the total order column to "sum".

Query #6

1. Create a query that lists the following fields, in the order in which they appear below:

ｷ Territory Name
ｷ Sales Rep First and Last Names, displayed as follows:

Last Name, First Name

ｷ Customer Name
ｷ Contact First and Last Names, displayed as follows:

Last ...