Null handling in access database

[BatmanMR287]

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).

[ItsMe]

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.

[June7]

Like ItsMe said, need better understanding of Issues table. Does every client have a record for each possible issue? Show example of data.

[BatmanMR287]

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.

[June7]

Try:

SELECT clientID FROM table2 WHERE Received Is Null GROUP BY clientID HAVING Count(ID) = 2;

[BatmanMR287]

I will try it first thing in the morning. Thank you!!

[BatmanMR287]

Don't think I am writing it correctly. Not sure what goes in the HAVING Count(ID) = 2; What ID am I using?

[June7]

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.

[BatmanMR287]

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.

[BatmanMR287]

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.

[June7]

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?

[BatmanMR287]

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.

[BatmanMR287]

Not sure if concatenate will work with mail merge.

What I need to concatenate is in the same column. Issue number column.

[June7]

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?

[BatmanMR287]

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?