Teaching A Better Tomorrow

I worked with someone today on an Access Database that they use to generate a very complex report. They had been given the database to work with. She was very knowledgeable and could explain very clearly what she wanted...

Here is the problem she had, because the structure was designed in a "non-normalized" way she had trouble reporting...

I can't even begin to tell you the importance of setting up database table correctly. Yes, you can make it work for data, but when you report the inconsistencies and the redundancy will kill you.

This is for the PowerPoint users/presenters.If you are running the PowerPoint presentation (Pressing F5 will always run the show), and you get to a point where you need the full focus of the audience, then you press B or W and it will Black out or White out your screen.

Try it out! Shortcuts:

F5 - Runs the Show

B - Blacks the screen out

This blog will only be beneficial if I know what my readers want!!!

Please comment and tell me what information you want to see!

Reasons to use Crystal Reports*:

Write reports on all different types of databases, from Excel spreadsheets to Oracle databases. Built in Experts to help you quickly return summaries, filter data, and make easy to read reports.

Report distribution is extremely flexible with all the formats to choose from. Parameter reports that prompt you for criteria…gives you great flexibility in reports! Cross-tab (pivot) reports are easy to work with and format for great looking reports! Charting capability is extremely great and easier to work with than most programs. Very Powerful, fun to work with and will make you a better report writer period.

*Solely based on the opinion of Robin Cooper who has been teaching it, reporting with it since Version 6.

If you receive this error(It shows when you restart the computer):

application_executable_name - Illegal System DLL RelocationThe system DLL user32.dll was relocated in memory. The application will not run properly. The relocation occurred because the DLL C:WindowsSystem32Hhctrl.ocx occupied an address range reserved for Windows system DLLs. The vendor supplying the DLL should be contacted for a new DLL.

Check out this article for the fix...

This is a question that I have answered at least 1000 times in the past 7 years! It is a great one, it plagued me forever, until I learned about formatting characters! QUESTION: How do I delete the blank page at the end of a Word document?

Answer:

~~~THERE IS NO DELETE PAGE Function~~~!

The blank page occurs because of the hidden characters on the page! Hidden characters are hard or soft returns, spaces or tabs for example. You need to turn on the “show/hide” button on the standard toolbar. It looks like a Paragraph marker...looks similar to this: ¶ Place your cursor behind the characters on the blank page

Why does my VLOOKUP function work in some cells,

then not in other ones it is copied into?

~~~There is a hundred answers for this, this is typically what I find to be wrong~~~

The fix I seem to do the most on this question has to do with the way the formula was built! When you build a vlookup you are giving it several parts to work with so that it can evaluate and return a result. The problem typically occurs due to inappropriate referencing in the formula itself.

A VLOOKUP has these required parts and one optional argument: Lookup=the cell reference is it using as the value to match in the table array. Table Array= the table where is goes to find the match Column Index number= cell reference that contains the value you want displayed. The optional argument= True for an approximate match, or False for an exact match. =VLOOKUP('Tshirt Orders'!C3,ColorList!A1:B6,2,FALSE)  The Vlookup works fine until it hits a little snag…and you begin to see "#N/A" in the cells. This error occurs because the table array is not absolutely referenced. When it is then copied or auto filled, it updates the reference to the table array to row that it does not exist on!

How to correct this? You apply an absolute reference to the table array by click in the references, and pressing F4 (function key- F4). This updates your formula to read like this:

=VLOOKUP('Tshirt Orders'!C3,ColorList!$A$1:$B$6,2,FALSE) (tip: Click after the cell reference, then press F4) By applying absolute referencing you are telling Excel that when it copies the formula update the cell references every where, except the table where it is searching!

Thanks to a student, I came by this today in class! I am so mad, I have been looking at it for years and never thought to click it!!!

Wow, that subject line probably got some attention...What on earth do I mean? Do I mean stop using one of the most valuable communication tools we have? NO! I mean stop opening Microsoft Outlook* when you want to send word documents, excel spreadsheets, etc...as attachments!

Here is what you do...Open up Microsoft Word, type a short letter, save it or don't, then go to File-Send To-Mail Recipient as Attachment. This actually opens up an email message with the document attached, the subject line as the file name of the attachment, and all you do is address and send!

Before you ask...If you choose File-Send T0-Mail Recipient, it opens a new message and then it takes the text on the document and copies it into the body of the message. Then address and send.

Hope this saves you time!!! Try it out and tell me what you think...

Access 2003 shortcuts:

Change your view to form view from Design view: F5 If I am in form design, and then I press F5, then it swaps from design to form view.

Open the property sheet: Press F4. I can select an object, then press F4

In Crystal XI new Parameter Feature.

This tip allows you to color every other line of the details section to mimic the "older style printer paper" (Sorry James :) !) green and white paper.

Copy this statement into the section expert, background color condition button.

If RecordNumber Mod 2=1 then crgray else crwhite

Interested? If you currently use Outlook for any of your tracking reports then you really should try this tip out!

Items inside of any folder can exported to a CSV (Comma seperated value file), Microsoft Excel, and several others.

For your brave at hearts...Go to the File menu (in Outlook) and select Import and Export, follow the wizard. (Great for tracking appts, emails and tasks).

Sometime we get a data list that contains a combined name, ie Cooper, Robin. We want to split that long list as easy as possible. (image example below)

Excel 2007

Highlight the range, go to the Data tab and click "Text to Columns" button and follow the prompts. It is delimted by a comma.

Excel 2003

Highlight the range, go to the Data menu and select"Text to Columns" option and follow the prompts. It is delimted by a comma.

I love this one!

You can now expand the formula bar to read more of your complex calculations.

To expand hover your mouse on the border of the formula bar, when you see the double sided arrow, left click hold and drag it down.

When your done, double-click your border to fit it back.

My favorite keyboard shortcuts to teach:

Excel Shortcut to build Excel Charts: Highlight the data for the chart Then press function key "F11"

Windows Shortcut to Minimize All Windows: Hold the Windows Key “ÿ” then Press Letter “M”

Internet Explorer Shortcut to put in the “www.” And “.com” Click in the Address bar, Type name, Hold CTRL and Press Enter. Example: Type “google” then press CTRL + Enter to get www.google.com to display.

Outlook Shortcut to send email Once you are ready to send: CTRL + Enter will send the message.

Office Suite Shortcut to “Save As” Function Key “F12”

Shortcut to “Spell Check” Function Key “F7”

Shortcut to “Repeat Last Command” Function Key “F4”

Here is what you do:

Open your access database and then open your excel file.

Highlight your list data (that means it is column format and has headings typically)

Then you will hold the control key and drag it to the database window.

Why Hold Control?: Control while you drag produces a copy of the data.

Tip: Do not release until you in the database window. If you see a circle with a slash it won't let you drop it there.