Using Microsoft Office 2007 with Cimple.NET - PowerPoint PPT Presentation

Using microsoft office 2007 with cimple net l.jpg
Download
1 / 96

Using Microsoft Office 2007 with Cimple.NET. Microsoft Office 2007 and Cimple.NET. Cimple.NET provides a real-time link for Microsoft Office users This allows you to use any of the data from the Cimple.NET Centralized Database within Microsoft Office in real-time

Related searches for Using Microsoft Office 2007 with Cimple.NET

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

Download Presentation

Using Microsoft Office 2007 with Cimple.NET

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Using microsoft office 2007 with cimple net l.jpg

Using Microsoft Office 2007with Cimple.NET


Microsoft office 2007 and cimple net l.jpg

Microsoft Office 2007 and Cimple.NET

  • Cimple.NET provides a real-time link for Microsoft Office users

  • This allows you to use any of the data from the Cimple.NET Centralized Database within Microsoft Office in real-time

  • The Cimple.NET Student Information System automatically maintains the Centralized Database and ensures data integrity and validity, eliminating the need for knowledge on designing, creating, and maintaining creating complex SIS databases

  • When you create Queries, Reports, Labels, Charts, or Mail Merges in Microsoft Office, they automatically update as soon as a change is made in Cimple (i.e. real-time)

  • This is an extremely powerful feature that allows for complete customization and integration with Microsoft Office

  • NOTE: Real-time integration is available in Microsoft Office 2000 (running Service Pack 3) and Microsoft Office 2003. The features, functions, and wizards are extremely similar, regardless of the version of Microsoft Office that you use


Initial setup l.jpg

Initial Setup

  • Before you can use Microsoft Office 2007 with Cimple.NET, you must setup and configure your workstation to access your Cimple.NET database

  • If your school district uses a Microsoft Access Database for Cimple.NET, you can link to the tables directly

  • If your school district uses a Microsoft SQL Server, you must establish an ODBC data source


Connecting to sql server l.jpg

Connecting to SQL Server

  • First, establish an ODBC connection from your computer to the Cimple.NET SQL Server

  • Second, create a blank Microsoft Access database

  • Third, establish a link between your Microsoft Access database and the Cimple.NET SQL Server using the ODBC connection

  • Once these three steps are completed, your Microsoft Access database will become a real-time link to the Cimple.NET SQL Server. You can then use any data stored in the Cimple.NET SQL Server in Microsoft Office in real-time.


Connecting to sql server step 1 l.jpg

Connecting to SQL Server: Step 1

  • From the Start Menu, open the Control Panel.


Connecting to sql server step 2 l.jpg

Connecting to SQL Server: Step 2

  • Switch the Control Panel to Classic View (use the link on the left of the screen)


Connecting to sql server step 3 l.jpg

Connecting to SQL Server: Step 3

  • Open the Administrative Tools folder.


Connecting to sql server step 4 l.jpg

Connecting to SQL Server: Step 4

  • Under Administrative Tools, open the Data Sources (ODBC) folder.


Connecting to sql server step 5 l.jpg

Connecting to SQL Server: Step 5

  • Choose the System DSN tab and then click the “Add” button.


Connecting to sql server step 6 l.jpg

Connecting to SQL Server: Step 6

  • Select “SQL Server” to and then click “Finish” to create a new data source.


Connecting to sql server step 7 l.jpg

Connecting to SQL Server: Step 7

  • Enter the name “cimple” and then select the name of your Cimple.NET SQL Server.

  • Click “Next” to continue.


Connecting to sql server step 8 l.jpg

Connecting to SQL Server: Step 8

  • Use the default authentication option (Windows NT authentication using network login ID).

  • Click “Next” to continue.


Connecting to sql server step 9 l.jpg

Connecting to SQL Server: Step 9

  • Check the box labeled “Change the default database to” and then select the “cimple” database.

  • Click “Next” to continue.


Connecting to sql server step 10 l.jpg

Connecting to SQL Server: Step 10

  • Use the default SQL options.

  • Click “Finish” to complete the process of setting up your ODBC data source.


Connecting to sql server step 11 l.jpg

Connecting to SQL Server: Step 11

  • Click the “Test Data Source” button. This will confirm that the connection to the Cimple.NET SIS server is working correctly.


Connecting to sql server step 12 l.jpg

Connecting to SQL Server: Step 12

  • The system will show you the results of the test. It should say, “TESTS COMPLETED SUCCESSFULLY!”

  • Click “OK” to continue.


Connecting to sql server step 13 l.jpg

Connecting to SQL Server: Step 13

  • Now that the data source has been tested, click “OK” to continue.


Connecting to sql server step 14 l.jpg

Connecting to SQL Server: Step 14

  • The ODBC data source is now created. You should now see “cimple” SQL Server listed as a System Data Source.

  • Click “OK” and then close all windows.


Create an access database step 1 l.jpg

Create an Access Database: Step 1

  • Start Microsoft Access.

  • Double-click the option to create a new Blank Database.


Create an access database step 2 l.jpg

Create an Access Database: Step 2

  • You will be prompted for a filename.

  • Enter the name “cimple” and then click the “Create” button.


Create an access database step 3 l.jpg

Create an Access Database: Step 3

  • A blank database will be created. Microsoft Access will also create a blank table for you. This table is not needed.

  • Close the table named “Table1” by clicking the “X”.


Create an access database step 4 l.jpg

Create an Access Database: Step 4

  • Right-click the “Table1” blank table on the left and then choose “Delete” to delete the blank table.

  • You now have a blank Microsoft Access Database.


Link to cimple net step 1 l.jpg

Link to Cimple.NET: Step 1

  • From the External Data tab, click the “More” button and then choose “ODBC Database”.


Link to cimple net step 2 l.jpg

Link to Cimple.NET: Step 2

  • Select the option to “Link to the data source by creating a linked table” and then click “OK”.


Link to cimple net step 3 l.jpg

Link to Cimple.NET: Step 3

  • Select the “Machine Data Source” tab and then choose the “cimple” data source.

  • Click “OK” to continue.


Link to cimple net step 4 l.jpg

Link to Cimple.NET: Step 4

  • Highlight the “DATA_STUDENT” and the “DATA_STUDENT _CUSTOM” tables.

  • Click “OK” to continue.


Link to cimple net step 5 l.jpg

Link to Cimple.NET: Step 5

  • The tables that you selected will now appear as links. The icons should look like a globe with an arrow to the left.


Link to cimple net step 6 l.jpg

Link to Cimple.NET: Step 6

  • Remove the “dbo_” prefix of each table by renaming the linked tables.

  • To rename, right-click each table and choose “Rename”.


Link to cimple net step 7 l.jpg

Link to Cimple.NET: Step 7

  • After renaming the table, press the “Enter” key to save the new name.


Using microsoft office and cimple net l.jpg

Using Microsoft Office and Cimple.NET

  • You now have a Microsoft Access database that is linked to the Cimple.NET SQL Server.

  • This linked database file is stored in your “Documents” folder and is named “cimple”.

  • To access it in the future, simply double-click the “cimple” document to open it.

  • You can now use any Microsoft Office application (Word, Excel, Powerpoint, Access, etc.) to access any data stored in the Cimple.NET SQL Server.


Getting started using microsoft access queries l.jpg

Getting Started using Microsoft Access: Queries

  • Open the “cimple” Access database you created previously.

  • On the top of Navigation Pane, make sure “All Access Objects” is selected.

  • From the “Create” tab, choose the “Query Wizard”.


Queries l.jpg

Queries

  • Select the “Simple Query Wizard” and the click “OK”.


Queries33 l.jpg

Queries

  • From the “Tables/Queries” dropdown, select the “DATA_STUDENT” table.


Queries34 l.jpg

Queries

  • Make sure you always add the “SchoolYear” and the “Active” field to your query.

  • To locate the “Active” field, click in the “Available Fields” list and repeatedly press the “A” key on the keyboard until the Active field appears.

  • Tip: You can press the first letter of any field name on the keyboard to toggle through all fields that begin with that field name.


Queries35 l.jpg

Queries

  • Add any remaining fields that you would like in your query.

  • If desired, you can choose fields from a different data table.

  • When you are finished, click “Next” to continue.


Queries36 l.jpg

Queries

  • When prompted, select a “Detail” query. This will allow you to see all of the data in the fields that you selected.

  • Click “Next” to continue.


Queries37 l.jpg

Queries

  • Enter a name for your query.

  • Click “Finish” to create your query.


Queries38 l.jpg

Queries

  • The query that you created will display all of the fields you selected and the records that were located.

  • Notice how the query obtains records for multiple school years as well as Active and Inactive students.


Queries39 l.jpg

Queries

  • To specify additional parameters for your query, from the “Home” tab, click the “Design View” button.


Queries40 l.jpg

Queries

  • Once in “Design View”, locate the SchoolYear and Active columns.

  • In the first Criteria row, enter an “8” for the 2007-2008 school year and enter a “Y” for Active students only.

  • Then, click the “Datasheet View” button to view the modified query results.


Queries41 l.jpg

Queries

  • Notice how the query changed. Now, only active students in the 2007-2008 school appear.

  • It is important to always include the SchoolYear and the Active field in your queries because the Cimple.NET database maintains information on both active and inactive students for multiple school years.


Queries adding a field l.jpg

Queries: Adding a Field

  • If you forget to include a field when using the Query Wizard, you can always add it later.

  • Locate the field you wish to add in the table above (e.g. DATA_STUDENT).

  • Drag the field to one of the columns below to add the field to the query


Queries adding a field43 l.jpg

Queries: Adding a Field

  • The field should now appear in a new column

  • You can the specify any additional query criteria for this field in the Criteria box

  • Change your view to “Datasheet View” to view your modified query


Queries adding a field44 l.jpg

Queries: Adding a Field

  • Notice how the query changed

  • Also, notice the number of records that the query returned also changed


Queries moving deleting a field l.jpg

Queries: Moving/Deleting a Field

  • Notice the tiny bar that appears above the field name

  • To select a column, click this bar; when you do this, the entire column will highlight itself

  • By dragging this bar to a new location, you can change the order in which the columns appear in the query

  • If you highlight a column and then press the “Del” button on the keyboard, it will remove the field from the query completely


Queries sorting l.jpg

Queries: Sorting

  • From the “Design View”, select Ascending or Descending for each field you wish to sort.

  • Fields are sorted in the order they appear, from left to right.

  • To change the sort order, change the order in which the fields appear by moving the column to the desired location.


Queries saving l.jpg

Queries: Saving

  • To save a query, simply click the “Disk” icon in the upper left-hand corner.

  • If you forget to save, Microsoft Office will remind you.


Query criteria examples l.jpg

Query Criteria Examples

  • Is Null

    • Finds records having BLANK data

  • Is Not Null

    • Finds records having non-BLANK data

  • Between x And y

    • Finds records between two values “x” and “y”

    • Example: Between 5 and 27

  • > x

    • Finds records having a value greater than “x”, Example: > 5

  • >= x

    • Finds records having a value greaten than or equal to “x”, Example: >= 5

  • < x

    • Finds records having a value less than “x”, Example: < 5

  • <= x

    • Finds records having a value less than or equal to “x”, Example: <= 5


Query criteria examples49 l.jpg

Query Criteria Examples

  • <> x

    • Finds records that are not equal to “x”, Example: <> 5

  • In (x, y, …)

    • Finds records that are found IN a specified subset

    • Example: In (3,5,100)

      • Finds records having a field value of 3, 5, or 100

    • Example: In (Yellow, Blue)

      • Finds records having a field value of Yellow or Blue

  • Not In (x, y, …)

    • Find records that are NOT found in a specified subset

  • x Or y

    • Finds records that meet one of two sets of criteria, “x” or “y”

    • Example for Grade: 9 or 10

  • Like

    • Finds records that are similar to a particular string

    • Example: Find all students whose last name begins with the letter “B”, criteria: Like “B*”

    • Example: Find all students that live on “ANDERSON” street, criteria: Like “*ANDERSON*”


Date expressions l.jpg

Date Expressions

  • Use date expressions to query on a portion of a date field

  • Enter expressions in the “Field” box in a new column of a query

  • Month(Date)

    • Finds the month of a given date, e.g. Month(DOB)

    • If DOB = 9/15/96, then Month(DOB)=9

  • Day(Date)

    • Find the day of a given date, e.g. Day(DOB)

    • If DOB = 9/15/96, then Day(DOB)=15

  • Year(Date)

    • Find the year of a given date, e.g. Year(DOB)

    • If DOB = 9/15/96, then Year(DOB) = 1996

  • Age

    • To calculate a student’s age, use one of the following two expressions:

    • Current age (as of today): Int(DateDiff(“m”,[DOB],Now())/12)

    • Age as of 10/15/07: Int(DateDiff(“m”,[DOB],#10/15/07#)/12)


Date expressions example l.jpg

Date Expressions: Example

  • Below is a query in design view with the date expressions into separate columns

  • Notice after you type in the expression in the field box, the system automatically gives the expression a name (e.g. Expr1, Expr2, Expr3, etc.)


Date expressions example52 l.jpg

Date Expressions: Example

  • When you switch the query to “Datasheet” view, you will notice the field names are labeled Expr1, Expr2, Expr3, etc.


Date expressions renaming l.jpg

Date Expressions: Renaming

  • It is possible to change the default name that Access automatically assigns to an expression (i.e. Expr1, Expr2, Expr3)

  • In the Query “Design View” simply change the “Expr1” to a more meaningful name (see below)


Date expressions renaming54 l.jpg

Date Expressions: Renaming

  • Now when you switch the query to “Datasheet View”, the names of the columns will appear with the names that you assigned to each date expression


Renaming any field or expression l.jpg

Renaming any Field or Expression

  • To rename a field, enter the desired field name in front of the original name separated by a colon

  • For example, to rename the DOB field as DateOfBirth, enter the following in the “Field” box of the query column, example:

    • DateOfBirth: DOB

  • To rename an expression (e.g. Month(DOB)), do the following, example:

    • BirthMonth: Month(DOB)


Query parameters l.jpg

Query Parameters

  • Query parameters allow the user to specify the query criteria when viewing the query results instead of saving it as part of the query

  • Query parameters are created by entering a variable name in the criteria box inside square brackets [], e.g. [parameter]


Query parameters example l.jpg

Query Parameters: Example

  • Create a query to prompt for the Building Number

  • In the criteria box for the BuildingID, enter [Building #]

  • When you run the query, a window will appear and you will be prompted for the [Building #] each time you run the query

  • This will allow you to use the same query for multiple [Building #s] instead of creating multiple queries for each building


Queries reports copy and paste l.jpg

Queries/Reports: Copy and Paste

  • If you have a query or report that you wish to modify slightly for a different purpose, you can make a copy of the query or report using the standard Copy and Paste functions instead of recreating the query or report from scratch

  • To copy a query or report, right-click the item and choose Copy

  • Then, in the “white area” of the Navigation Pane, right-click and choose “Paste”


Additional query help l.jpg

Additional Query Help

  • Get online help from Microsoft Access

  • Click “?” button or press F1 when in Query Design Mode

  • Search for “Query” or “Query Criteria” for additional examples


Reports creating l.jpg

Reports: Creating

  • Once you create a query, you can use the Report Wizard to create a printer-friendly report.

  • With the query in “Datasheet View”, select the “Create” tab, then choose the “Report Wizard”.


Reports creating61 l.jpg

Reports: Creating

  • If you forgot to save your query, Microsoft Office will remind you. Click “Yes” to save your query.

  • Note that all of your queries and reports that you create are saved in your Microsoft Access “cimple” database file.


Reports creating62 l.jpg

Reports: Creating

  • Choose the fields from the query you created that you would like to appear on your report.

  • Click “Next” to continue.


Reports creating63 l.jpg

Reports: Creating

  • The Report Wizard will ask you if you would like to add any grouping levels.

  • If desired, choose one or two fields to group by. This will separate the data by the value of the fields you select.


Reports creating64 l.jpg

Reports: Creating

  • For example, if you group by the “Grade” field, then students in each grade level will be grouped together.

  • Here, we grouped by “Grade” and then by “Homeroom”.

  • Click “Next” to continue.


Reports creating65 l.jpg

Reports: Creating

  • If you wish your report to be sorted by certain fields, select those fields in the desired order.

  • Note that fields that you grouped by do NOT appear as a sort option. Groups are automatically sorted in ascending order.

  • Click “Next” to continue.


Reports creating66 l.jpg

Reports: Creating

  • Choose a report layout. When you select a different layout, a visualization of that layout appears on the left.

  • You can also change the orientation of the report, if desired.

  • Click “Next” to continue.


Reports creating67 l.jpg

Reports: Creating

  • Choose a report style. When you select a different style, a visualization of that style appears on the left.

  • Click “Next” to continue.


Reports creating68 l.jpg

Reports: Creating

  • Finally, give the report a name.

  • Click “Finish” to create your report and preview its contents.


Reports creating69 l.jpg

Reports: Creating

  • The report will appear in Print Preview mode.

  • Notice that some of the fields may not appear exactly as you would like.

  • In this example, the DOB field is truncated.


Reports modifying l.jpg

Reports: Modifying

  • Lets modify this report.

  • First, we have to click the “Close Print Preview” button.


Reports modifying71 l.jpg

Reports: Modifying

  • You are now in “Design View”.

  • Scroll to the right using the scrollbar at the bottom of the window to locate the DOB field.


Reports modifying72 l.jpg

Reports: Modifying

  • Click the DOB field in the “Detail” section, then drag the right edge of the field to make the field wider.


Reports modifying73 l.jpg

Reports: Modifying

  • Notice how the column header automatically expands and aligns to the new size of the DOB field.

  • From the “Home” tab, click the “View” dropdown button and choose “Print Preview” to view the report with the change you just made.


Reports modifying74 l.jpg

Reports: Modifying

  • Now the DOB field appears correctly on the printed report.


Labels creating l.jpg

Labels: Creating

  • To create labels, first create a query for your labels and then open the query in “Design View”

  • Then, from the “Create” tab, click the “Labels” button to start the Label Wizard


Labels creating76 l.jpg

Labels: Creating

  • Select the label size and manufacturer that you wish to use

  • Click “Next” to continue


Labels creating77 l.jpg

Labels: Creating

  • Select the font name, size, and weight

  • Click “Next” to continue


Labels creating78 l.jpg

Labels: Creating

  • Under the “Prototype Label” section, enter any text you would like to appear on the label

  • To add a data to the label, select a field from the left and then click the arrow; the field name will appear inside curly brackets

  • Additional formatting and layout options are available one the label is created (“Design View”)

  • Click “Next” to continue


Labels creating79 l.jpg

Labels: Creating

  • Select how you would like your labels to be sorted

  • The order in which you select the fields will determine the order in which the data is sorted

  • Click “Next” to continue


Labels creating80 l.jpg

Labels: Creating

  • Finally, give a name to the labels you just created

  • Click “Finish” to view your labels as they will be printed


Labels creating81 l.jpg

Labels: Creating

  • If you get this error message, just ignore it and click “OK” to continue


Labels creating82 l.jpg

Labels: Creating

  • The labels will appear in “Print Preview” mode

  • The labels you create will be listed as a Report in the Navigation Pane on the left


Labels previewing l.jpg

Labels: Previewing

  • NOTE: When you return to your labels, the default view is “Layout View”

  • In order to view your labels as they will be printed, you must change your view to “Print Preview”


Extracting data l.jpg

Extracting Data

  • To extract data, first create a query containing the data you would like

  • Then, right-click the query on the Navigation Pane and choose Export

  • Here, you can Export a variety of data files and formats, including Excel

  • NOTE: When you export a TEXT file, Microsoft Access will start the Text Export Wizard. Use the “Advanced” button to create export specifications (specs) that you can save and recall if you need to extract the data in the same format again


Creating a mail merge l.jpg

Creating a Mail Merge

  • To create a Mail Merge with Microsoft Word, first create a query containing your mail merge data (including addresses, if desired)

  • Then, Export the query and choose the option “Merge it with Microsoft Office Word”


Creating a mail merge86 l.jpg

Creating a Mail Merge

  • You will be asked if you wish to merge this data with an existing Word document or create a new Word document

  • If you already have a document (i.e. letter) you wish to use, select the “Link” option

  • If you would like to create a new document (i.e. letter), select the “New Document” option

  • Click OK to continue


Creating a mail merge87 l.jpg

Creating a Mail Merge

  • Here, we created a new Word Document to perform a mail merge

  • Microsoft Word will automatically start and the Mail Merge wizard will appear on the right

  • Select the type of Word document you wish to create (i.e. Letter), then click the “Next” link at the bottom of the Mail Merge Wizard


Creating a mail merge88 l.jpg

Creating a Mail Merge

  • Click the Next button below to “Select Recipients”


Creating a mail merge89 l.jpg

Creating a Mail Merge

  • Select “Use an Existing List” to use the data from the query you created in Microsoft Access

  • Click “Write your letter” to continue


Creating a mail merge90 l.jpg

Creating a Mail Merge

  • You can now begin creating your letter

  • To add data from your query, select the data field from the “Insert Merge Field” dropdown


Creating a mail merge91 l.jpg

Creating a Mail Merge

  • Here, we constructed a letter and added the information from the query

  • To preview your letters with the merged data, click “Preview your letters” at the bottom of the Mail Merge Wizard


Creating a mail merge92 l.jpg

Creating a Mail Merge

  • Your letter is now merged with your data

  • Use the buttons in the “Preview Results” section of the “Mailings” tab

  • The “Preview Results” button will toggle between displaying the student data and the data fields

  • Use the arrows to navigate between the different student records in your query

  • When you are ready to print, click the “Complete the Merge” step at the bottom of the Mail Merge Wizard


Creating a mail merge93 l.jpg

Creating a Mail Merge

  • You are now ready to print your letters

  • From the “Finish and Merge” button, select “Print Documents”


Opening a mail merge document l.jpg

Opening a Mail Merge Document

  • When you re-open your Mail Merge Document for future mailings, you will be prompted to execute your Access query.

  • Notice the name of the query in the message box that you created in Microsoft Access

  • Click “Yes” to run your query and open your document


Modifying your mail merge l.jpg

Modifying your Mail Merge

  • To make modifications to your Mail Merge document, go to the “Mailings” tab

  • You can then use the various options on this tab to make modifications to your Mail Merge, including returning the Mail Merge Wizard using the “Start Mail Merge” button and then selecting “Step by Step Mail Merge Wizard”


Questions l.jpg

Questions?


  • Login