If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Calculated Field & Composite Unique Index
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 |
#3
|
|||
|
|||
2 Qs: Calculated Field & Composite Unique Index
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 |
Thread Tools | |
Display Modes | |
|
|