Use table in other table twice?

[jaxba]

Hi,

I’m working with a database with two tables: Address Book and Project (this is just an example).

Address Book:
Company ID
Company Name
Address
City

Project:
Project No.


Project Name
Supplier ID
Customer ID

Supplier ID and Customer ID should relate to Company ID from Address Book, but I can’t figure out how to relate these fields in order to make it work. I would really like to avoid to have two Address Books (one for suppliers and one for Customers), but what’s the best solution?

Cheers.

[June7]

Address Book can be related to both SupplierID and CustomerID fields. Just pull Address Book table into query twice.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[Bob Fitz]

I assume that one company can have many projects, so perhaps this would work for you:
Remove Supplier ID and Customer ID from table Project and replace them with a new field called CompanyID which should be a data type field, assuming that Company ID in table Address Book is an Auto-Number type field. Create another new field in table Project called CompanyType as a text field. In the sub form that you would use to enter data, use a combo box control bound to the new CompanyType field. The combo box could be set to only offer a choice of Supplier or Customer.

EDIT:

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

Excellent advice. IMHO.

[jaxba]

Address Book can be related to both SupplierID and CustomerID fields. Just pull Address Book table into query twice.

I’m sorry, but I’m not sure I understand you correct…

It will work fine if I create the relationships in the query (with two Address Books tables and the Project table), but not if I make the relationship in ‘Relationships’ - is that correct?

I would assume that I needed a relationship between SupplierID=CompanyID and CustomerID=CompanyID (as a real relationship), but I only have some kind of relationship (join?) defined in the query at the moment…

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

I fully agree. It's just an example ;-)

[June7]

Relationships in relationship builder are not required for db to function but you can set this double relationship by dragging the table into relationship builder twice, just like in query. Established relationships will aid when building queries as well as aid with data integrity, but not mandatory. I often ignore the relationship builder.