Return blank results in query when field is fed by a look-up relationship

[mlm]

Hi,


I'm still learning the basics, but I have a table with several fields being fed through look-up relationships using a combo-box on the associated form. So, my main table is "EventList", which includes "StateNum" and "CountryNum" fields. I have a "StateList" and "CountryList" table where the combo-box on my form finds the info to populate itself. The selections on the form are then stored in the associated fields in the "EventList" table. My problem is that I am not requiring these fields to be filled in, but when I run a query, unless both the "StateNum" and "CountryNum" fields have info, the record isn't pulled by the query. From the troubleshooting I've done, it seemed to indicate that it was a problem with the Join Type of the relationships linking the StateList and CountryList tables to the EventList fields. From what I can tell, the selection should be "Include ALL records from 'StateList' and only those records from 'EventList' where the joined fields are equal" and the similar one for the countries. But, I have tried this and vice versa without success. I still only get the records where both fields are filled in. Any thoughts on this?

Thanks!

[June7]

Show the query SQL for analysis.

Probably need to use wildcards.
Review this tutorial http://datapigtechnologies.com/flash...tomfilter.html

[mlm]

Thanks for the help, that tutorial was very helpful. I will be looking it over for a second time and trying to update my query accordingly soon. In the meantime, here is the query sql:

SELECT PrincipalList.Salutation, PrincipalList.FirstName, PrincipalList.LastName, AgencyList.Agency, PrincipalList.Position, PrincipalList.Grouping, EventType.EventType, EventList.[Start Date], EventList.[Multi-Day Event?], EventList.[End Date], EventList.[Confirmed?], EventList.City, StateList.State, CountryList.Country, EventList.Description, EventList.Link, EventList.ID
FROM StateList INNER JOIN ((AgencyList INNER JOIN PrincipalList ON AgencyList.[ID] = PrincipalList.[Agency]) INNER JOIN (EventType INNER JOIN (CountryList INNER JOIN EventList ON CountryList.[ID] = EventList.[CountryNum]) ON EventType.[ID] = EventList.[EventTypeNum]) ON PrincipalList.[ID] = EventList.[PrincipalNumber]) ON StateList.[ID] = EventList.[StateNum];

There are many fields that I left out of the original description, so sorry for the clutter. Let me know what you think.

[June7]

Probably want LEFT or RIGHT joins. Need WHERE clause.

[mlm]

Thanks for the help. Could you help me out a little more with the SQL...I don't have any experience with it. I tried replacing all the inner joins with left joins but Access told me that it didn't support the join operation.

[June7]

Want to provide db for analysis? Follow instructions at bottom of my post.

[mlm]

Sorry for the delay in responding...I'm not sure I can remove all the confidential info so that I could send it. Would you have any general suggestions for setting the joins right?

[June7]

I would need to work with data to analyse issue. If you can't get the query designer to accept the query might require multiple queries to get the final output.