Advanced Topics in Access

1. Advanced Topics in Access How to create forms and reports Andrew Yu 03/04/2002

2. Major Objects in Access • Table  - The "Heart" of a database; Tables store all the information that will be managed by the user. • Query - Allows the user to ask questions and extract data from Tables or other Queries. • Form - Electronic forms that enter data directy in the table; convenient data entry. • Report - Prints or previews data in a pleasant/stylish format.

3. Why Forms • Looks Nice: Tables are where you will spend time designing the functionality of your database, but forms are where you will spend a lot of time, especially in the beginning, to design the face of your database. • Enter Data: There are two ways to enter information in to the database: from datasheet view and from forms. Data entry forms offer a more user-friendly interface by adding labels for each field and other helpful information.

4. View Data: There is the datasheet view in the Table area. With this option, you can see several records at one time, depending on the size of the screen. The other option is using a Form. Forms allow users to view one record at a time.

5. Forms • It is important to understand that all information that you enter into a form will actually be entered into the table that the form gets its fields from. Conversely, all of the data that appears in the forms, comes from the respective tables.

6. Create Forms • In this example, we will create a simple data entry form for the Rawdata table. • To begin the process, click on the Forms tab on the Access main screen.

7. Create A Form • click on the New button to create a new form. • Choose “From Wizard”. This is always a good try when you are a beginner.

8. Create A Form

9. Create A Form • Click and add all the fields into your form.

10. Create A Form • Then, click “Next”. • Select your favorite style.

11. Create A Form • Type in “RawDataForm” and select “Modify the form’s design”.

12. Create A Form • After “Finish”. • Change the form in the design view the desirable way it looks. • Drag the boxes, to move it and resize it appropriately. • Any time, you can have a look at what we've done so far. Click to change to the form view, and click back to design view, where you work on.

13. Create A Form

14. Add A Title • Move the mouse pointer to the edge of the ‘Detail’ area and looks like • Then drag down so you make some space for Form Header. • To create a title, in the toolbox, click • It changes to , then add a box in the header area. Type in the title of the form, say “Raw Data Entry Form”. • Change the font, color and background.

15. Remember: You can design the form the way you want and you might get higher points here, if you can show your reasonable creativity and effort.

16. You could stop here, but in order for data entry more convenient and accurate, you have a lot to do. • Click the Save button or select File, Save from the menu. • Now, drug is a three-level categorical field, we don’t want to type the name every time. In stead, we want to create a drop-down list and choose the drug when we enter the value.

17. Creating a Combo Box for the Categories • In the Toolbox, click the button. The mouse pointer will change to . • Click once somewhere in a blank area.

18. Combo Box • “Next”, then select the MinEffDose table as where your category values are provide. • “Next” again, select ‘Drug’.

19. Following the instruction and read, “Next” and “Next” again, • Store the value in the field ‘Drug’.

20. Using List Box for Gender • Label your combo box as “Select A Drug”, and Finish. • We will do similar thing for gender field, which only take value ‘M’ or ‘F’, but with “List Box” instead of Combo Box. • Select “I will type in the values I want”. • Type like this: • Then

21. List Box • Then • Label that as gender • Look at how it looks in the form view.

22. In design view, delete the original duplicate boxes for drug and gender, and replace with what you just created. • Add something useful and click command botton • Add whatever you want and think as useful.

23. Command Botton • I add “Print Current Form” botton, In order to print current form. • I think to find a record is important too. Then I add a second command botton

24. Now, you can view your form and records • Change the updated information, or type in new record. But whenever you change the information, the new information will also be updated in the original RawData form. • Save the form again.

25. A New Form: AddressBook • Build a form based on an existing table is always a good idea. • In table tab, click “New”. • We use ‘Table Wizard’ to call some existing some elements from table templates. • In MailingList table, select Names, address, phone, DOB, hobbies, etc.

26. Rename Middle Name as ‘M.I.’, PostalCode as ‘ZIP’.

27. Name this table as ‘AddressBook’ and let it to the primary key. • Not relate to any other table, and ‘Next’. • Check ‘Enter data directly into the table’, and the ‘Finish’.

28. Change to table design view, insert a row named ‘group’, select data type as number • In the field ZIP, look at ‘General’ tab below, change the input mask as ‘00000’ only. This is your input format. • Save the new table.

29. A Entry Form for AddressBook • Create a Form to input the information of your group members into this table. You have to do this on your own. • You don’t have to type the real private information except the name, email address and group number. • Another homework for you is to create a report including all the information in address book on a single paper.