Numbering by Name

[David127]

I've got a list of quality observations for many individuals & I want to
number the observations by name, & date. I also need the numbering to restart
with each new month. Results should be something like:

Name Month Obs#
David Jan 1
David Jan 2
David Jan 3
David Feb 1
Alex Jan 1
Alex Jan 2
Alex Feb 1

Thanks!

 

[KARL DEWEY]

Post the table structure, field names and datatype, where the observations
are recorded.
Post sample data.
Post what you would want the results to look like.

 

[John Spencer]

BY name and date -
Assuming that there is an actual date and not just a month name and there is
only one record for any name and date combination then

SELECT [Name], Month([Date]) as MonthName
, 1 + (SELECT COUNT(*) FROM YourTable as Tmp
WHERE Tmp.[Name] = YourTable.[Name]
And Month(Tmp.[Date]) = Month(YourTable.[Date])
AND Tmp.[Date] < YourTable.[Date]) as Rank
FROM YourTable

Of course, if you have more than one year's worth of data, then you need to
change all the Month([Date]) to Format([Date],"yymm").

This will be slow. You might find the following faster, but no guarantee

SELECT X.[Name], Format(X.[Date],"yymm") as MonthName, Count(Tmp.Date) as Rank
FROM YOURTable as Y LEFT JOIN YourTable as X
ON Y.Name = X.Name
and Format(Y.Date,"yymm") = Format(X.Date,"yymm")
AND Y.Date <= X.Date
GROUP BY X.[Name], Format(X.[Date],"yymm")



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County