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

Microsoft Access

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

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

Figure B

4. Clear the Show check box under the Balance field used as the criterion for the query.
5. 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.

Microsoft Word

Do you find yourself constantly retyping your contacts' addresses in your documents? Or do you often need to search your Outlook address book for an address and then have to copy and paste it from there? Let Word's Smart tags do the work for you. Follow these steps:

1. Type the recipient's name in your document and then press the Enter key.
2. Move your mouse over the name until the Smart Tag appears.
3. Click the Smart Tag drop down arrow and then click Insert Address (Figure A).

Figure A

Word automatically searches your Outlook address book and adds the client's address to your document.

If the Insert Address option does not appear on the Smart Tag shortcut menu, click Smart Tag Options. This will bring up the Smart Tag Options menu. Click the Person Name (Outlook E-mail Recipients) button and then click the Recheck document button (Figure B).

Figure B

Microsoft Excel

As in earlier releases, Excel 2007 lets you reference a formula by name. For example, say you use the worksheet shown in Figure A to calculate the average monthly sales for each of your products by Region. You would like your users to be able to calculate the Average Monthly Sales for each product anytime of the year, without having to create their own formulas.

Figure A

To set this up, follow these steps:

1. Click in cell B2.
2. On the Formula tab, click Define Name in the Define Name Group.
3. Click Define Name.
4. Click in the Name box and type AverageMonthlySales (Figure B).
5. Click in the Refers To box and enter =AVERAGE(B4:B15).
6. Click OK.

Figure B

To enter the named formula in row 2 of the worksheet, follow these steps:

1. Select cells B2:K2.
2. Click the Formula tab and then click Use In Formula in the Define Name Group (Figure C).

Figure C

1. Click AverageMonthlySales.
2. Press Ctrl + Enter (Figure D).

Figure D

You can define and add other operations to the Use In Formula menu ( such as Sum, MIN, or Max) that allow users to analyze their worksheets without having to write their own formulas.

Talkback 0 comments

• Service Delivery Manager Banking IT
• MIS Executive (ref: JN)
• Managermarket Risk Management (mrm) MIS Infrastructure
• MIS Officer
• MIS Supervisor

Cutting-edge technology from premium sponsors

Adaptive Threat Management

High Performance Security for your Distributed Enterprise

Achieve lower TCO, agility, risk mitigation, compliance, and productivity

Webcast series: Cybercrime is a growth industry - is your organisation prepared?

More resources to optimize the security of your enterprise »

Brought to you by:

 

 

 

More Tech Showcases:

Juniper Networks

Hitachi Eco-Products