Reference/dereference conundrum. My data-en...

[Charlie_Wells]

Reference/dereference conundrum.

My data-entry table has a Number column defined as Ref. The user enters a number and that number is currently dereferenced with data from a table called Members (to get a member’s name once a number is selected).

So now I need to also use Number to grab data from another read-only table which contains permit information for members. That table is also keyed by Number.

So basically I want [Number] in my data-entry table to reference two other tables.

Is that even doable? Or is the only solution to combine the two read-only tables into one at the back end? (I’m dealing with legacy data, which is why this is a bit messy).

EDIT: Maybe create a virtual column in the data-entry whose value is the number entered elsewhere in the form, and use that as a ref into the second table? Trying that… Thanks.

[RezaRaoofi]

What are these 2 read-only tables? Are they actually different entities in real world? If yes, then each must have their own Key column that could be linked to the third table (i.e. data-entry table) via 2 Ref columns; one for each table.

This is as far as I can tell from your description without knowing the tables and their column structure.

[Charlie_Wells]

@RezaRaoofi thanks. The tables are worksheets created by our office at the back end and referenced in the field by our operators. One worksheet is called ‘Members’, and [Number].[Membername] in that worksheet returns the name of the member for that number.

The end user enters a member number in the entry form (yet another worksheet) in a column called [Number], currently deref’ed to ‘Members.’

The other table is a second worksheet called ‘Form18’, and contains permit data, also keyed by (member) number. So [Number].[Contractor] in that worksheet is the name of the contractor for that member number.

Sorry if this is confusing… I’ll work on creating a second column to Ref to ‘Form 18’.

[Charlie_Wells]

@RezaRaoofi got it to work - thank you!

[RezaRaoofi]

+Charlie Wells you are welcome!