SIMPLE QUERIES

Go to the Query section of the Database Window. Select New then New Query. You are given the opportunity to nominate the table[s] involved in the query - Add Albums then Close the Add Tables dialog. You will be presented with a blank QBE (Query By Example) grid. This is termed DESIGN VIEW.

From the pictured fields of the Albums table, click-drag AlbName into the first Field space on the QBE grid, Artist into the second, Media into the third. For the Media field, Un-check the Show box and enter cd into the criteria box.

Execute the query by pressing the ! button on the button bar. This will give you an answer table containing details of all cd's on record - the is termed DATASHEET VIEW. The resulting record set is a DYNASET - changes made to it will be pumped back to the base tables - many query answer tables are updatable in this way, some are not (non-updatable answer tables are termed SNAPSHOTS).

Press the SQL button on the button bar. This takes you to SQL VIEW - don't be put off by all of the extra bits, they are largely unnecessary - verify this by pruning the query back to the following:

                 select AlbName, Artist
                 from Albums
                 where Media = 'cd'            and run[!]ning it.

It is possible to flip between Design, Datasheet and SQL Views freely - this is often necessary when composing complex queries (as this is unnecessarily complex in QBE).

Follows is a set of queries, many of which are needed for later activities - compose each of them separately, only including those tables required in each one - try using a mixture of SQL and QBE. To save the queries into your database container, simply press the DISK button on the button bar and use the name presented (long names with spaces are supported and are a good idea).

• Distinct Media
• Artists arranged alphabetically
• RecCos arranged alphabetically
• All Albums details, sorted by Artist, PDate
• All Artists with their Instruments sorted by Artist,Instrument
• All Songs arranged alphabetically
• Count of all cds
• Artists and album counts
• SerNums without tracklistings
• Bass players
• Distinct RecCos
• Song listing of Violent Femmes self titled

---

PARAMETERISED QUERIES

To place parameters in queries, place prompts in SQUARE BRACKETS for the parameters in the Criteria boxes for that column, Access will display a prompt window for each parameter mentioned

Write parameterised queries (and save them with the names given) that will perform the following:

• Songs given Artist and AlbName
• Song count given SerNum
• Artist who play a given Instrument
• AlbName and Artist of given PDate period (start and finish dates must be prompted for)
• Medias for given AlbName and Artist
• Song count given AlbName
• AlbName and PDates of given Artist
• AlbNames featuring a given Instrument

---

ACTION QUERIES

Action queries are not requests for information, but rather requests to update that information. Let us suppose that I exchange all my LPs for CDs. Using SQL, enter the following:

            update Albums
            set Media = 'CD'
            where media = 'LP'

Run[!] it. You will be asked to verify the action (CANCEL please unless you wish to help me fund the change), then save the update query. Notice that the query icon for an update query differs from a data supply query.