Count records based on two sorted columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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;
 
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!
Click to expand...
Click to expand...
 
Jump away -- You are correct in that a crosstab will not run on a calculated
"Rank" field but first must be stored in a table.

--
KARL DEWEY
Build a little - Test a little


Gary Walter said:
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!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count Unique Field Values 1
Crosstab Query 2
Looping through data with calculations 1
Add Previous record to Current record 3
Previous Date in a Query 1
Date Brackets as an IIf statement 3
Sort order and seconday sort order 1
Sum, group, date 2

Back
Top