Design query to compare data in one table -- Retain one record for each customer name

[OB7]

Good afternoon,



I have table that contains the following fields (among others, which are not relevant):

1. Customer Last name
2. Customer First name
3. Application Date

Within this table, one customer may have multiple Application Dates and/or an Application Date with a null value. I would like to get the list of all customers who have ONLY a null Application Date. (If a customer has ANY records with a date in this field, I would like that customer excluded from my list.)

Is this possible using Design View in Access 2007? If not, is there SQL code that can be used in Access 2907 to accomplish this task?

Related to this question, I'm working on a similar problem where there are multiple (and variable in number) records for one customer and I would like to choose the one record for each customer with the most current date in the date field.

Any assistance would be greatly appreciated!

[June7]

Maybe:

For the first query:

SELECT * FROM tablename WHERE [Application Date] Is Null;

The second query can be tricky. Review http://allenbrowne.com/subquery-01.html#TopN

[OB7]

Thank you!

Your response made me reailize that I did not state the question clearly. For the Application Date issue, I would like my list to include customers who have ONLY a null value for Application Date. In other words, if customer has one record with a null value for Application Date, and that same customer has another record with a date in the Application Date field, I would NOT want that customer included in my list. I would like to include only those customers who have no records with a dated Application Date field. Thanks again!

[June7]

That does complicate. Both of your requirements involve each record considering value in other records in same table as criteria. Never simple. Need subquery (or several query objects) or domain aggregate function.

Do a query that counts each client's records:

SELECT ClientID, Count(*) AS ClientCount FROM tablename GROUP BY ClientID;

Now join that query to the original table linking on the common ClientID. Set filter criteria. Under the ClientCount field =1. Under the Application Date Field Is Null.

Or use DCount (beware, domain aggregates can be slow):

SELECT * FROM tablename WHERE [Application Date] Is Null AND DCount("*", "tablename", "ClientID=" & [ClientID])=1;

[OB7]

Thanks, June 7, for your response! Unfortunately, the table I'm using does not have a unique clientID, and I do not have rights to change the table. Therefore, in place of the ClientID field in your code, I'll need to concatenate customer Last_Name and customer First_Name fields into a new field called customer Name in order to count each customer's records. I'm new to SQL and receiving syntax error messages upon my attempts to add this to your code. Can you tell me how to correct the code below such that it counts the records for each customer Name, where Name is concatenated last and first names:

SELECT ([LAST_NAME]& ", " &[FIRST_NAME]) AS NAME,
NAME, Count(*) AS NameCount
FROM <TableName>
GROUP BY NAME
ORDER BY NAME;

[John_G]

Hi -

First, you have the field "Name" in the select list twice, first as the alias for the concatenated fields, and again by itself at the beginning of the second line.

Second, you cannot use the alias in the Group By or Order by - you have to use the original expression ([LAST_NAME]& ", " &[FIRST_NAME])

John

[June7]

Names are poor unique identifiers. What if you have more than one person named John Smith?

[OB7]

Thank you, John-G!

[OB7]

Thanks, June7. Yes, I agree that names are not good identifiers. However, that's all I have to work with in this case. Fortunately, the population and the time window are both small. In most cases, my tables do contain a field which uniquely identifies each customer.

Thank you also for the extremely useful reference you sent earlier on subqueries! Much appreciated!