2 Qs: Calculated Field & Composite Unique Index

1. Can I create a calculated field in Access 2000?

IOW I want a field whose value is automatically inserted based on the values
of 2 other fields.

For example, I want Field 3 to be the product of Field 1 and Field 2. So if
values of 5 and 2 are input to Fields 1 and Field 2, the value of 10 is
automatically inserted into Field 3.

Is this possible? If so how?

2. Is it possible to define a composite unique index in Access?

For example, I want Field 1 and Field 2 to have a unique index on them such
that the combination of values in these 2 fields must be distinct.

I can achieve this result by declaring a composite PK but in this case the
two fields are not PK yet in combination they should be unqiue.

Thank
Dave

1) There is no calculated/saved field in Access and there probably shouldn't
be. You can always create a query that calculates the value on-the-fly. You
can use code in the after update event of a form to place a calculated value
into a field.

2) You can create composite indexes by opening the index dialog and
selecting one or more fields and then giving the combination a single name.
You can set the index to be unique.

--
Duane Hookom
MS Access MVP


"David F" wrote in message
...
1. Can I create a calculated field in Access 2000?

IOW I want a field whose value is automatically inserted based on the
values
of 2 other fields.

For example, I want Field 3 to be the product of Field 1 and Field 2. So
if
values of 5 and 2 are input to Fields 1 and Field 2, the value of 10 is
automatically inserted into Field 3.

Is this possible? If so how?

2. Is it possible to define a composite unique index in Access?

For example, I want Field 1 and Field 2 to have a unique index on them
such
that the combination of values in these 2 fields must be distinct.

I can achieve this result by declaring a composite PK but in this case the
two fields are not PK yet in combination they should be unqiue.

Thank
Dave

On Sat, 5 Jun 2004 18:33:46 -0700, "David F" wrote:

1. Can I create a calculated field in Access 2000?

IOW I want a field whose value is automatically inserted based on the values
of 2 other fields.

For example, I want Field 3 to be the product of Field 1 and Field 2. So if
values of 5 and 2 are input to Fields 1 and Field 2, the value of 10 is
automatically inserted into Field 3.

Is this possible? If so how?

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

2. Is it possible to define a composite unique index in Access?

For example, I want Field 1 and Field 2 to have a unique index on them such
that the combination of values in these 2 fields must be distinct.

I can achieve this result by declaring a composite PK but in this case the
two fields are not PK yet in combination they should be unqiue.

Open the Table in design view; click the indexes icon (looks like
lightning hitting a datasheet). Type an index name in the left column
and select up to ten fields on succeeding rows of the right column;
specify that it is a unique index using the checkbox.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public