 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
Creating an Access Database - Step 3: Queries as a RecordSource for Forms
I normally only build queries to use as the RecordSource for forms, in order that I can get the ordering that I want, and as the RecordSource for reports, so that I can drag in all of the data required.
In this part of the guide, we will look at creating the queries to be used to ensure that Access sorts the data in the order that we want. I know that there is an OrderBy property for forms, but using a stored query should make the form load faster, and give us more options. Access normally makes creating queries easy by use of a query grid, that acts as a graphical interface the SQL that actually happens.
In the database, there will be 3 forms that will require a specified sort order. These are the form to display the details for the bands, which will be sorted by the band name, the form that will display the records, which will be sorted by the band name first, and then the record name, and then finally, the form that will display the tracks for each record, which will be ordered by the track number.
Obviously, the first step is to go to the query tab, and then select "New". The first query that we are going to design is for the bands, so we obviously need to have the Band table in the query. As the form will only be displaying band details, we don't need to include any other tables in the query.
Double click on 'BandName' in tblBand, which will add it to the query grid. Set the 'Sort' field to 'Ascending' to get the order that we want, and remove the tick from the 'Show' box. Then double click on the '*' in tblBand, which will add tblBand.* to the query (which is a short way of saying all fields from tblBand). The advantage of doing it this way is that if we went and added a new field to tblBand, it would automatically be included in the query, and hence available in a form based on the query.
That's all that we need to do with this query, so we can save it, as 'qryBand'. If we look at the SQL for the query, it should look like:
| SELECT tblBand.* FROM tblBand ORDER BY tblBand.BandName; |
This is saying "Select everything from tblBand, sorting it by BandName".
So, one down, two to go. The next query that we will create will be the one that will be used to base the Record form on. As before, create a new query in design view, and add both tblBand and tblRecord to the query. The reason for having both tables in is that we want to include a sort on the BandName, and tblRecord only has a numeric field, which doesn't correspond to the alphabetic order of the band.
We will again start by adding the sorted fields to the query grid, remembering that the order of sorting is left to right, so the first field that has a sort order is the first field that is sorted. Therefore, as we want to sort by band name first, double click on BandName in tblBand to bring it down to the Query Grid, and then set it to sort Ascending, and remove the show tick. Next add RecordName from tblRecord, and set it to sort ascending and remove the show tick. Finally, as it is entirely possible to have multiple records with the same title by the same band, add CatalogueNumber to the grid, removing the show tick, and setting it to sort ascending. Finally, double click on the '*' in tblCD to have all of the fields from tblCD in the query. Save this query as 'qryRecord'.
The SQL for this query should be similar to:
| SELECT tblRecord.* FROM tblBand INNER JOIN tblRecord ON tblBand.BandID = tblRecord.BandID ORDER BY tblBand.BandName, tblRecord.RecordName, tblRecord.CatalogueNumber; |
This is saying "Select everything from tblRecord, sorting by the associated band name, and then by the record name, and then by the catalogue number".
The final query that we need to build for the forms is one to ensure that the tracks for each record are sorted by their track number, and is very similar to the first query that we created. Create a new query, and add 'tblTrack'. Double click on 'TrackNumber', set the 'sort' to Ascending, and remove the tick from the 'show' box. Double click on the '*' in tblTrack to add all of the other fields, and then save this as 'qryTrack'.
Copyright & Disclaimer
Last modified at 06/06/2006 15:02:10