1 / 96

Using Microsoft Office 2007 with Cimple.NET

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

Renfred
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using Microsoft Office 2007with Cimple.NET

  2. 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

  3. 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

  4. 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.

  5. Connecting to SQL Server: Step 1 • From the Start Menu, open the Control Panel.

  6. Connecting to SQL Server: Step 2 • Switch the Control Panel to Classic View (use the link on the left of the screen)

  7. Connecting to SQL Server: Step 3 • Open the Administrative Tools folder.

  8. Connecting to SQL Server: Step 4 • Under Administrative Tools, open the Data Sources (ODBC) folder.

  9. Connecting to SQL Server: Step 5 • Choose the System DSN tab and then click the “Add” button.

  10. Connecting to SQL Server: Step 6 • Select “SQL Server” to and then click “Finish” to create a new data source.

  11. 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.

  12. Connecting to SQL Server: Step 8 • Use the default authentication option (Windows NT authentication using network login ID). • Click “Next” to continue.

  13. 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.

  14. Connecting to SQL Server: Step 10 • Use the default SQL options. • Click “Finish” to complete the process of setting up your ODBC data source.

  15. 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.

  16. 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.

  17. Connecting to SQL Server: Step 13 • Now that the data source has been tested, click “OK” to continue.

  18. 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.

  19. Create an Access Database: Step 1 • Start Microsoft Access. • Double-click the option to create a new Blank Database.

  20. Create an Access Database: Step 2 • You will be prompted for a filename. • Enter the name “cimple” and then click the “Create” button.

  21. 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”.

  22. 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.

  23. Link to Cimple.NET: Step 1 • From the External Data tab, click the “More” button and then choose “ODBC Database”.

  24. Link to Cimple.NET: Step 2 • Select the option to “Link to the data source by creating a linked table” and then click “OK”.

  25. Link to Cimple.NET: Step 3 • Select the “Machine Data Source” tab and then choose the “cimple” data source. • Click “OK” to continue.

  26. Link to Cimple.NET: Step 4 • Highlight the “DATA_STUDENT” and the “DATA_STUDENT _CUSTOM” tables. • Click “OK” to continue.

  27. 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.

  28. 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”.

  29. Link to Cimple.NET: Step 7 • After renaming the table, press the “Enter” key to save the new name.

  30. 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.

  31. 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”.

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

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

  34. 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.

  35. 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.

  36. 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.

  37. Queries • Enter a name for your query. • Click “Finish” to create your query.

  38. 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.

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

  40. 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.

  41. 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.

  42. 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

  43. 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

  44. Queries: Adding a Field • Notice how the query changed • Also, notice the number of records that the query returned also changed

  45. 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

  46. 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.

  47. 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.

  48. 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

  49. 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*”

  50. 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)

More Related