Queries for Parent Child Relationship (1 Viewer)

[Thales750]

This is super basic, but worth asking. I've slowed down on development in the last years and rust has taken over the development portion of my brain.

The following is a query that needs to return all of the Contact Records when the getContactTypeSearch() Function is set to "*". The problem is that there are Contacts that have no Records in the tblContactTypesPerContact.
Interestingly, if the function has a specific value all records matching that criteria are displayed.

Is there a simple way to display all records when the criteria is set to a wild card?

Obviously a simple fix would be to add a default Contact Type for each new Contact Record.


Thanks

 

[tvanstiphout]

If you set a criterion on the outer side of a query (as in your last element), the query "collapses" to an inner join.

 

[Thales750]

If you set a criterion on the outer side of a query (as in your last element), the query "collapses" to an inner join.

I think it is on the outer side.

This field ctpContactTypeID in the Table tblContactProfile is null. It was there when I thought that one contact Type would be enough. It's not of course.

 

[Pat Hartman]

To solve the problem of criteria on the "wrong" side of a left join, you can create two separate queries. One query applies the criteria to the left table. The second query applies criteria to the right table. The final query joins the two criteria queries using a left join.

SQL Server handles this situation differently from Access and doesn't cause the join to be interpreted as an inner join. Using separate queries coerces Access into seeing things your way.

 

[plog]

needs to return all of the Contact Records when the getContactTypeSearch() Function is set to "*"

First I'm really concerned about how getContactTypeSearch functions and the downstream of what this query feeds. My gut tells me a docmd.openform or docmd.openreport would be the best way to accomplish your ultimate goal and not configuring this query.

With that said you should move the logic of that criteria to a new field and then use criteria on that new field. For example--

Matches: iif(getContactTypeSearch()="*" or ctcContacttypeid like "*" & getContactTypeSearch() & "*", 1, 0)

Then put 1 in the criteria area of that new field. Again though with a better idea of the big picture we could give you best way to accomplish that.

 

[Thales750]

All this is, is a RowSource for a list box. The Contacts can have multiple Contact Type. Hence the One to many from the tblContactsProfile to the tblContactTypePerContact.
There is a unbound dropdown that allows the User to select ContactType this returns a value that is stored in the PupContactTypeSearch. There is a Clear Button. I think I'lljust have the clear button assign a new RowSource to the List Box and the Dropdown a separate one.

Problem Solved. I was hoping for a new simpler method.

Thank All.