Results 1 to 11 of 11
  1. #1
    whistler is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    26

    What in the World? Access Does Not Operate As Expected

    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

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    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.

  3. #3
    whistler is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    26
    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

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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

  5. #5
    whistler is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    26
    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

  6. #6
    whistler is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    26
    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

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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

  8. #8
    whistler is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    26
    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

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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;

  10. #10
    whistler is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    26
    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.

  11. #11
    whistler is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    26
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. What the world happened!
    By SemiAuto40 in forum Programming
    Replies: 2
    Last Post: 10-03-2011, 10:59 AM
  2. Replies: 6
    Last Post: 07-01-2011, 02:11 PM
  3. Too few parameters expected 4
    By forestgump in forum Programming
    Replies: 1
    Last Post: 04-15-2011, 09:10 AM
  4. World's simplest question
    By corquando in forum Database Design
    Replies: 2
    Last Post: 02-09-2010, 05:46 AM
  5. Too few parameters. Expected 2.
    By PPCMIS2009 in forum Access
    Replies: 0
    Last Post: 01-28-2009, 01:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums