Adding record number to existing table

[argear]

I'm trying to write an update query to add a record number to a group of
records.

The table looks like this:

ID Name Date Day

1 Jane 1/1/07
1 Jane 1/2/07
2 John 1/5/07

I'm trying to update the "Day" field where the count starts over for each
new ID number at 1 and then runs sequentially. Can anyone help?

 

[KARL DEWEY]

Substitute your table and field names --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points < Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

If you have identical ranks it gives both the higher number and skips one to
the next like this ---
Group Points Rank
1 23 1
1 23 1
1 33 3
2 43 1
2 51 2
3 43 1
3 54 2
3 56 3
3 65 4
4 13 1
You would then need to edit the duplicates.

 

[argear]

I added in my field names, and I'm getting the rankings in reverse order
going down to 2.

So it's

ID Name Date Day
1 Jane 1/1/07 4
1 Jane 1/2/07 3
1 Jane 1/5/07 2
2 John 1/2/07 2

Right now this is the SQL of my query:

SELECT [Test Outlier day].ID, [Test Outlier day].NAME, [Test Outlier
day].[SERV DATE], (Select COUNT(*) from [Test Outlier day] AS T Where T.ID =
[Test Outlier day].ID And [Test Outlier day].[SERV DATE] <= T.[SERV DATE])+1
AS [Day]
FROM [Test Outlier day]
ORDER BY [Test Outlier day].ID, [Test Outlier day].[SERV DATE];

Substitute your table and field names --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points < Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

If you have identical ranks it gives both the higher number and skips one to
the next like this ---
Group Points Rank
1 23 1
1 23 1
1 33 3
2 43 1
2 51 2
3 43 1
3 54 2
3 56 3
3 65 4
4 13 1
You would then need to edit the duplicates.
--
KARL DEWEY
Build a little - Test a little

I'm trying to write an update query to add a record number to a group of
records.

The table looks like this:

ID Name Date Day

1 Jane 1/1/07
1 Jane 1/2/07
2 John 1/5/07

I'm trying to update the "Day" field where the count starts over for each
new ID number at 1 and then runs sequentially. Can anyone help?

 

[KARL DEWEY]

Try this as it changes <= to >= and removes the +1 ---

SELECT [Test Outlier day].ID, [Test Outlier day].NAME, [Test Outlier
day].[SERV DATE], (Select COUNT(*) from [Test Outlier day] AS T Where T.ID =
[Test Outlier day].ID And [Test Outlier day].[SERV DATE] >= T.[SERV DATE])
AS [Day]
FROM [Test Outlier day]
ORDER BY [Test Outlier day].ID, [Test Outlier day].[SERV DATE];

--
KARL DEWEY
Build a little - Test a little

I added in my field names, and I'm getting the rankings in reverse order
going down to 2.

So it's

ID Name Date Day
1 Jane 1/1/07 4
1 Jane 1/2/07 3
1 Jane 1/5/07 2
2 John 1/2/07 2

Right now this is the SQL of my query:

SELECT [Test Outlier day].ID, [Test Outlier day].NAME, [Test Outlier
day].[SERV DATE], (Select COUNT(*) from [Test Outlier day] AS T Where T.ID =
[Test Outlier day].ID And [Test Outlier day].[SERV DATE] <= T.[SERV DATE])+1
AS [Day]
FROM [Test Outlier day]
ORDER BY [Test Outlier day].ID, [Test Outlier day].[SERV DATE];

Substitute your table and field names --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points < Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

If you have identical ranks it gives both the higher number and skips one to
the next like this ---
Group Points Rank
1 23 1
1 23 1
1 33 3
2 43 1
2 51 2
3 43 1
3 54 2
3 56 3
3 65 4
4 13 1
You would then need to edit the duplicates.
--
KARL DEWEY
Build a little - Test a little

I'm trying to write an update query to add a record number to a group of
records.

The table looks like this:

ID Name Date Day

1 Jane 1/1/07
1 Jane 1/2/07
2 John 1/5/07

I'm trying to update the "Day" field where the count starts over for each
new ID number at 1 and then runs sequentially. Can anyone help?