Custom Reports

a) The flexibility allowed by Prime for customizing and creating reports is enormous. If you need a report that is different from the many choices already offered in Prime, getting and displaying the information is easily accomplished. Before you create your report, ask yourself three questions

1) Is the data I am looking for gathered? (i.e., you cannot find out how many brown-eyed clients you have in Fulton County if you don’t track eye color in a field on the Client Intake form); and

2) Does this report already exist in Prime or is there one almost like it? If so can I make a copy and modify it?

3) Should my custom report be a Query, a Report or a Form?

Creating a Custom Query in Ten Steps

If you’d never done a custom query, Microsoft provides a wizard for constructing one. This will explain the steps, and then we will look at screens that follow these steps. 1) Startup Prime and close down all the open forms and menus. 2) Go to Ribbon Bar and choose Create. 3) On the second line that appears click on the Query Wizard. Let it remain on the Simple Query Wizard and click on OK.

4) You can then choose between a table and a query. i) Hint: when you get more advanced, try using another query as a record source. This allows you to have multiple tables in your report, such as information from both the ClientsW and the Eligibility tables. ii) For now Choose ClientsW as your table (this holds the intake sheet) 5) A box opens that allows you to select fields. i) Click on each field you want and click on the right facing arrow to move it into the box on the right. ii) For our example I chose these fields: Casenum, Clname, Cfname, Pcode, Snum, Dopen, Officenum, Dclosed and Fundsnum. iii) Click on Next 6) You now can choose Details or Summary. i) Summary is more advanced and you can experiment with this later. This can count, sum, average, etc. ii) Choose Detail for now. iii) Press Next 7) The Query asks for a name. You might name it _QMytest. i) The underscore will put it at the top of the list of queries. ii) The Q tags it as a query. iii) Test lets you know it was only a test. iv) Click on Modify the design v) Then Click on Finish 8) The query opens in the design view. i) Go to the grid and on the line that says Criteria i. Under the column marked Dopen put, Between 1/1/15 and 6/1/15 ii. Under the column marked Pcode, put 63 iii. This will give you all the landlord tenant cases opened in the beginning of 2015. ii) Go to the line called Sort i. Under the column marked Clname, Click in the box, push the button that appears and choose Ascending 9) Go to the top line Ribbon and click on Design i) Click on the Run report to see the results. ii) Right Click on the tab and choose Design View to go back to the design mode (Or choose Home from the top line ribbon bar and choose Design from the Views choice on the far left.) iii) Save your query again if you want to save your criteria. 10) To find and play back the Query i) Press F11 to open the navigation pane ii) If the Search bar isn’t there, right click and choose Search bar iii) Make sure All Access Objects is selected iv) Type in the name of the Query and tap enter v) When its name appears, double clicking on it will make it run, or vi) Right click on it and choose design to modify the design, etc.

An Alternate description for Creating a Query

A Query is the simplest way to get a custom row and column report. You will need the full version of Access. This method does not use the wizard mentioned above. 1) On the Ribbon Bar, go to Create Query Design 2) From the Show Table box, choose the Table you want to use

3) Choose which fields you want to see by double-clicking on them. As you choose the fields, you will see them drop down into the Grid

4) Indicate the Sort order and restricting Criteria, if desired 5) Then in the Design section, click on Run to get your resulting query

6) Choose Save from the top-line menu and name the query. Note: Begin the name with a Q if you intend to add it to the Report List table and see it on the report search screens. Add your office initials to make it easier to find later, e.g., QKCW_ReportOnFundingCodes.

7) Once you save your query, it will be listed under your list of Queries in Prime on the Navigation Pane. However it will not show up in the Prime Menus. The steps to get it to show in the menu is described later in this chapter.

Creating a Custom Report

The design mode of the Report Screen lets the user take records and group and total them. Again, you need to be in the full version of Access Note: You can add conditions when creating a Report or base it on a query you already created. 1) In Access, go to Create Report Wizard 2) Select the table or query you want to use from the pull down menu

3) Choose the fields you want to see from the Available Fields i) To select a field, double-click on it in Available Fields OR single click on it and then click on the single right arrow ii) To move all of the fields in, click on the double right arrows iii) To remove a field from the selected area, click on it to highlight it and then click on the single left arrow iv) To remove all of the fields from the selected area, click on the double left arrows

4) Add any groupings if necessary

5) You can sort records by up to four fields in ascending or descending order and then determine your layout and page orientation

6) Give your Report a title and then hit Finish to preview it

7) Choose Save from the top-line menu and name the report. Note: Begin the name with a R. If you want to find it easily, follow this naming convention, R + your offices letters + _ + the name of the report. Example: RKCW_OpenCasesByOffice

Creating a Custom Form

1) In Access, go to Create Form Wizard 2) Select the table or query you want to use from the pull down menu 3) Choose the fields you want to see from the available fields

4) Choose the layout for your form

5) Give your form a name and hit Finish to see your created Form. Note: Begin the name with an F. If you want to find it easily, follow this naming convention, F + your offices letters + _ + the name of the form. Example: FKCW_AgenciesByOffice