Query Minimum Date

[jsic1210]

Hello,

I have a table called tblNameHistory. When one of our companies changes it's legal name, the old name is stored in this table, along with a date that it was changed and its Customer ID (COID). The unique identifier is LegalID. I'm having trouble pulling just the minimum date (by COID) and its former name. For example:

LegalID     COID     Former Name     Change Date
      1     12345    ZYX Co.         1/1/2001
      2     99999    Nine Co.        5/1/2001
      3     12345    ABC Co.         6/1/2010

I want it to pull out 99999 (Nine Co.) and just the 12345 that is ZYX Company (as it has the earlier [Change Date]). Is there a way to do this and pull the former name? With my query, it's pulling each separate LegalID, not distinct COIDs. Here is my SQL statement:

SELECT DISTINCT tblNameHistory.COID, Min(tblNameHistory.ChangeDate) AS [Min Change Date], tblNameHistory.FormerName
FROM tblNameHistory
GROUP BY tblNameHistory.COID, tblNameHistory.FormerName
HAVING (((tblNameHistory.COID) Is Not Null) AND ((Min(tblNameHistory.ChangeDate)) Is Not Null));

Thanks!

 

[CJ_London]

There are two ways to to this, a groupby query as you have or using a subquery or dlookup. In this scenario, Dlookup is not very efficient so the two options are groupby and sub query:

Your groupby is also grouping by name - and since it is different names you will get two rows (also, the DISTINCT is not required in GroupBy queries and the Having is not necessary) Try:

SELECT DISTINCT tblNameHistory.COID, Min(tblNameHistory.ChangeDate) AS [Min Change Date] 
FROM tblNameHistory
GROUP BY tblNameHistory.COID

But this won't give you the old name.

Using a sub query, try this:

SELECT * FROM tblNameHistory
WHERE ChangeDate=(SELECT MIN(ChangeDate) FROM tblNameHistory as Tmp WHERE COID=tblNameHistory.COID)

 

[jsic1210]

Awesome, that worked. Thanks!
By your statement "FROM tblNameHistory As tmp," does that create a subquery? And is tmp a reserved word, or just a variable?

 

[Brianwarnock]

Its the whole

=(SELECT MIN(ChangeDate) FROM tblNameHistory as Tmp WHERE COID=tblNameHistory.COID)

that creates the subquery.
Tmp is just a chosen alias for the table, it could be almost anything, it is given to avoid conflicts with the use of the original table name.

Brian

 

[CJ_London]

beat me to it with the explanation