linking table fields where one tables records are the field name

[timmyfish01]

Hi,

I have a table which uses a field list box and populates records with
the field name. Another field in this table records a change to be made
to that field. for example:

Table A field columns
id
shop_id
field_to_change < this contains a lookup field list of Table B
change

Table B field columns
id
shop_id
fish
poultry
meat

so, in Table A I select the "shop_id" (1 for example), Select the
"field_to_change" (fish, for example) and then in "change" I add the
change (to Cod, for example)

What I then want to do is link table a field records (fish, poultry,
etc) to the Table B field columns (fish, poultry, etc) so that I can
update the changes.

The above is only an example and the real thing actually contains over
40 fields, hence trying to use the field list box, rather than a
replica table for changes for example.

Any help appreciated.

Thanks,

Tim

 

[Guest]

if I understand your question; you should simply define the field as LookUp
in the design view of the Table so that they look up other fixed tables.
Tables better accommodate large lists and lists that change.

This is the common method for something like state initials i.e. AL AZ
etc where you don't want to have people type in.....this fixed data resides
in a separate table not a lookup field list.... as you note - a lookup
field list is probable suitable for 5 or less items that never change....

revisit the field parameter when in Table design view and go to the LookUp
tab...and redefine what it is looking up

 

[timmyfish01]

Thanks for the reply network, but its once the field name is recorded
from the look up.

The look up combo box is used to select a field name > the Field name
is selected and added as a record.

What I then want to do is use the recorded field name from above to
link it back to its original table and field.

This cannot be a normal relationship as the field value cannot match a
field name or column. Put simply I want to match a record value with a
table field name/Column.

Complicated to describe, but I hope that helps!

Thanks,

Tim