Composite Key Table, how to populate it?

[d9pierce1]

Hi everyone,
I have two tables (tbl_Company and tbl_Address) which I am joining with (tbl_CompanyAddress) by composite keys. I for the life of me cannot determine how this tbl_ComapnyAddress is to be populated? This tbl has two keys, one from ID in tblCompany and one from tbl_Address. Yet I don't know how to get this table to populate with the ID's from the two tables joined. Do I create in form when saved or ? I see plenty of examples of how to make these composite key tables, yet nothing on how to populate them to get quary results. I am assuming on a form when saved? Please if someone could explain to me.


Thanks
Dave

[ranman256]

You don't need the address table ID. You don't need a 3rd table tCompanyAddress.
just add the address fields to the company table.
The company.id is the important one BUT just add a field called AddrID and add the address fields to tCompany table.
when you update the address street,city, etc. everything will be in 1 record and you maintain the original Co.ID.

[d9pierce1]

Thanks, but its just a bit more complicated than that. I have several tables that will need the address but different tables linked to it such as company, partner and so on so just adding a look up isn't the solution. I need to have this to store addresses in one table, and link to separate tables without a look up.

[Bulzie]

So tblAddress does not have duplicates? If tblAddress is your "Main" table, add AddressID field to the other tables and use that to link tblAddress to all the other tables.

[d9pierce1]

Maybe I am not explaining this correctly? I wouldn't say my main t is tbl_address... I also have a tbl_AddressType which is related to tbl address. There are more than one address in most cases, such as home, work, PO, Bill to...... I don't want a link to my other tables...Just a join, that's all. I don't want 10 records for each company when I have ten addresses.... Just an address type, address, and a company or Partner, Contact, Employee and so on that will have multi addresses.

[Bulzie]

Maybe give better examples of your tables and their fields, the data in them and what you want the output to be.

From what it sounds like you want is to select a company or a Partner and have it show all their addresses?