Table Structure advice- Receiving Join Key not in recordset error.

[sw1979]

Hi,
I was wondering if I can get some advice as I am recieving an error when creating a form for the following database (Cannot add records, Join Key of tbleSupplier not in recordset).

The Supply Database must track all suppliers, their address, their insurance and any info on what projects they work for etc etc. Any feedback would be helpful. I think the part im sort of confused with is how the relations work, ie. if each PK is an Autonumber and is a FK in the main table are they always inner joins etc?

A Supplier MAY have MANY Salesperson
A Salesperson MUST work for ONE Supplier
A Suppler MUST belong to ONE Category
A Category MAY have MANY Supplier
A Supplier MUST be situated in ONE Country
A Country MAY contain MANY Supplier
A Supplier MAY have MANY Insurance Types
A Insurance Type MAY have MANY Supplier
A Supplier MAY supply ONE Project
A Project MAY have MANY Supplier

So the Databases are as follows: tblSupplier, tblSalesperson, TbleCountry, TbleInsurance, TbleProject, TblCategory

TbleSupplier
(ABN, ACN, SupplierNo, SupName, Legal Entity Name, BusPhNo, BusFaxNo, Webpage, BusEmail, BusStreetNo, BusStreetName, BusPCode, BusState, BusCountry, PostalStNo, PostalStName, PostalSuburb, PostalState, PostalPcode, PostalCountry)
PK – SupplierNo
FK - CountryID - References TblCountry
FK - ProjectID - References TblProject
FK - InsuranceID - References TblInsurance
FK – CategoryID – References TblCategory
FK – SalesPersonID – References TbleSalesperson

TbleSalesperson
(SalesFirstname, SalesSurname, SalesMobile, SalesEmail)
PK – SalesPersonID (AutoNumber)

TbleCountry
(CountryName)
PK – CountryID (AutoNumber)


TbleInsurance
(InsuranceType, InsuranceDescr, InsuranceExpDate, InsuranceStatus, InsuDocNo, InsuHyperlink)
PK - InsuranceID (AutoNumber)


TbleProject
(ProjectName)
PK - ProjectID (AutoNumber)


TbleCategory
(CategoryName)
PK - CategoryID (AutoNumber)

TbleProject
(ProjectName)
PK - ProjectID (AutoNumber)


TbleCategory
(CategoryName)
PK - CategoryID (AutoNumber)

 

[neileg]

Do you understand what an inner join is? An inner join will only return results where there is a matching record on both sides of the join. A left outer join will return all the records from the left table and any records from the right that match. If there is no matching right table record, null values will appear in the right table fields. A right outer join is the same as a left with the tables swapped over.

The information you have given is more relevant to determining if you have a one to many or a many to many relationship.

Whether you would use an inner or outer join depends on what data you wanted to return, not what the relationship is.

Do you want to rephrase your question?