Form query excluding specific records?

[adh123]

Trying to build a CRM system for the office but am getting stuck with the below, any help is much appreciated!

Each company (tblCompany) in the database has at least 1 enquiry (tblEnquiries) linked to it and normally at least 1 (sometimes 0) people (tblPeople).

I have a form which loads company specific information e.g. notes/quotes/orders/people/enquiries. All data is loaded based on the company unique id (c_id).

The attached image shows Company 1 (c_id = 1). It has 6 enquiries. However the subform only displays 5 of these. It does not display any which do not have a person (or p_id) linked to it - this is consistent throughout the database. I have included the table relationships and the enquiry as well in case they are needed!

Searching the net seems to suggest 2 possibilities:
1 - the relationship join type needs to be set to include all from tblEnquiries and only those from tblPeople where joined fields are equal, however changing the join properties does not appear to have an effect.
2 - table field types do not match (all _ID fields are set to number, unique numbers only).

[edit]: am using Access 2010!

I have re-designed the query to pull through tblEnquiries and tblPeople data based on the c_id field on the open form, which is now showing all records, including those not assigned to a person.

However, in the form I can select one from the query datasheet and open to see additional info. Those without a blank p_id number do not open, I receive the error:

"Run-time error '2113':
The value you entered isn't valid for this field.


All _id fields are set to numeric so am not sure how to correct this?

 

[plog]

You need to work on your table structure. Relationships shouldn't have loops or multiple paths between tables. Your relationship can causes ambiguity, duplicates and suppressed records.

What you really need to do is decide what objects belong to other objects. Enquiries cannot be directly linked to people and companies if people are directly linked to companies as well. You either need to remove the relationship between enquiries and companies or the relationship between people and companies.

 

[adh123]

Thanks plog!

Simple things are catching me out!

I updated the query to show all tblEnquiries p_ID records and only those which match on tblPeople (in the datasheet it now displays the null records correctly).

The Enquiry form record source has now been updated to the same, which now allows me to view all records in detail (not just those with people attached).

I shall have a look through the rest of the database now and make sure that there are no other cyclical relationships!