When implementing the database “Pharmacy” using Microsoft Access, 7 tables were created:
6. Units of measurement;
7. Storage locations.
The first three tables are created as the main entities of the subject area. The entities “Manufacturers”, “Suppliers”, “Measurement units”, “Storage locations” are attributes of the main entities, but they are placed in separate tables in order to create drop-down lists when filling in the main tables.
Let’s describe the main tables and their attributes:
Medicines have the following attributes:
– drug identifier (key field);
– name of the medicine;
– a brief annotation;
– company manufacturer;
– arrival identifier (key field);
– a drug;
– receipt date;
– the supplier;
– purchase price.
– expense identifier (key field);
– a drug;
– implementation date;
– selling price.
The data entered in the tables are reflected in Appendix A.
Queries are used to view, modify, and analyze data. Queries can also be used as record sources for forms, reports, and data access pages. There are several types of queries in Microsoft Access.
· Requests for selection. A select query is the most commonly used query type. Queries of this type return data from one or more tables and display it as a table whose records can be updated (with some restrictions). Select queries can also be used to group records and calculate sums, averages, count records, and find other types of totals.
· Queries with parameters. A parameterized query is a query that, when executed, displays in its own dialog box a prompt to enter data, such as a condition (Condition: A constraint that is set on the selection of records to include in the result set of records of a query or filter.) to return records or a value to be inserted into field. You can design a query that prompts you to enter multiple items of data, such as two dates. Microsoft Access can then return all records that fall between those dates.
· Cross requests. Cross queries are used to calculate and present data in a structure that facilitates their analysis. A cross query calculates the sum, average, number of values, or performs other statistical calculations, after which the results are grouped in a table across two sets of data, one of which defines the column headings and the other the row headings.
· Change requests. A change request is a request that changes or moves multiple records in one operation. There are four types of change requests.
– To delete an entry.
– To update the record.
– To add records.
– To create a table.
As part of this course work, the task was to create the following queries in design mode based on the combined tables:
– requests for selection (selection of symbolic data, numeric, use of masks);
– final requests;
– request with a parameter;
– cross requests.
A selection request was organized by the value of the text field, which displays information on the arrival of the drug “5-NOC”. The query result is shown in Fig. 1.2.
Fig.1.2 – Query to the database by the value of the test field
A query to the database by the value of one first character allows you to get information on the arrival of all drugs whose names begin with the letter “M” (Fig. 1.3).
Fig.1.3 – Query to the database by the value of the test field
In the course work, a query was also organized to the database “Pharmacy” by the value of the numerical field. This request allows you to get information about drugs received by the pharmacy in the amount of “10” size units. The result of this query is shown in Fig. 1.4.
Fig.1.4 – Query to the database by the value of the numeric field
A query with a parameter, organized to the “Pharmacy” database, allows you to get information about the arrivals of all drugs in a user-specified time interval. When this request is called for execution, the “Enter parameter value” dialog box appears (Fig. 1.5), in which you must sequentially enter the start and then the end date of the time interval of interest to the user. The result of the query with the parameter, the time interval for which is 01.01.2010-05.01.2010, is shown in fig. 1.6.
Fig.1.5 – Parameter entry dialog box
Fig.1.6 – The result of the query with the parameter
As part of this course work, a query was created that demonstrates the operation of the data grouping operation using the sum () function and the use of the expression builder (Figure 1.7). This query allows you to determine the amount from the sale of each drug by date of sale. To display the specified amount, a new field was created with the title “Amount”, calculated as the expression “[Quantity of goods sold] ∙ [Selling price per piece]”. This query uses data sorting (in ascending order) by name.
Fig.1.7 – The result of the query using the expression builder and data grouping
A cross-query was implemented to the “Pharmacy” database (Fig. 1.8), which reflects the number of medicines sold per day by date.
Fig. 1.8 – Cross query: Sales of drugs by dates
To work with the database, a query was created from the combined tables (“Drugs” and “Realization”) – fig. 1.9-1.10.
Fig.1.9 – Formation of a query from joined tables
Rice. 1.10 – The result of the query from the joined tables
Access provides the ability to enter data both directly into a table and using forms. The form in the database is a structured window that can be presented so that it repeats the form of the form. Forms are created from a set of individual controls.
The appearance of the form is selected depending on the purpose for which it is created. Access forms allow you to do tasks that you can’t do in Datasheet view. Forms allow you to calculate values and display the result. The data source for a form is the table or query records.
The form provides options for:
entering and viewing database information,
creating diagrams, messages, etc.
There are different ways to create forms in Access:
· Form constructor (designed to create forms of any complexity).
· Master forms (allows you to create forms different both in style and content).
· Autoform: in a column (multi-page – fields for a record are displayed in one column, the form simultaneously displays data for one record).
Autoform: tape (all fields of the record are displayed in one line, all records are displayed in the form)
· Autoform: tabular (records are displayed in the table mode).
· AutoForm: pivot table.
· AutoForm: PivotChart.
· Chart (a form is created with a chart built by Microsoft Graph).
· Pivot table (an Access form is created and displayed in Excel mode).
Using the Access toolkit for the table, tape autoforms have been created that allow you to easily add new records. On fig. 1.11.one of the tape autoforms is presented – “Medicines”. In total, 7 similar autoforms were created, for each of the “Pharmacy” database table. To create the “Medicines” autoform, the WotArt graphic toolkit was used, which made it possible to create a colorful inscription “Medicines” (Fig. 1.11).
Rice. 1.11 – An example of a ribbon autoform for the “Drugs” table
Ribbon autoforms were also created for queries organized to the Pharmacy database, for example, for a query with a parameter (Fig. 1.12).
Rice. 1.12 – An example of a ribbon autoform for a request with a parameter
Four button forms were created in the design mode and form wizard to facilitate navigation through the “Pharmacy” database. The main pushbutton form of the “Pharmacy” database (Fig. 1.13) provides access to the main elements of the database – directories, information on the receipt and sale of goods, completed requests, reports, and a revenue chart.
Rice. 1.13 – Form “Pharmacy”
Using the Microsoft Access graphical tools, the pharmacy logo has been added to the form. The form “Pharmacy” also reflects information about the current date and the rest of the days until the beginning of the third quarter of 2010. This dynamic information is implemented using the built-in functions of the Microsoft Access DBMS – Date() and DateDiff(). The Date() function returns the value of the current date to the field, and the built-in DateDiff() function returns the time interval between the specified time intervals.
By pressing the button directories, the form “References” appears (Fig. 1.14). To design this form, the graphical capabilities of Microsoft Access were also used: a picture and an inscription in the “WordArt” style were added. The reference form provides navigation with the tables “Drugs”, “Suppliers”, “Storage Locations”, “Manufacturers”. By clicking on the buttons of the same name, forms will appear for the specified tables, allowing you to enter data.
Rice. 1.14 – Form “Reference books”
The “Requests” form (Fig. 1.15), which is available by pressing the button of the same name on the main “Pharmacy” form, provides quick and convenient access to queries implemented to the database as part of this course work.
Rice. 1.15 – Form “Requests”
Access to reports is provided by the “Reports” form (Fig. 1.16).
Rice. 1.16 – Form “Reports”
From the “Pharmacy” form, the “Revenue from sales” form is also available (Fig. 1.17), which in the form of a diagram displays information on the total amount of revenue by date.
Rice. 1.17 – Diagram “Proceeds from sales”
A report is a formatted representation of data that is displayed, printed, or filed. They allow you to extract the necessary information from the database and present it in a form that is convenient for perception, and also provide ample opportunities for generalizing and analyzing data.
When printing tables and queries, information is issued almost in the form in which it is stored. Often there is a need to present data in the form of reports that are traditional and easy to read. A detailed report includes all information from a table or query, but contains headers and is paginated with headers and footers.
Microsoft Access displays data from a query or table in a report, adding text elements to it that make it easier to read.
These elements include:
· Title. This section only prints at the top of the first page of the report. Used to display data, such as the text of a report title, a date, or a stating part of the document text, that should be printed once at the beginning of the report.
· Page header. Used to display data such as column headings, dates, or page numbers printed on top of each report page. To add or remove a header, select Headers and Footers from the View menu. Microsoft Access adds the header and footer at the same time. To hide one of the headers and footers, set its Height property to 0.
· The main text of the report is the data area located between the header and footer of the page. This section displays the data that is printed for each of the records in the table or query on which the report is based. To place controls in the data area, a list of fields and a panel of elements are used.
· Footer. This section appears at the bottom of every page. Used to display data such as totals, dates, or page numbers printed at the bottom of each report page.
· Note. Used to display data such as a conclusion text, grand totals, or a signature that should be printed once at the end of the report. Although the Note section of the report is at the bottom of the report in Design view, it prints above the page footer on the last page of the report.
As part of this course work, three reports were created: “Medicines”, “Receipts of medicines”, “Units of measurement” (Fig. 1.18-1.19). Reports are available from the “Reports” form, called by pressing the “Reports” button from the main “Pharmacy” form. The reports “Drugs” and “Units of measure” are created according to the corresponding tables and without additional changes and display the data contained in them. In the “Receipt of medicines” report, an additional calculated field “Lot cost” has been created, calculated as [Purchase price]*[Quantity]. When creating reports, the font size, color of margins and borders were changed, the pharmacy logo was added. A fragment of the report “Receipt of medicines” is shown in fig. 1.20.
Rice. 1.18 – Report “Units of measurement”
Rice. 1.19 – Fragment of the report “Drugs”
Rice. 1.20 – Fragment of the report “Receipt of medicines”
In a business or personal environment, it is often necessary to work with data from different sources, each of which is associated with a specific type of activity. To coordinate all this data, certain knowledge and organizational skills are required. Microsoft Access combines information from different sources into one relational database. Created forms, queries and reports allow you to quickly and efficiently update data, get answers to questions, search for the right data, analyze data, print reports, charts, etc.
The developed database “Pharmacy” allows you to quickly and efficiently work with the data of this subject area. The user-friendly interface of the program, on the one hand, makes it easy to navigate the program without requiring the user to have any special skills in working with electronic computers, on the other hand, it provides the user with prompt information about the cost of medicines, income, consumption, etc. As already It was noted above that this database is educational and does not cover the entire business logic of the pharmacy business. However, it is a prototype demonstrating work in this industry. This database can be expanded to automate concepts that have not been considered within the framework of this course work in the subject area “Pharmacy”.