Using microsoft office 2007 with cimple net
Download
1 / 96

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


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

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

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 2007with 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

  • 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

  • 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

  • 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

  • From the Start Menu, open the Control Panel.


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

  • Open the Administrative Tools folder.


Connecting to SQL Server: Step 4

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


Connecting to SQL Server: Step 5

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


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

  • 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

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

  • Click “Next” to continue.


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

  • Use the default SQL options.

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


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

  • 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

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


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

  • Start Microsoft Access.

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


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

  • 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

  • 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

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


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

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

  • Click “OK” to continue.


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

  • 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

  • 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

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


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

  • 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

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


Queries

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


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.


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.


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.


Queries

  • Enter a name for your query.

  • Click “Finish” to create your query.


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.


Queries

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


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.


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

  • 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 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 Field

  • Notice how the query changed

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


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

  • 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

  • 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

  • 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 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

  • 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

  • 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: Example

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


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: 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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: 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: Creating

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

  • Click “Next” to continue.


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: 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: 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: 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: 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: Creating

  • Finally, give the report a name.

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


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

  • Lets modify this report.

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


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: Modifying

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


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: Modifying

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


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: Creating

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

  • Click “Next” to continue


Labels: Creating

  • Select the font name, size, and weight

  • Click “Next” to continue


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: 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: Creating

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

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


Labels: Creating

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


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

  • 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

  • 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

  • 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 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 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 Merge

  • Click the Next button below to “Select Recipients”


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 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 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 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 Merge

  • You are now ready to print your letters

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


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

  • 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?


ad
  • Login