Identifying Firsts in a Query in One Go for Report?

[Banana]

I have a report that summarizes the visits made to our office. I need to be able to identify those who made their first visit to our office in a given month. There is no Yes/No field to track that as I reasoned that it is "calculated" so ergo shouldn't be stored.

However, I'm stumped on how to make my query tick off the First visit of a unique record. The current SQL is:

SELECT tblVisit.VisitID, tblVisit.VisitDate, Nz([FirstName],"") & " " & Nz([LastName],"") AS FullName, tblVisit.FirstVisit, tblVisit.NewCase
FROM tblPerson INNER JOIN (tblClients INNER JOIN tblVisit ON tblClients.PersonID = tblVisit.PersonID) ON tblPerson.PersonID = tblClients.PersonID
ORDER BY tblVisit.VisitDate, Nz([FirstName],"") & " " & Nz([LastName],"");

When I add a column with the SQL:

UDupe: IIf(First([tblClient].[PersonID]), -1, 0)

I get a error saying that I did not include VisitID as a part of domain aggregate. Fiddling with it only gets me the same error on PersonID.

I suppose I could use UNION query but I'm sure there is a way to make a single query with the indication that this is a unduplicated visit of the month.

Any suggestions?

 

[Banana]

Fiddling with UNION query now.

SELECT qtrODHHRepeatClient.PersonID, qtrODHHRepeatClient.VisitID, qtrODHHRepeatClient.VisitDate, qtrODHHRepeatClient.FullName, qtrODHHRepeatClient.FirstVisit, qtrODHHRepeatClient.NewCase, -1 AS Dupe
FROM qtrODHHRepeatClient
WHERE (((qtrODHHRepeatClient.PersonID) In (SELECT [PersonID] FROM [qtrODHHRepeatClient] As Tmp GROUP BY [PersonID] HAVING Count(*)>1 )) AND ((qtrODHHRepeatClient.VisitDate) Not In (SELECT Min(Tmp.VisitDate) AS FirstVisitDate FROM qtrODHHRepeatClient AS Tmp GROUP BY Tmp.PersonID HAVING (((Count(Tmp.PersonID))>1));)))
ORDER BY qtrODHHRepeatClient.VisitDate;
UNION all
SELECT qtrODHHRepeatClient.PersonID, qtrODHHRepeatClient.VisitID, qtrODHHRepeatClient.VisitDate, qtrODHHRepeatClient.FullName, qtrODHHRepeatClient.FirstVisit, qtrODHHRepeatClient.NewCase, 0 AS Dupe
FROM qtrODHHRepeatClient
WHERE (((qtrODHHRepeatClient.PersonID) In (SELECT [PersonID] FROM [qtrODHHRepeatClient] As Tmp GROUP BY [PersonID] HAVING Count(*)>1 )) AND ((qtrODHHRepeatClient.VisitDate) In (SELECT Min(Tmp.VisitDate) AS FirstVisitDate FROM qtrODHHRepeatClient AS Tmp GROUP BY Tmp.PersonID HAVING (((Count(Tmp.PersonID))>1));)))
ORDER BY qtrODHHRepeatClient.VisitDate;

However, this does not catch duplicates correctly for reasons I've yet to fathom. I"m also worried that using a union query would make it very difficult for me to use WhereCondition to limit the results to month as the queries uses subqueries which might not be necessarily filtered (or even if they were, would pull too much data from Jet).

Suggestions?

 

[Banana]

Color me stumped.

I now realize that my "Find First visit" query isn't working correctly. While I can create a subquery to get first visit of the month from clients and use to exclude from my "Find duplicate visits", I can't get it to work in "Find First Visit" as I get error saying I didn't include VisitID as part of domain aggegrate. Why can I make a query using VisitDate in aggegrate functions, but not VisitID reallys irks me.

I need my "Find First Visit" to be in same format as "Find Duplicate Visit" to be able to union them together, correct? The subquery is in different format. (doesn't include all columns, only clientID and visitdate)

Can anyone at least explain to me what is Access trying to say about having certain fields in domain aggegrate? At first I thought it mean that I had to make it a part of the query, or have query depend on that field, but doing both did nothing to help the case.

 

[Banana]

This is the latest Union query I have so far. It *still* doesn't get things right after union. (The queries I united together seems to work fine, though)

SELECT qtrODHHRepeatClient.PersonID, qtrODHHRepeatClient.VisitDate, qtrODHHRepeatClient.FullName, qtrODHHRepeatClient.FirstVisit, qtrODHHRepeatClient.NewCase, -1 AS Dupe
FROM qtrODHHRepeatClient
WHERE (((qtrODHHRepeatClient.PersonID) In (SELECT [PersonID] FROM [qtrODHHRepeatClient] As Tmp GROUP BY [PersonID] HAVING Count(*)>1 )) AND ((qtrODHHRepeatClient.VisitDate) Not In (SELECT Min(Tmp.VisitDate) AS FirstVisitDate FROM qtrODHHRepeatClient AS Tmp GROUP BY Tmp.PersonID HAVING (((Count(Tmp.PersonID))>1));)));
UNION ALL
SELECT  Tmp.PersonID, Min(Tmp.VisitDate) AS FirstVisitDate, Tmp.FullName, Tmp.FirstVisit, Tmp.NewCase, 0 AS Dupe
FROM qtrODHHRepeatClient AS Tmp
GROUP BY Tmp.PersonID, Tmp.FullName, Tmp.FirstVisit, Tmp.NewCase, 0
HAVING (((Count(Tmp.PersonID))>1))
ORDER BY qtrODHHRepeatClient.VisitDate;

 

[Karma]

This might give you some ideas.

HitH

K.

 

[Banana]

Bingo! I was making progress then realized that my code only work for the *very* first visit, not necessarily by month. Your sample gave the clue I needed (I didn't use a Where expression to restrict the query result to a given month).

Thanks!

 

[Karma]

No worries