SELECT TOP 1 - Does not work for me. 1

[regava]

Access 2000 - I have the following query:

SELECT  tblTracking.Tatno, tblTracking.Taxpayer, qryTick.Item, qryTick.ActDate
FROM tblTracking INNER JOIN qryTick ON tblTracking.Tatno = qryTick.TatNum
WHERE (((tblTracking.Consol)="N") AND ((tblTracking.Related)="N") AND ((IsNull([tblTracking].[ClosedDate]))<>False))
ORDER BY tblTracking.Tatno;

qryTick is UNION query. When I used it as shown it returned lots of records which are correct. However what I need is only one from each tblTracking.Tatno. I inserted TOP 1 after Select and I get only two records from the same tblTracking.Tatno. What am I doing wrong? Any help is appreciated.

 

[Shawn12]

When you add the TOP 1 in there do the values its returning all have different dates? It looks to me like this would return the TOP 1 for each date in that table.

 

[regava]

Shawn12 thank you for your reply. When I add the TOP 1, I get only two values, same TATNo and different dates. Please note I should be getting more that ten values, each values having a different TATNo and some of them may have the same date. In other words, I should (must) not get more that one value with the same TATNo.

 

[Remou]

Have you tried SELECT DISTINCT?

 

[Golom]

I think you need a correlated sub query. You want only one Tatno record ... but which one? In the following I'm assuming that it's the one with the latest ActDate.

SELECT T1.Tatno, T1.Taxpayer, K.Item, K.ActDate

FROM tblTracking As T1 INNER JOIN qryTick As K ON T2.Tatno = K.TatNum 

WHERE K.ActDate =

(
SELECT MAX(T2.ActDate)

FROM tblTracking As T2 INNER JOIN qryTick As K ON T2.Tatno = K.TatNum

WHERE T2.Tatno   = T1.Tatno
  AND T2.Consol  = "N"  
  AND T2.Related = "N" 
  AND IsNull(T2.[ClosedDate])

)

 

[regava]

Remou - I tried using DISTINCT - same result.

Golom - I tried your query and the following happened:
1. Syntax error in JOIN operation, pointing the T2.Tatno in line 2.
2. Then I changed it to T1.Tatno, I was asked to enter parameter for T2.ActDate.

Any Advice?

 

[Golom]

Sorry ... got my aliases mixed up

SELECT T1.Tatno, T1.Taxpayer, K.Item, K.ActDate

FROM tblTracking As T1 INNER JOIN qryTick As K ON T2.Tatno = K.TatNum 

WHERE K.ActDate =

(
SELECT MAX(K2.ActDate)

FROM tblTracking As T2 INNER JOIN 
     qryTick As K2 ON T2.Tatno = K2.TatNum

WHERE T2.Tatno   = T1.Tatno
  AND T2.Consol  = "N"  
  AND T2.Related = "N" 
  AND IsNull(T2.[ClosedDate])

)

 

[regava]

Golom - Thank you very much. I got all the records except for 7. I will look into it. If it is worth posting I will do that.
To all thak you and I do appreciate your effort and time. Have a wonderful long week end.