two tables and needing one source for a combobox selection

[Guest]

Here's my semi-dilema:

I have two tables:
1) Companies
2) Contacts
The Contacts table is related to the Companies table primary key
(autonumber) so companies can have contacts related to them (directly, only
one company to a contact.) I have a "Quotes" table with quote info in it and
I want to add bidders to the quote. So here's the catch: I want to be able
to add EITHER a company OR a contact to the bid list of a quote.

I tried creating an outer join query with a conglomerated Company/Contact
Index but my concern arose when I noticed that once I added a contact to a
company without a contact, that conglomerated Index would dissapear. i.e. A
company would have an ID say 423 and if they had a contact there would be a
listing of 423-98 for the 98th contact entered under auto number. Well if a
company did not have a contact they would be listed as only 423- and the
contact (being null) would be blank. Well, if later, I added a contact to
that company that key (423-) would no longer exist because there would not be
a null value in the contact table.

 

[Guest]

I think the problem lay in having the same structure for both tables, Company
table is OK to have ID number as "Company number" but contact table should
have a field with the company number (The ID number from the Company table in
this case) and if you like, the contact list can also have an ID number as
regulatory purpose. when come the time of linking both table, use that ID
from the Company table and the new field in the contact table.
You will be able to Query by Company or by Contact.

 

[Larry Daugherty]

Is a bidding contact representing himself or a company? My guess
would be a company. The issue is usually about separating contacts
from customers (companies). If it's a business entity then it's a
company even though it may be a sole proprietorship operated by your
pal Fred. Fred's company is the customer. Fred is the contact there.

HTH