Explore BrainMass

Database Questions: Query/Commands

I like to make changes to the attached database based on the question (queryquestion.doc).

Since I can not attach the access file, please change the name database.xls to databse.mdb when open the file.

See the attachments.


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 ...

Solution Summary

The solution assists with changes to the database query and commands.