Results 1 to 7 of 7
  1. #1
    stovecor26 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2017
    Posts
    4

    Need help with query

    Hi



    New to this i know nothing about sql programming so hoping there is an easy solution. I have a simple ms database 3 tables one holds members info and the cost of membership, one holds payment records and the 3rd holds discount information. i put in a query using the persons name and id for the relationship between the 3 tables and the i have it showing thier name membership amount, total paid in total discounts and then a field that calculates the balance. prob is it will only show a recored that has an entry in all 3 tables. so if someone joins does not get a discount and pays something they dont show up. hope that makes sense. any help appreciated

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    investigate using a left join. In the query design, right click on the join line and select 'include all records from...' which one depends on how your query is structured

  3. #3
    stovecor26 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2017
    Posts
    4
    Thanks a million head was wrecked. worked perfect

  4. #4
    stovecor26 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2017
    Posts
    4
    All the records show now so even if there is no discount i can see the payment but the ones that have a blank the calculation doesnt work is there any way around that

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Use nz([fieldname],0) in your calculation. Fields which are null don't show up in calculations so nz sets them to zero (in this case) if they are null.


    Sent from my iPhone using Tapatalk

  6. #6
    stovecor26 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2017
    Posts
    4
    Worked great thanks for that. I think i might be back on again over the coming weeks.

  7. #7
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Glad to help. Good luck.


    Sent from my iPhone using Tapatalk

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

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