| |
| |
|
Courses - Microsoft
Access 104 |
| Description: |
Using
Microsoft Access 3 |
| Running Time: |
70
minutes |
| Pre-Requisites: |
Access 103 very strongly recommended |
| Versions: |
|
We use Access XP in this course,
but the lessons are valid for all versions of Access from 95 to 2003.
There are cosmetic changes in Access 2007. Order before 3/15/2010 to
get a FREE upgrade to our 2007 version when released!
|
|
|
|
|
| |
 |
Microsoft Access 104
Using Microsoft Access
Creating a contact history
table, string concatenation, contact history report, more form and report
tricks. 70 Minutes. |
| |
|
AC104 Major Topics |
- Contact Management
System
- String Concatenation
- Report Headers
- Sorting & Grouping
- Page Numbers
|
In this course we're going to begin
building a Contact Management System. A “contact” is every
instance of communication with a customer – via phone, in person, email,
etc. We'll begin by building a table to store all of the contacts with
our customers.
Next, you'll build a contact query, so that
you can get - for example - a list of all contacts between two dates -
of course the user can type these dates in when the query runs using
parameters.
We'll learn about string concatenation - or
taking two text fields (like First Name and Last Name) and smashing them
together into one Full Name field.
Next we will make a contact report - showing all
of the contacts for each of our customers. We'll learn about the Can
Grow and Can Shrink properties in reports - which will allow
certain fields (like our notes fields) to grow to fit the data in them.
Then we'll learn about Sorting and Grouping
levels in reports - so that we can turn group headers and
footers on and display all of a particular customer's contacts
together in the same group.
Finally (with reports) you'll learn how to use the
Page and Report Headers and Footers to put a classy title
at the top of your report, and Page Numbers at the bottom of each
page.
We'll go back to our Main Menu and add buttons for
our new contact management features. We'll also create Tooltips
on them (you know - those little yellow text boxes that pop up to tell
you what a certain button does when you hover over it).
If you're interested in building your database
into a professional-quality contact manager, this is the course
for you. Say goodbye to your old Rolodex or Act! software. We're going
to make Access do everything for you! By the end of the next few
classes, you'll have something very powerful to use... and you can
customize it to do exactly what you want it to!
|
Access 104 Outline
|
l. Introduction
Welcome
Objectives
Pre-Requisites
Versions
Sample Database Files
2. Contact Table
What is a "Contact"
Using the customer notes field for contacts
Limitations
Creating the Contact
Duplicate data problem
Setting the default date with the Now() function
3. Contact Query
Listing contacts by date
Show only a specific date between two dates
Between keyword
Let the user enter the dates with a parameter
Creating Full Name field by merging two fields
String concatenation
Formatting a query field
Format property
4. Contact Report
Using the page header
Selecting objects using the ruler bar
Notice an & disappears in label
&& in Label
Adding a line object
Keeping your line straight
Text boxes with large amounts of data are truncated
Can Grow / Can Shrink textbox properties
Can Grow / Can Shrink section properties
Sorting and Grouping
Sorting in the report
Group Header & Footer
Keeping a group together on a page
Report header / footer
Adding page numbers to your reports
Showing total number of pages
5. Miscellaneous
Put contact report on Main Menu
Control Tip Text
6. Review
Review topics
Skills check
What's next? |
| |
|
Try a FREE Demo Lesson |
| |
| |
|
 |
Huge Discounts Available
When you purchase multiple classes together
Huge savings up to 50% off! Order Now. |
|
| |
Student Interaction:
Microsoft Access 104
|
Richard on 1/1/2007:
Creating a contact history table, string concatenation, contact history report, more form and report tricks. |
|
John Brandrick on 10/20/2007: Hi Richard,
In section 03 you show the perameters in the date/time field >=[StartDate] And<[EndDate] will this only work in the D/T field? I've tried using it with week numbers in a text field so we can search between>=[Week No And <[Week No]but it only asks for the first parameter then immediately opens the query I've also tried entering the Between [WK No]And[WK No]that does the same thing as well. what am I doing wrong
Thanks
JohnB |
|
Richard Rost on 10/20/2007: The problem, John, is that you're using the same parameter value twice. If you ask for [WeekNo] and then [WeekNo] again in the same query, Access will only prompt you for it once. Try [WeekNoBegin] and [WeekNoEnd] and that should work. In later classes, you'll see how you can do this with a form and text fields to supply your parameter values. |
|
Francisco Ponze on 11/8/2008: Richard, The Control Tip text does not work. Is any advice that you can give.
Thank you |
|
Richard Rost on 11/10/2008: Francisco, what do you mean when you say it does not work? Are you putting in a Control Tip and not seeing the popup text? What version of Access are you using? It *should* work. |
|
Francisco Ponze on 11/10/2008: I am using Access 2003, I fallowing you class and set the control tip text to show the text with the mouse on the button in Access 104. I also tested with the DB from the download and still does not show the text. First I went a select repair Access, but still witht the problem. That is why I send you an email perhaps another students have similar problem and have the solution.
I will try re-istall Access later and see that will resolve the problem. |
|
Richard Rost on 11/10/2008: Francisco, I tested this both with Access 2000 and 2003, and I've never had a problem. You're the first student (out of thousands) who has reported this problem. I want to help you figure it out, but I can't reproduce the problem on my end, so it's probably something on your PC. |
|
Francisco Ponze on 11/10/2008: Richard:
I tested in onther PC and it works, I re-install Office 2003 and still with the problem, It seem that is my PC it just 8 month old from DELL and using windows xp pro sp3. Any way I will setup another PC to test in case I have any other problems for the rest of the tutorials.
Thank you |
|
Tina on 12/5/2008: The instr function counts to the left. What if you had yyy@uu@iii how would you get the iii or how to get the instr to look at the 2nd @ |
|
Charles Brumagem on 1/12/2009: Richard I want to build a good knowledge tree program for the helpdesk that I run. I'm going to use a lot of ck boxes or radio buttons and on what is selected it will build a memo field of what was done and then I want to some how link it to the tickets system that we use called Mantis.I have read you history that you worked support and built tools. I sure you have some good thoughts on this can you let me know what you think. I'm going to take all your classes and I hope by the end I will be able to build and program more tools.
Keep up the good work! |
|
Fran Raymond on 4/9/2009: Hi Richard. I'm running some parameter queries. I have auditors that go out to our schools and i have dates that the auditors visit. I'm running parameter queries on the auditor names and dates. Everything is fine except that i get the parameter prompts TWICE - EVERY TIME (??). In the Auditor field i have
Like [enter auditor] &"*"
and in the AuditDate field i have
Between[enter start date] And [enter end date]
Is it some sort of glitch in my system that gives me each prompt twice or is there something wrong with my code? |
|
Richard Rost on 4/9/2009: Fran, it sounds like you have the parameter set up twice. Is the query perhaps pulling on another query that also asks for the same parameter?
You might want to try using a FORM FIELD to provide the parameter. It's a little more complex, but it works much better. Here is a tutorial that explains how:
http://599cd.com/tips/access/forms-formname-field-notation
You can literally set up a small form with ONE field in it (the parameter) and then make a command button to launch the query, which gets the parameter from your form. Neat, huh? |
|
Judith Koester on 4/27/2009: I am designating a query of projects before 2008 which works fine, but I want to include some years that are blank. |
|
Richard Rost on 4/28/2009: Judith, you'll have to say:
<2008 OR Is Null |
|
Nathan Wittmann on 8/13/2009: I have an address that has and address line 1 and and address line 2. I currently have them in a group header and have the header set to "can grow/shrink" to yes and the textboxes grow/shrink to yes and it will not shrink if there is not data in the address line 2. It does do it if I move them to detail section, but not if I have them in the group header or any header i put it in. Any Ideas? |
|
Richard Rost on 8/15/2009: That's very odd. Do you have any other controls NEXT TO (left and right side) the text boxes? For example, LABELS cannot grow or shrink, so if you have a matching label next to the address text box, then it will prevent the shrinking. |
|
Nathan Wittmann on 8/17/2009: Actually I had some objects to the right and when I moved them it worked. Unfortunately my report looks odd now with a large gap to the right of my address, but I think it looks better then having a gap in my address. Thanks for your help! |
|
|
| |
| |
|
You may want to read these articles from the 599CD Blog: |
|
| |
|
 |
| |
| Do you have
questions about
Word, Excel, Access, Web Design, or computers in general? Just
ask us anything you'd like.
Click here
for assistance. |
|
|
|
|
|
| |
|
CLICK HERE for a FREE
lesson |
Order
your first 599CD course now.
Your Satisfaction is Guaranteed!
|
|
|
|
|
|
| |
|
|
|
|
