Referential Integrity From Two Table Sources?

[Bubbis Thedog]

Hello,

I was wondering if it was possible in Access to have one field in a 'hub' (main) table whose values are restricted by those in two different tables. The two other tables are static, and the 'hub' field I would like restricted only to the unique IDs in those tables.

Is this possible only by restricting the 'hub' field criteria according to the values allowed in the two static tables?

Any help would be greatly appreciated!

 

[SydneyGeek]

If the "hub" field is not a key field, and you are using a form for data entry, you could try building a Union query as the Row Source for a combo box.
So...
In the combo, set the Control Source to the field tht you want to populate.
In the Row Source, click the blank row and hit the Builder (...) button to open the query grid. Now,select View | SQL view. You'll need to type something along these lines:

SELECT * FROM [Table1].[Field1Name] UNION SELECT * FROM [Table2].[Field2Name] ;

Closethe query, save when prompted and try it out.

Alternatively, if the other fields are not changing (they're static, right?) you could biuld a query like the above to ectract the values, then use a Make-Table query to push that into a new table with a single field. You could then base the combo on this field.

Denis

 

[Bubbis Thedog]

Thanks for the great input, SydneyGeek. I'll give your solution a shot and (hopefully) post the results.