Please pardon me for jumping in...
In addition to Karl's sage help...
You might miss a typo (plus Karl apparently graciously
did not mention choice of field names which are Access
reserved words -- which you should change, or at least
wrap in brackets in query). Maybe these are only "example"
names, but you have definitely scored a trifecta!
SELECT
[Name],
[DATE],
(SELECT COUNT(*)
FROM [YourTable] T1
WHERE
T1.[Name] = T.[Name]
AND
T1.[DATE] <= T.[DATE]) AS [Number]
FROM [YourTable] AS T
ORDER BY [DATE];
{end nitpicking w/apologies}
The other reason I wanted to respond was because
I have found that sometimes there can be a reasonable
excuse to store a "Rank" -- for example, basing a crosstab
on the rank field.
If it is truly necessary, you will need to use a domain
function (constructed similarily to subquery above)
since update queries choke on aggregation (except
in the WHERE clause)
something like (now my typing may be suspect)
(replace yurtable with actual name of your table)
UPDATE
yurtable As T
SET T.[Number] =
DCount("*","yurtable","[Name]='" & T.[Name] & "' AND [DATE]<=#" & T.[DATE] &
"#");
This will be slow, but can make a further crosstab snap.
A faster alternative though is to use a "report table"
that you empty, then use select query like Karl's
to append needed data into this "report table."
INSERT INTO ... will have no problem with aggregating subquery
in this case (I believe).
INSERT INTO tblReport (AName, ADate, ANumber)
SELECT
[Name],
[DATE],
(SELECT COUNT(*)
FROM [YourTable] T1
WHERE
T1.[Name] = T.[Name]
AND
T1.[DATE] <= T.[DATE]) AS [Number]
FROM [YourTable] AS T
ORDER BY [DATE];
KARL DEWEY said:
You should not be storing the number as it will always need to be updated
as
new records are added. You should calculte it as needed.
Try this query --
SELECT Name, DATE, (SELECT COUNT(*)
FROM [YourTable] T1
WHERE T1,Name = T.Name AND T1.DATE <= T.DATE) AS Number
FROM [YourTable] AS T
ORDER BY DATE;
--
KARL DEWEY
Build a little - Test a little
Backin said:
Hope someone here has a suggestion. I have three columns in my table
(Name,
Date, Number) and would like to update the Number field with a count of
records based on the Name and Date fields. The Name field is sorted and
the
count begins with the latest Date for that particular name.
Here is an example:
Name Date Number
Bob 8/1/04 4
Bob 9/15/04 3
Bob 2/3/05 2
Bob 1/6/06 1
John 10/1/04 2
John 12/1/04 1
Jerry 11/4/05 1
Susan 1/3/04 3
Susan 11/4/04 2
Susan 12/4/05 1
Wayne 2/2/04
Wayne 3/4/04
Wayne 12/3/04
Wayne 1/5/05
So Wayne's column would be updated with 4,3,2,1
Any idea's how to update the Number column so I don't need to do this
manually. There are 107000 rows in this table.
Thanks!
