Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    BatmanMR287 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    69

    Null handling in access database


    I have a client database that has null values in issue received, there are as many as ten issues per client. What I need to do is make a list of clients that have only two issues outstanding and a list of clients that have three or more issues outstanding. I am not sure how to write this.
    There are two tables that are connected correctly. The client table(one) and the issues table(many).

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    sounds like you have multiple columns in a table dedicated to unique issue types. If possible, I would try to implement a single table dedicated to issue types. This issues table would hold a record and a unique ID for each issue type. I would create another table to log the many issues for your clients, storing PK values from your clients and issues tables. Additional fields could indicate the status of the issue as well as time stamps, etc.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Like ItsMe said, need better understanding of Issues table. Does every client have a record for each possible issue? Show example of data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    BatmanMR287 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    69
    One table is client name, address, contact names. The second table tracks the issues. The issue numbers are the same for every client and are dropped on everyone at the same time.
    Example: Table one:
    Jackson District||Springfield||Il||65243
    Example Second Table:
    Jackson District||Issue#84||12/12/2013
    Jackson District||Issue#85||Null
    So the second table is a many to one in relation to the first table. There are ten issues currently active.
    I need to do two mailmerge letters. One for all clients with two issues Null and one for three or more Null issues.
    I don't know how to write the code to compile the two lists I need. The tables are linked correctly for cascade add and delete.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Try:

    SELECT clientID FROM table2 WHERE Received Is Null GROUP BY clientID HAVING Count(ID) = 2;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    BatmanMR287 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    69
    I will try it first thing in the morning. Thank you!!

  7. #7
    BatmanMR287 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    69
    Don't think I am writing it correctly. Not sure what goes in the HAVING Count(ID) = 2; What ID am I using?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I was assuming there is a unique ID field in table, possibly an Autonumber type. Any field with data for every record should work. Try Division field instead.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    BatmanMR287 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    69
    The primary key is the District name on both tables, no auto number. I thought of going with auto number but decided not to.
    NewsBreak!!!!!! I got it. Your SQL line is right on! Thank you so much.

  10. #10
    BatmanMR287 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    69
    June7, thank you for the help. I am a Computer Information Systems major and understanding this is important. You helped me to understand more SQL functions which will help me in the future.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    How can District be the primary key field in both tables? Your example data shows repetition in that field for second table. Did you mean it is primary in table 1 and foreign in table 2?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    BatmanMR287 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    69
    Yes, the District table is primary and Issues table foreign. District name is the primary key on district table and foreign key on Issues table.
    Now I have to set up a mail merge for two queries. The first one is easy since it only tracks two issues. The second one is now a problem because I need to include three or more issue numbers in the mail merge. I can pull out highest and lowest issue numbers with the other information from the district table but to have it list more than three is hard.
    In the criteria area I put Min and Max but I need it to list all issue numbers with the contact info for one mail merge.

  13. #13
    BatmanMR287 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    69
    Not sure if concatenate will work with mail merge.

    What I need to concatenate is in the same column. Issue number column.
    Last edited by June7; 01-13-2014 at 03:18 PM.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I have limited experience with mail merge - just helping posters in forum. Seems it did not work with calculated fields.

    Why do you want mail merge and not just an Access report?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    BatmanMR287 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    69
    I'm doing this for an association. They want to send reminder letters to clients that are two issues behind and last chance letters for clients that are three or more issues behind. The mail merge would automate the process and allow them to send the letters with more frequency. Would it be easier to have the form letter as a report? Could I set it up to be one button that would generate the letters for all clients?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 10-03-2013, 11:01 PM
  2. Replies: 2
    Last Post: 10-25-2012, 05:36 AM
  3. Handling Null DLookup result
    By j6sander in forum Access
    Replies: 1
    Last Post: 07-19-2012, 11:07 AM
  4. Replies: 6
    Last Post: 04-26-2012, 10:00 PM
  5. Help with Null value handling
    By Thumbs in forum Programming
    Replies: 3
    Last Post: 03-05-2012, 10:55 AM

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