Help with complex lookup field

[PetroChris]

I've mastered adding simple lookup fields both manually and with the wizard.
However, I have a complex query that I want use as a lookup and I can't
figure out how.

Database setup...
* Parent table "Logs" with key fields "Well_ID" and "Tool_Order". "Tool_ID"
is a field in this table (must be entered).
* Daughter table "Log_Meas" with key fields "Well_ID", "Tool_Order", and
"Meas_ID"
* Table "Tool_Meas" with key fields "Tool_ID" and "Meas_ID" that lists the
measurements that can be made by each tool.
* Table "Measurements" with key field "Meas_ID" and field "Meas_Type" that
contains the desired lookup value.

Problem statement...

When working in daughter table "Log_Meas", I would like to add a lookup up
field for "Meas_ID" that look-ups values of "Meas_Type" from table
“Measurementsâ€, but limits the list to only those "Meas_ID" values in the
table "Tool_Meas" where "Tool_Meas.Tool_ID" equals the field value
"Logs.Tool_ID" in the associated parent table record.

I have created a query that performs this operation, by “hard-coding†in the
value of “Tool_ID†to “2†(set in Query field Criteria). What I can’t figure
out is how to use a variable query criteria for Tool_ID, that changes as the
selected parent table record, and associated value of Logs.Tool_ID changes.

Help!

 

[John Vinson]

I've mastered adding simple lookup fields both manually and with the wizard.

Well... unfortunately, that's a skill that may not get you too far.
Most developers disdain the Lookup Wizard and the use of lookup fields
altogether: see

http://www.mvps.org/access/lookupfields.htm

for a critique. Using lookup TABLES is fine and routine; the objection
is to using them in Tables, rather than on forms where they belong.
Table datasheets should NOT be used for data entry or editing; they're
just too limited in functionality, as you are discovering!

However, I have a complex query that I want use as a lookup and I can't
figure out how.

Database setup...
* Parent table "Logs" with key fields "Well_ID" and "Tool_Order". "Tool_ID"
is a field in this table (must be entered).
* Daughter table "Log_Meas" with key fields "Well_ID", "Tool_Order", and
"Meas_ID"
* Table "Tool_Meas" with key fields "Tool_ID" and "Meas_ID" that lists the
measurements that can be made by each tool.
* Table "Measurements" with key field "Meas_ID" and field "Meas_Type" that
contains the desired lookup value.

Problem statement...

When working in daughter table "Log_Meas", I would like to add a lookup up
field for "Meas_ID" that look-ups values of "Meas_Type" from table
“Measurements”, but limits the list to only those "Meas_ID" values in the
table "Tool_Meas" where "Tool_Meas.Tool_ID" equals the field value
"Logs.Tool_ID" in the associated parent table record.

Very easy on a Form. AFAIK, impossible in a Table. Is that enough of
an incentive to start using Forms? <g>

Seearch for "Dependent Combo" on the mvps.org site listed above for a
sample of how to do this on a Form.

John W. Vinson[MVP]

 

[PetroChris]

Forms... I haven't gotten that far in the on-line training class my company
has provided me with ... but my goal is to eventually enter all data via
forms. Right now, I've got a "draft" database design and I'm populating it
with a few example records, to test queries and (future) forms with during
development. I'll give up on data entry in tables as you suggest and start
tackling forms.

Unfortunately, I picked a complex data entry issue as the first problem I
want to solve by learning / using Access. Although my new question belongs
in the "Forms" group, can you point me to an example / tutorial on using the
"Tab Control" to enter data for multi-level parent-daughter tables (ie. Tab 1
--> data entry / viewing for Parent table, Tab 2 --> data entry for Level 1
daughter table for active record on Tab 1, Tab 3 --> data entry for Level 2
daughter table for active record on Tab 2... etc).

Thanks,

 

[John Vinson]

Forms... I haven't gotten that far in the on-line training class my company
has provided me with ... but my goal is to eventually enter all data via
forms. Right now, I've got a "draft" database design and I'm populating it
with a few example records, to test queries and (future) forms with during
development. I'll give up on data entry in tables as you suggest and start
tackling forms.

Unfortunately, I picked a complex data entry issue as the first problem I
want to solve by learning / using Access. Although my new question belongs
in the "Forms" group, can you point me to an example / tutorial on using the
"Tab Control" to enter data for multi-level parent-daughter tables (ie. Tab 1
--> data entry / viewing for Parent table, Tab 2 --> data entry for Level 1
daughter table for active record on Tab 1, Tab 3 --> data entry for Level 2
daughter table for active record on Tab 2... etc).

Please repost this as a new thread - I'm packing to leave for a
ten-day vacation and won't have time to answer! One of the other
volunteers should be able to help.


John W. Vinson[MVP]