Examine the sample purchase order database for this session and be sure you understand each of the queried that have been developed and what they do.
Add the following data to the dentist database
For each of the following queries, there is no problem in adding records to tables if you feel it would help you to understand what is happening in the query.
Create a Select query that is based on the Appointment and Patient tables and that displays all the fields from the Appointment table and Amount owed from the Patient table. Name it Appointments. This query will be used in a later session to allow for the dentists to make bookings and to take payment following treatment.
Create a Select query that is based on the Appointment and Patient tables that allows the user to enter a dentist's name and the output then displays the dentist's name, the patients full name in a single field and the patient's address, town post code and telephone number. Name this query Dentist's patients
Under the View menu of a query in design mode there is an option called Totals. Once selected this provides an additional row in the grid. This provides a pull-down list that allows you to provide collated output.
Create a Select query that will display for each dentist how many of each of the treatments he/she has made and name it Dentist's treatments.
A Yes/No field returns a 0 if it is not selected and a -1 if it is. To create a query that returns zeros and negative numbers is relatively easy. However, the output to this query should be a positive number. Use the Help system (Hint: Expressions and the Abs() function)
In this query you are going to create an archiving process. What is required is that if a patient has not been for an appointment for over two years they are removed from the current records. However, there may be a good reason for their absence so you do not want to delete their records altogether. The first thing you need to do is to create a table that will store the patient details. You could do this with a Make Table query, but the alternative would be to create a copy of the Patient table. With the database window visible, right click on the Patient table. From the menu select Save As/Export, and selecting Within the Current Database type Patient Archive as the New Name. This will create an exact copy of the Patient table including the data. Since the data is not necessary within the archive you should open the table and delete all the data.
Create the queries that are going to perform the archiving. In this case you are going to need three types of query: Select (Patient archive (Select)), Append (Patient archive (Append)) and Delete (Patient archive (Delete)). Hint: Do the append first using the Date() function in the criteria. Select what you have appended and use that in your criteria for the Delete query. See if you can figure out why you need three queries rather than two.
At the end of this session, the purchase order system will consist of two forms. The first of these will be a form-based main menu, whilst the second will allow the user to view/edit existing purchase orders as well as adding new ones. So why two forms? We know that the system should support a number of processes e.g. to view/edit/add purchase orders and to archive purchase orders older than a certain date. To allow for these processes we have created a number of queries.
It is possible that we could squeeze everything onto one form, but would it make sense to put different processes together. Users come to systems with a particular purpose in mind. You need to be familiar not only with what they do, but also how they are used to achieving it. Once you know this you can have an idea of the steps they go through and structure your system to model those activities. Since your system can support a number of processes, this often means that you need to offer a choice of actions at the beginning of your system. This is where your main menu comes in.
So what does the Main Menu offer? Concentrating on the two major processes outlined above, they are not related to one another so we can deal with them independent of one another and they can both be options from the Main Menu. If this were not the case, you would have to map out how each of the processes links with each other. You would have to know whether, for example, one is dependent on the other, in which case processes may be sequenced. For any system that is going to contain more than two forms I would recommend that you map the processes to forms and then decide how they are all going to link together by sketching out on paper. It is far easier to get an idea of your system if you think about high-level design before you commit yourself to development.
The process to view/edit/add purchase orders obviously needs a form of its own; however, archiving purchase orders older than a certain date does not really require a form, so this will just be an option from the Main Menu. The only other thing that will be required will be some way to close the system when the user has finished what they want to do.
Create the Main Menu for the purchase order system. The form should not be based on a table or query, it should be opened in design view and its size increased to allow for things to be put on it. You should add three buttons. If your wizards are activated, then you should press Cancel as soon as the wizard appears. In the properties to the buttons, change their captions to Purchase Orders, Archive and Quit. Add a label to the form that informs the user that this is the Main Menu. Modify the size, font, colour and other properties of each of the objects until you are satisfied with the look and feel of your form. Click on the Detail area of the form and change its Back Color property to 7524210 then save the form as Main Menu.
Modify the form properties in line with the suggestions made in the lecture. Try each one at a time to see the effect that it has. To do this select Form View form the View menu.
Create a form that will display all of the details from a purchase order. This is not as straightforward as it might appear. In the paper-based version of the system details about the purchase order appeared once and then there would be lines of individual items purchased. Because we have now converted this into a database, all of this data is split between a number of tables. The Purchase Order part is fairly easy because that is all stored in the Purchase Order table. However, the purchase order lines are now stored in the Purchase Order Line and Stock tables. Therefore there is one more step before you can create the form you need to temporarily create a query. This query should be based on the Stock table, from which you will need the Item Code, Item and Cost of Item fields, and the Purchase Order Line Table, from which you will need the Number of Items field. Save the query under any convenient name. And then create the form based on that query. Use the form wizard. Base the form on the Purchase Order table and then when asked to select fields select all from Purchase Order and then from the pull-down menu select your query and then select all of those fields. The wizard should then recognise that you want to develop what is known as a form with a sub-form. Continue with the wizard selecting Tabular as the layout for the sub-form. Select the Evergreen style and name your forms Purchase Order and Purchase Order Line Sub-form. If you opened the sub-form in design mode and looked at the Control Source property of the form you will see that instead of the query name you have something that starts SELECT DISTINCTROW. This is an SQL statement that represents what the query does. Therefore you can now delete the query.
If you opened the Purchase Order form you would see that you can move from purchase order to purchase order by using the record controls at the bottom of the form. These are quite small and inconvenient o you are going to replace them with much larger buttons. To do this, you will have to make a space at the bottom of the form for the buttons and then in the properties for the form set the Navigation Buttons to No. Using the Select a Command Button and draw one on the form. The Button Wizard should start and from Record Navigation select Go to First Record. Follow the rest of the wizard changing whatever values you feel best suits your system. Once the button is created, follow the same procedure for three more buttons: Go to Previous Record, Go to Next Record and Go to Last Record. You will also need another button with the Caption Main Menu that returns the user to the Main Menu. Once again just click Cancel once the wizard starts.
Word Count: 1610