dolution

Create an appropriate query that returns the following information for all products that have not been discontinued: product name, product category name, unit price, units in stock, and units on order. The query should filter out all discontinued products before they are returned to Excel. Return the results as an Excel table. Then create another table right next to it with a similar query, but this time ask only for the product name and category of all discontinued products. (Note that the discontinued field is 1 for discontinued products, 0 for others.) Problems 39–40 (and 45–46 in the Level B section) are based on the file Classical CDs.mdb. Open this file in Access, click on Database Tools and select Relationships to see the relationships diagram. This is the attempt by one of the authors to capture his rather large classical music collection in a database. Each record in the CDs table corresponds to one CD “album.” (Usually it’s a single CD, but some come in sets.) The large CDs Works table contains a record for each work on each CD. There are links to the musicians playing the work, which could include an orchestra, a chamber group, a choral group, a conductor, and/or one or more artists (such as pianists). The Works table has a record for every work (piece of music) in the collection, with a link to the composer. If you open one of the tables with foreign keys in Access, such as the Artists table with a foreign key to the Instruments table, you will see the names of the instruments. This is a lookup trick that is possible in Access. Even though you might see Piano, only the foreign key (6 for Piano) is stored in the Artists table. The implication is that if you want a list of artists and their instruments, as in the next problem, you will need both the Artists and Instruments tables in the query.