What in the World? Access Does Not Operate As Expected

[whistler]

When I run a query, Access does not respond as expected with all of the expected results. I am a beginner so I may just not understand how it works.



Tables in Query

Clients ------ Clients/Accounts ------ Accounts

When I create a query, I would expect Access to respond with all Accounts that meet the Criteria in the query, not just the Accounts that have already been connected to Clients through the Clients/Accounts table. Instead, Access filters out results, even though my query wouldn't exclude the account. I would at least like it to show the Accounts, possibly with blanks for the Clients. I am using a query Criteria from a column in the Accounts table. The query essentially says to show me all accounts that are Not Null in this one column. Two of the accounts had not yet been connected to Clients table via the Clients/Accounts table, and the query ignored them by not displaying them. This caused me to miss accounts that should have been listed. Why couldn't the query show me all accounts that fit the criteria? Would you be able to help me understand the reason Microsoft would design it like this?

Many thanks,
Whistler

[alansidman]

Please post the SQL Statement for your query. I think your issue is the type of join you have on the tables but cannot be sure until we see the query itself. The table Clients/Account is a junction table? Because you have a many to many relationship between clients and accounts? Just confirming my understanding.

BTW: the character "/" is a reserved character in Access and should not be used in Field Names or Table Names. It may cause unexpected results or issues.

[whistler]

Alan:

Thanks for your quick reply. Here is the SQL Statement:

SELECT [040 Accounts].AccountID, [040 Accounts].[Account Number], [040 Accounts].[Account Title], [040
Accounts].[Required Minimum Distribution], [040 Accounts].[RMD Year], [040 Accounts].[RMD Type], [040
Accounts].[RMD Frequency], [040 Accounts].[Inherited Account], [010 Clients].[First Name], [010
Clients].[Last Name], [015 Role].RoleID, [015 Role].RoleDescription
FROM [015 Role] INNER JOIN ([040 Accounts] INNER JOIN ([010 Clients] INNER JOIN [041 Clients-Accounts]
ON [010 Clients].[Client ID]=[041 Clients-Accounts].ClientID) ON [040 Accounts].AccountID=[041 Clients-
Accounts].AccountID) ON [015 Role].RoleID=[041 Clients-Accounts].RoleID
WHERE ((([040 Accounts].[RMD Year]) Is Not Null) AND (([015 Role].RoleID)=17)) OR ((([040

Accounts].[RMD Year]) Is Not Null) AND (([015 Role].RoleID)=58));

Also, thanks for your comment about the "/" character. I used that in my post because I felt that using a dash might be confusing since I was using dashes to show connections between different tables.

Regarding relationships, I selected Enforce Referential Integrity, Cascade Update Related Fields, and Cascade Delete Related Records, only because that seemed like the strongest integrity, not because I understood it. Any insight on that would be appreciated.

Thank you,
Whistler

[orange]

Further to Alan's comments, you should avoid using spaces and "-" in field and object names.This is the typical separator "_" if you use one.

You have an additional table Role in your query that was not mentioned earlier.

I'm not sure if I have followed exactly your SQL statement, but your Where statement may reduce to this
WHERE
([040 Accounts].[RMD Year] Is Not Null AND
[015 Role].RoleID In (17,58)
);

If some records are not yet in your ClientAccounts table then you will not see them using INNER JOIN.

See http://www.w3schools.com/sql/sql_join.asp for LEFT and Right JOINS

[whistler]

Hello,

I was oblivious to INNER JOIN, LEFT JOIN, and RIGHT JOIN until you mentioned it. In fact, I've never looked at an SQL statement even though I've been developing my Access database for several years. There were three INNER JOINS in the SQL Statement. I tried changing them to LEFT JOINS because I want all records in the Accounts table but it didn't like it. It said that Join Expression was not supported. Any ideas on what to do next? Here is the part of the SQL statement in question: FROM [015 Role] INNER JOIN ([040 Accounts] INNER JOIN ([010 Clients] INNER JOIN [041 Clients-Accounts] ON [010 Clients].[Client ID] = [041 Clients-Accounts].ClientID) ON [040 Accounts].AccountID = [041 Clients-Accounts].AccountID) ON [015 Role].RoleID = [041 Clients-Accounts].RoleID
WHERE ((([040 Accounts].[RMD Year]) Is Not Null) AND (([015 Role].RoleID)=17)) OR ((([040 Accounts].[RMD Year]) Is Not Null) AND (([015 Role].RoleID)=58));

Thanks for the link...it started opening my eyes.

Whistler

[whistler]

I was thinking further about this. How did Access know to pick INNER JOIN if I didn't specify it?

Is there a way to change the statement from INNER JOIN to LEFT JOIN so that all records from the Accounts table even if the Account is not yet connected to a Client through the junction table?

Thank you,
Whistler

[orange]

You can change the type of join in the relationships window.

Click on the line joining the tables and adjust based on prompts/screen from the wizard.

I have 2003, not 2007 so there may by differences.

see this also
http://office.microsoft.com/en-us/ac...010096320.aspx

[whistler]

You are teaching me things I never knew. I clicked on the lines between the tables in the query design window. I tried to change them but it responded The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. I test some different choices for each but it came back everytime with the same error dialog box.

Clients ------ ClientsAccounts -------- Accounts

Any ideas on how to resolve? I would like all Accounts to show up even though there may not be clients linked to accounts yet in the ClientsAccounts table.

Thank you,
Whistler

[orange]

There is reference info to ambiguous outer joins in the last link I gave.

Work with it an see if you can get it working. I'm heading out, we're visiting friends.

Will check in later.

Here's a simple example (2 tables only) from my stuff. Perhaps you can the syntax??

SELECT distinct Customers.CustomerID, Customers.CompanyName, Orders.CustomerID
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

[whistler]

I have been working with it, including both your syntax and the link you sent, and haven't yet figured it out. I'll keep trying.

[whistler]

OK, I figured it out enough to close this thread. Thanks for all of your help. Orange and Alan, special thanks to you!

Thank you,
Whistler