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:

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.

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:

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.

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.

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:

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

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. Click Next.
2. Click Next.
3. Enter cmdUndoRecord.
4. Click Finish.