August 20, 2008

Use Access Query Totals to obtain a list of your customers’ recent activity

  • Date: August 12th, 2008
  • Author: Mary Ann Richardson

When you need to round up data on when your customers last placed an order, an Access query can do the trick. Here’s a quick walk-through that shows you how.


Orders have fallen in the last month and you are wondering whether you should send out another promotion. You decide to send one promotion to customers who have ordered recently and another to those customers whose last order was more than a month ago. Customers who haven’t ordered in six months will be excluded from the mailing. To find out when each customer placed their last order, follow these steps:

  1. In the Database window, under Objects, click Queries and then click the New button. Click Design View and then click OK. (In Word 2007, click the Create tab and then click Query Design in the Other group.)
  2. Double-click the Orders table.
  3. Double-click the CustomerID, CompanyName, and OrderDate fields from the field lists (Figure A).

Figure A

  1. Right-click the Query grid and select Totals (Figure B).

Figure B

  1. Click the drop-down arrow of the Totals cell under the OrderDate field and select Last (Figure C).

Figure C

    propecia merck

  1. Click Run.

The query Datasheet view lists each customer’s name and last order date.

Permalink • Print • Comment

August 18, 2008

Fine-tune your Access queries to remove duplicate entries from your mailing lists

  • Date: August 4th, 2008
  • Author: Mary Ann Richardson

If you query your Access database to generate a mailing list, but the list contains duplicate names and addresses, this helpful trick will save you some time. See how tweaking the query properties can ensure a list with unique entries.


You’ll get more mileage out your advertising dollar if you eliminate duplicate mailings to the same address. One way to do this is to use query properties to remove duplicates from your mailing lists.

For example, say you just performed a query on your Orders database to obtain the names and addresses of all customers whose order balance is typically below $100. You would like to send them a special coupon for new orders totaling more than $100. A quick glance at the data indicates that there are a number of duplicates in the list. (There is more than one order per customer.)

Rather than spend time removing the duplicates one by one, rerun the query with the Unique Values property set to Yes. Follow these steps:

  1. Open the query in Design mode.
  2. Right-click the query design grid and select Properties (Figure A).

Figure A

  1. Click in the Unique Values property box and select Yes (Figure B).

Figure B

propecia indications width=”500″ height=”381″ />

  1. Clear the Show check box under the Balance field used as the criterion for the query.
  2. Click Run.

The query should now list only one record for each customer. Be sure to clear the Show check box under any criteria fields; otherwise, it will override the Unique Values property setting.

Permalink • Print • Comment

How to reference an Access subform

One of the most common mistakes I see, and hear about it, is improper syntax when referencing controls on a subform. As far as Access is concerned, a subform is just another control on the main form — and that’s what confuses folks.

First, let’s review referencing controls in general. Use either of the following syntax statements to reference a control on a main form:

Forms!formname!controlname

Me!controlname

(In more recent versions, you can substitute bang (!) with dot (.) between objects.)

To refer to a subform or a control on a subform, you must remember that Access treats the subform as a control. Essentially, you have a form with a control with a control. To express that arrangement in terms Access can decipher, you need the Form property as follows

Forms!mainform!subform.Form.controlonsubform

Me!subform.Form.controlonsubform

In other words, subform is simply a control on the main form.

What I commonly see is a simple transposition of the Form property and subform, which generates a runtime error:

Forms!mainform.Form.subform.controlonsubform

In this form, Access assumes Form is a control. When it can’t find a control named form, Access returns an error.

Permalink • Print • Comment

Visually group Access data with lines, rectangles, and back color

  • Date: July 29th, 2008
  • Author: Mary Ann Richardson

With a few simple formatting tweaks, you can make your Access reports far easier to read and interpret.


You can make your Access reports easier to read by adding controls, such as lines and rectangles, and using back color to group records visually. For example, say you have a report that lists each customer’s order balance. To make it easier to interpret, you have grouped the customers according to their assigned Intern and included a Total Balance for each group. However, as you can see in Figure A, the results are difficult to read.

Figure A

To improve this situation, follow these steps:

propecia generic version

  1. Open the report in Design view.
  2. Click the Line tool.
  3. Press Shift and click and drag to create the vertical line to separate the columns for each group.
  4. Click the Rectangle tool.
  5. Click and drag to draw a rectangle around the Balance Total field in the report footer (Figure B).

Figure B

  1. Click in the Back Style box of the Rectangle Property Sheet and select Transparent (Figure C).

Figure C

  1. Click the Detail bar to select the Detail Report section.
  2. Click in the Back Color box of the Detail property sheet and click the Build button.
  3. Click a light blue color swatch (Figure D).

Figure D

Now when you run the report, the added controls visually organize the data to make it much easier to read, as shown in Figure E.

Figure E

Permalink • Print • Comment

June 21, 2008

Make it easy for users to change entries made to Access forms

Date: June 16th, 2008

Author: Mary Ann Richardson

You can make it easier for users to update records by adding an Undo button to a form. The Undo button will allow them to cancel changes made to a record without is generic propecia just as good having to go back and retype or delete entries made to a field. Follow these steps to add an Undo button to a form:

  1. Open the form in Design view.
  2. Right-click any toolbar and click Toolbox. Click to activate the Control Wizards button, if necessary, and then click the Command button tool. (In Word 2007, in the Form Design Tools tab, click to activate the Use Control Wizards button in the Controls group, if necessary, and click the Command button.
  3. Click and drag in the form where you want to locate the command button.
  4. Under Categories, click Record Operations.
  5. Under Actions, click Undo Record.

  1. Click Next.
  2. Click Next.
  3. Enter cmdUndoRecord.
  4. Click Finish.
Permalink • Print • Comment
« Previous PageNext Page »
Made with WordPress and an easy to use WordPress theme • Sky Gold skin by Denis de Bernardy