Using values of a lookup in a calculation

[Chris]

Hi,

In table1, I want to have a lookup performed on Table2 Which would return
the value of a field plus the value of two checkboxes (yes/no). If the value
of the checkbox is a yes, I want to set Field2.Table1 = Field1.Table1.

Any help?
Thanks
Chris

 

[John Vinson]

Hi,

In table1, I want to have a lookup performed on Table2 Which would return
the value of a field plus the value of two checkboxes (yes/no). If the value
of the checkbox is a yes, I want to set Field2.Table1 = Field1.Table1.

You cannot and should not do this in a Table. For that matter, you
need to provide more information if you want to do it in a Query.
Table1 might have tens of thousands of records; Table2 might have
millions. Which record in Table1 is linked to which record in Table2?
Which table contains the yes/no field (tables do not contain
checkboxes; a checkbox is not data, it's a data display tool)?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Chris]

Well,
This would be used in a form to do data entry into a table. Table 2
currently has about 15 records and would probably never go past 100 or so
records.

Basically I want to have a value entered into a field (= to that of another
field) IF a field for a given record in table 2 is TRUE.

 

[John Vinson]

Well,
This would be used in a form to do data entry into a table. Table 2
currently has about 15 records and would probably never go past 100 or so
records.

Basically I want to have a value entered into a field (= to that of another
field) IF a field for a given record in table 2 is TRUE.

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.

Store the data once. Then use a query to look it up.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps