Alias

[Scott Matheny]

I have a table named Companies. I have two different
places in another table where these companies to need to
be dropdowns; I want to be able to have seperate form
fields that display information from this Companies table
twice, but I'm stuck on how to reference them
individually. I thought possibly that if I create two
seperate relationships to this table and the Companies_1
tables would appear. In doing this, do both still relate
and update as one table (Companies)? But more
importantly, can I reference to Companies_1 in a form to
distinguish through which field I am referencing the
Companies table? As is now, only the first field shows up
no matter how many sets of Companies fields I include in
my form.

Please help.
Thanks in advance.
-Scott

 

[Graham Mandeno]

Hi Scott

Create a query on which to base your form. To this query, add the main
table and two copies of the Companies table. Join (by dragging) the
CompanyID of one companies table to one foreign key field in your main table
(say it's SupplierID) and do the same forthe other field (let's say it's
CustomerID). Display the Properties Window (View>Properties). Click on the
Companies table joioned to SupplierID and change its alias to Suppliers.
Repeat for the other table. Now right-click one of the join lines between
the two tables and choose "Join properties". Select the option 2 (or 3)
which says "Include all records from 'Main Table' and...". Repeat for the
other join line. They should now have arrows pointing *towards* the
Companies aliases.

Now add the fields from the Companies aliases that you want to display in
your form. For example, add CompanyAddress from Suppliers and give the
field an alias by typing SupplierAddress: before CompanyAddress in the
fieldname cell.

Now all the fields you require will be available for your form. Note that
you don't need to include either CompanyName, because this should be the
displayed field in the combobox bound to SupplierID (or CustomerID).

 

[Scott]

This was a really great solution, thank you so much.

-Scott