Hello,

I am a beginner using Access. I have viewed your Access 101 tutorial and applied each lesson. I currently have a "prototype version" of what my database would look like once I feel confident enough to implement it.

(I've been messing around with an old Access 2000 program)

My goal is to learn on Access 2000 then buy the latest release once I am confident enough to completely build my database.

I wanted to know which of your CDs would teach me to program the following features:

When printing mailing labels I want the program to recognize when a client is deceased and insert the clients spouse's name in their place; I also would like the salutation to change as well. The example below is for a Man named Bob Smith married to Jane Smith.

Example:

Current Label would look like:

M/M Bob Smith
123 Abby Road
Honolulu, HI 96813

(I already have a client deceased yes/no entry and a client's spouse deceased yes/no entry on my forms/tables)

When I check client (Bob) deceased box, I want the program to change mailing labels to read:

Ms Jane Smith
123 Abby Road
Honolulu, HI 96813

Also if Jane was to predecease Bob, I'd like the label to automatically change the salutation to read like:

Mr. Bob Smith
123 Abby Road
Honolulu, HI 96813

Of coarse if both are dead I would like NO label to be produced.

Which of your CDs would teach me this type of programming?

I'm assuming SQL lessons?

I have a few other things I want but I'll save those questions for another day. This will be a client/client information only database that will be used to track client data and create mailing labels for mass mailings.

Thank You,
Wayne K.

---

Richard Elaborates...

You could probably do this without any SQL programming. A regular query would handle it. Let's assume the following fields:

MrFirstName
MrLastName
MrDeceased
MrsFirstName
MrsLastName
MrsDeceased

You could use an IIF function inside a query to say:

Salutation: IIF(MrDeceased,"Mrs. " & MrsFirstName & " " & MrsLastName,IIF(MrsDeceased,"Mr. " & MrFirstName & " " & MrLastName,"Mr. & Mrs. " & MrFirstName & " " & MrsLastName))

You can put this statement into a query as a CALCULATED FIELD and it should provide the correct results.

If you were to break this down into a "conventional" programming language like Visual Basic, it would say:

IF MrDeceased = TRUE Then
  Salutation = "Mrs." & MrsFirstName & " " & MrsLastName
ELSE IF MrsDeceased = TRUE Then
  Salutation = "Mr. " & MrFirstName & " " & MrLastName
ELSE
  Salutation = "Mr. & Mrs. " & MrFirstName & " " & MrsLastName
END IF

This will give you the salutation. Now as far as NOT printing a label if BOTH people are deceased, I would just make another calculated field:

BothDeceased: IIF(MrDeceased AND MrsDeceased,TRUE,FALSE)

Now set the criteria for this field in your query to FALSE and then any TRUE records (where both people are deceased) will not be displayed.

As far as which lessons you'd need...

I cover Calculated Query Fields and the IIF function in Access 220.

So I would recommend at least everything up to that point. You've already taken 101, so you'd need 102 - 104 and 201 thru 220. You COULD skip 202 to 207, but I'd take them if you can.

Hope this helps.