Query return help

[LHolden]

I am currently working with a query which relates Brokers to their Clients. Each client has a BrokerID associated with it. This query is also linked to a form which can search for specific Brokers with a combobox. There are also fields of client termination and Broker activity. The query is set so that the broker name is equal to whatever is selected in the drop box, but the broker must be active, and the client must NOT be terminated. My problem currently is that if a Broker is not affiliated with a client, then they will not be shown on the form when selected in the combobox. I've been trying to come up with some sort of "IsNull" statement to fix this problem, but can't figure it out so far.

Any help would be greatly appreciated, and Thanks in advance!

 

[Trevor G]

Welcome to the Forum,

You would need to create a query for the 2 tables and ensure the relationship points to broker ID. Add some fields from both tables to check and see the results that then gives you the basis for your drop down on the form to show all Brokers.

 

[LHolden]

Sorry for the late response, I had a lot of work to do yesterday. My relationship is definitely centered around BrokerID, and the drop box is set to show all the brokers, but the query itself will not return any Brokers not associated with a Client, and I don't quite understand why/how to get around it.

Thanks again for your help.

 

[Trevor G]

It is down to the relationship in the query, when the 2 tables are added and if you already created the relationship through the relationship window you would have set that to 1 to Many and Cascade Update etc. But in the Query window you can also specify that it shows all from 1 table if you double click the Line that Joins the tables in the Query Window.

If you can't get it perhaps upload a sample of your database with a little data (not real) from the 2 tables and the one query and I will take a look for you.

 

[MSAccessRookie]

Take time to read up on Table JOINs. I believe that a LEFT JOIN between the Tables wil provide what yopu are looking for, since it will show all records from the Broker Table and make the missing Client IDs Null. Something like the following (change to your names) will give you an idea of what I mean.

SELECT tblBrokers.BrokerName, tblClients.ClientName
FROM tblBrokers LEFT JOIN tblClients ON tblBrokers.BrokerID=tblClients.ClientID

 

[LHolden]

Thanks for offering to take a look at this. I replicated the database with obviously fake information and slimmed down to my specific problem (There are more fields in my actual Clients table). I created the queries and form exactly as they appear in my database, so I really hope you are able to help me. Just like in my database, the broker "Anybody" has no client, and will not show up when selected in the combo box on the form.

Thanks so much for looking at this again!

 

[LHolden]

I totally understand what you mean (and thanks for pointing that out, as I'm pretty new to Access), but I just tried a Left Join, on both my database and the mock database, and still a Broker with no client attached will not show up.

 

[LHolden]

Oh wow! I just figured it out! Your Left Join worked like a charm, just have to make the "no" for terminated clients an OR statement instead of an AND statement. Thank you so much for all of your time and help!

 

[Trevor G]

Pleased to read you have found a solution and thank you for letting us know.