Results 1 to 8 of 8
  1. #1
    indians207 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    10

    Help with Relationships

    I'm trying to run a query to get Charges and Payments from different accounts, but not all are showing up. I think my relationship is wrong.

    Can somehow tell me how this is?
    *All 3 tables have primaries keys, they are the first field under the table.
    Table: Account Info
    Account #
    LastName
    FirstName
    Credit Limit
    State
    Card Type

    Table: Charges
    ChargeId#
    Account#
    Amount


    Date

    Table: Payments
    Payment#
    Account #
    Amount
    Date

    I have two one-many relationships set up, Account Info to Charges and Account Info to Payments.

    However in my query I want to list all accounts in each the total charges query and the total payments query, and I'm not getting some of the Account ID's. Thanks!

  2. #2
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    That's because, if you do an INNER JOIN, you will only get the accounts that have BOTH Charges and payments.

    You'll have to do a LEFT JOIN to get both.
    Code:
    SELECT 
       T1.Account#,
       T1.Lastname,
       T1.Firstname,
       SumOfCharges,
       SumOfPayments
    FROM 
      ([Account Info] As T1,
       LEFT JOIN
          (SELECT Sum(Charges.Amount) As SumOfCharges
           FROM Charges
           GROUP BY Charges.Account#)
       ON T1.Account# = Charges.Account#
       )
       LEFT JOIN 
          (SELECT Sum(Payments.Amount) As SumOfPayments
           FROM Payments
           GROUP BY Payments.Account#)
       ON T1.Account# = Payments.Account#


    By the way, Date is a reserved word. You should avoid naming fields Date, Time, Month, Year, Day, Name, and so on. It will also save you lots of issues in the long run if you avoid using spaces or dashes ("-") in field names and table names. Most Access programmers follow those guidelines. Personally, I also avoid #'s, but that's a personal preference, not a generally agreed rule.

  3. #3
    indians207 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    10
    How would I go about making the outer join in the relationship builder and not SQL?

  4. #4
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    1) You need to create the summary queries first.

    2) You create the relationship in the query, then click on the line and change the join type. Include all the records from one table and only the matching records from the other.

    3) You'll probably also need to put (NZ) around the SumOfCharges and SumOfPayments, so that they'll be zero instead of Null if there are no matching Charges or Payments.

  5. #5
    indians207 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    10
    Quote Originally Posted by Dal Jeanis View Post
    1) You need to create the summary queries first.

    2) You create the relationship in the query, then click on the line and change the join type. Include all the records from one table and only the matching records from the other.

    3) You'll probably also need to put (NZ) around the SumOfCharges and SumOfPayments, so that they'll be zero instead of Null if there are no matching Charges or Payments.
    Can I put Nz on a field name?

  6. #6
    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

  7. #7
    indians207 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    10
    How do I use NZ and Sum it at the same time?

  8. #8
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    You don't need to, because a NULL value doesn't add anything to a SUM.

    But it's totally legal to say:
    Code:
    Sum(NZ(MyTable.MyField)) As SumOfMyField

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

Similar Threads

  1. Explicit Relationships and Implicit Relationships
    By Dazza666 in forum Database Design
    Replies: 2
    Last Post: 07-17-2013, 02:11 AM
  2. Help with relationships
    By clive2011 in forum Access
    Replies: 5
    Last Post: 09-14-2011, 03:00 PM
  3. Relationships
    By neo651 in forum Database Design
    Replies: 2
    Last Post: 08-30-2011, 08:38 AM
  4. Help with Many to Many Relationships
    By alpinegroove in forum Database Design
    Replies: 5
    Last Post: 03-24-2011, 01:12 PM
  5. Relationships?
    By rosh41 in forum Database Design
    Replies: 2
    Last Post: 06-23-2010, 01:26 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