Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2011
    Posts
    9

    Unanswered: Too many rows in query - join issue

    Having not used Access in many years I think I have forgotten the basics.

    I should be getting 9 rows of data but instead I am getting 90.

    SELECT DISTINCT [dbo_Spend Basic Data].LastName AS [Recipient Last Name], [dbo_Spend Basic Data].FirstName AS [Recipient First Name], dbo_Users.Email
    FROM [dbo_Spend Basic Data] INNER JOIN ((dbo_Users INNER JOIN dbo_UserTypes ON dbo_Users.UserTypeID = dbo_UserTypes.UserTypeID) INNER JOIN dbo_Engagement_Invitations ON dbo_Users.UserID = dbo_Engagement_Invitations.UserID) ON [dbo_Spend Basic Data].EngagementID = dbo_Engagement_Invitations.EngagementID;

    There are 4 tables: [this is the field that is joined to the index field of the next table]

    dbo_Spend Basic Data [engagementid] dbo_EngagementInvitations [UserId] dbo_users [usertypeid] dbo_UserTypes

    I thought I would get a row that looks like this:

    FirstName LastName Email Usertype

    But I get 90 rows. Each row has the first name and last name and is then repeated for each email address.

    Thank you for your help in advance.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    But I get 90 rows. Each row has the first name and last name and is then repeated for each email address.
    So if you expect 9 rows, how, exactly, do you expect all the e-mails to show?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Aug 2011
    Posts
    9
    It's a one to one relationship.

    I wish I could upload a sample of the db.

  4. #4
    Join Date
    Aug 2011
    Posts
    9
    Ahh, I can upload it in zip form.

    Here it is and thanks for the help.
    Attached Files Attached Files

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, gonna have to wait until I get back to my office -- I don't have access to 2007 / 2010 from here. Bump the topic again later if there's no reply for a while :-P
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No relationships? Ugly!!

    Anywayz, here is what I think you want, not cleaned up like the SQL gurus would like to see, but copy and paste this into a query and it will do what you want.:

    Code:
    SELECT DISTINCT Users.FirstName, Users.LastName, Users.Email, UserTypes.UserType
    FROM ((BasicSpendDataDistinct INNER JOIN EngagementInvitations ON BasicSpendDataDistinct.EngagementID = EngagementInvitations.EngagementID) INNER JOIN Users ON EngagementInvitations.UserID = Users.UserID) INNER JOIN UserTypes ON Users.UserTypeID = UserTypes.UserTypeID;
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Aug 2011
    Posts
    9
    Yea, it's not pretty. I'm trying to cobble a quick report together.

    Anyway, AWESOME. Thanks for the help. It's exactly what I needed.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome :-)
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •