Geek Answers Handbook

Number of samples of various NULL values ​​in Access

I have a table in which I need to track items that are returned for repair, so keep track of the disposition, problems, and when we received and sent the items .:

  Table Name: RMAItems
  id |  RMANbr |  PartNbr |  RecvDate |
 -----------------------------------------
  1 |  12345 |  456-003 |  12-21-2012 |
  2 |  12345 |  434-007 |  12-21-2012 |
  3 |  12346 |  422-010 |  |
  4 |  12347 |  421-540 |  12-21-2012 |
  5 |  12347 |  460-000 |  |
  6 |  12348 |  459-006 |  12-26-2012 |
  7 |  12349 |  439-007 |  |
  8 |  12349 |  435-006 |  |

The "Recv Date" column is the only one of the three that can be NULL (i.e. no value). The identifier is the primary key (without duplicates), and the RMA Nbr column can have duplicates (since there can be several elements for the RMA Nbr assigned to the client). The Recv Date column can be NULL until we mark it as received, and the form automatically populates the date with another request.

I need a query in Access 2003 (my company is poor and cannot be updated right now) in order to count the number of individual entries in the Rbr Nbr column in which the Recv Date field is NULL (i.e. I need to know the number of RMA Nbr that does not have was all items received). If I were to execute the query that I need in the above database, I would expect it to return 3, since there are three Nbr RMAs that have elements on them that were not received.

I tried the following queries without success:

  SELECT Count (RMANbr)
 FROM RMAItems
 WHERE RecvDate Is Null;

(returns 4)

  SELECT Count (*)
 FROM RMAItems
 WHERE RecvDate Is Null;

(returns 4)

  SELECT Count (*)
 FROM (SELECT DISTINCT RMANbr FROM RMAItems)
 WHERE RecvDate Is Null;

(when this query is run, a dialog box appears with a text box and "RecvDate" above the text box)

As already mentioned, I work with MS Access 2003 on a computer running Windows XP Pro SP3.

I made several Google's, but did not come up with a query that would do what I needed to do it (that I came up with a subquery above that didn't work). Any help you can provide would be super.

Thanks.

+4
source share
1 answer

Use an approach similar to your third pattern, but move the WHERE to the subquery. The reason you got the options dialog box for RecvDate because this field was not included in the SELECT subquery list, so it was unknown / inaccessible to the parent query.

 SELECT Count(*) FROM ( SELECT DISTINCT RMANbr FROM RMAItems WHERE RecvDate Is Null ) AS sub;
+2
source

More articles:


All Articles