Unbelievable Response from Access

[whistler]

Hello. I would like to first say that Access is good; however, I have been unable to answer how to get the database query to produce a list of all clients, even if they don't have any interactions. I've attached a few files for your review. Would you kindly take a look and see if you can pinpoint how to get a list of all clients, even if there were no interactions? Thank you,
Whistler

[June7]

Probably has to do with the jointype. An INNER join will retrieve records only when there are related records in both tables. Probably need RIGHT join. Looks like the join is okay between Clients and Interactions but not sure about Clients and Group-Client. Would need to analyse the project directly if you want to provide it.

[whistler]

Thanks for your reply. Here are the details:

SELECT [010 Clients].[Client Segment], [010 Clients].[First Name], [010 Clients].[Middle Name], [010 Clients].[Last Name], [010 Clients].[Name Suffix], [011 Group].GroupName, [011 Group].GroupTypeID, [011 Group].GroupID, [010 Clients].[Contact Type], [010 Clients].[Client ID], [015 Role].RoleID, [015 Role].RoleDescription, [050 Interactions].InteractionsID, [050 Interactions].Date, [050 Interactions].NB, [050 Interactions].Owner, [050 Interactions].Summary, [050 Interactions].Venue
FROM [015 Role] INNER JOIN ([011 Group] INNER JOIN (([013 Group-Client] INNER JOIN [010 Clients] ON [013 Group-Client].ClientIDNumber = [010 Clients].[Client ID]) LEFT JOIN [050 Interactions] ON [010 Clients].[Client ID] = [050 Interactions].ClientIDNumber) ON [011 Group].GroupID = [013 Group-Client].GroupID) ON [015 Role].RoleID = [013 Group-Client].RoleID
WHERE ((([011 Group].GroupTypeID)<>3) AND (([010 Clients].[Contact Type])="Client") AND (([050 Interactions].Date) Between #1/1/2011# And #12/31/2011#));

Your assistance is greatly appreciated,
Whistler

[whistler]

One other comment...my objective is to show a list of all clients in the query results, including both those that have interactions (i.e. conversations during a certain time period) as well as those that do not have interactions. That way, I will know who I haven't contacted recently. Currently, the query provides an output only showing clients who have had interactions.

Thanks very much as this is important to me,
Whistler

[orange]

review the left join examples here
http://www.w3schools.com/sql/sql_join_left.asp

[June7]

All I can do is suggest you test modifying the jointypes. I would have thought the join between clients and interactions would be RIGHT (but then I always have a hard time recognizing what is a LEFT or RIGHT situation). Then there are all those INNER joins that probably should be LEFT or RIGHT. Try testing a query with just the two clients and interactions tables. Then see what happens as you include each of the others. It is possible these cannot all be joined in one query and get what you want.

[ssanfu]

Another option to try is to use a subquery. I have a hard time creating them so I use two queries.

Create a query with two tables: "Clients" and "Interactions" (qryCI_010_050).

SELECT [010_Clients].ClientID, [010_Clients].FirstName, [010_Clients].MiddleNmae, [010_Clients].LastName, [010_Clients].NameSuffix, [010_Clients].ClientSegment, [010_Clients].GroupName, [010_Clients].GroupTypeID, [010_Clients].[Contact Type], [010_Clients].GroupID, [010_Clients].[Client Segment], [050_Interactions].InteractionsID, [050_Interactions].ClientIDNumber, [050_Interactions].InteractionDate, [050_Interactions].NB, [050_Interactions].Owner, [050_Interactions].Summary, [050_Interactions].Venue
FROM 010_Clients LEFT JOIN 050_Interactions ON [010_Clients].ClientID = [050_Interactions].ClientIDNumber;

Once it is returning the data you want, create another query with the other tables and the qryCI_010_050.


NOTE: "Date" is a reserved word in Access and a function; it shouldn't be used for object names. Also, it is not a good idea to use spaces in object names.
http://allenbrowne.com/AppIssueBadWord.html
http://access.mvps.org/access/tencommandments.htm

Somewhere years ago, I read that you should not use a number as the first character of an object name. Can't find anything like that Googling, but I never have a number as the first character .

[June7]

I haven't had problem with numbers, even as first character, in Access. However, special characters and punctuation (underscore is exception) could cause issues and should be avoided in names as well as data.

[whistler]

Although I have not fully resolved this issue, I have been trying the suggestions that June7, orange, and ssanfu have made here (thank you!). I created a new query with just the Client and Interactions tables, as shown on the current attachment. That worked properly. However, I haven't figured out how to re-add the Group, Group-Client, and Role tables, which were described in the uploaded documents in my original post. Any suggestions? I have not had time to make updates to the object names but will soon.

Thanks for your efforts, and I am looking forward to your reply.
Whistler

[June7]

I would need the data to test if you want to provide.

[ssanfu]

Although I have not fully resolved this issue, I have been trying the suggestions that June7, orange, and ssanfu have made here (thank you!). I created a new query with just the Client and Interactions tables, as shown on the current attachment. That worked properly. However, I haven't figured out how to re-add the Group, Group-Client, and Role tables, which were described in the uploaded documents in my original post. Any suggestions? I have not had time to make updates to the object names but will soon.

Thanks for your efforts, and I am looking forward to your reply.
Whistler

Create a NEW query. Add the Group, Group-Client, and Role tables, then add the query (Query1 - from the PDF). Create the links (set the join type), drag down the fields, execute the query.

A query does not have to contain ONLY tables; it can contain:
only tables, or
only queries (as in a union query)
or a mix of tables and queries.