views:

83

answers:

2

I'm using Access via OleDb. I have a table with columns ID, GroupID, Time and Place. An application inserts new records into the table, unfortunately the Place isn't calculated correctly.

I want to update each record in a group with its correct place according to its time ascending.

So assume the following data:

ID     GroupId  Time   Place
Chuck  1        10:01  2
Alice  1        09:01  3
Bob    1        09:31  1

should result in:

ID     GroupId  Time   Place
Chuck  1        10:01  3
Alice  1        09:01  1
Bob    1        09:31  2

I could come up with a solution using a cursor but that's AFAIK not possible in Access.

+2  A: 

I just did a search on performing "ranking in Access" and I got this support.microsoft result.

It seems you create a query with a field that has the following expression:

Place: (Select Count(*) from table1 Where [Time] < [table1alias].[Time]) + 1

I can't test this, so I hope it works. Using this you may be able to do (where queryAbove is the above query):

UPDATE table1
SET [Place] = queryAbove.[Place]
FROM queryAbove
WHERE table1.ID = queryAbove.ID

It's a long shot but please give it a go.

Codesleuth
unfortunately, I found out access doesn't support subselects in update statements, you can use the DCount function (or any similar) to do it, but it's rather limited for my purposes. I will accept this answer as it pointed me in the right direction to solve this problem, albeit it's not compatible with access.
Johannes Rudolph
Oh, that's odd. The article I linked references Microsoft Access 2000 Standard Edition, so I thought that would work. But thanks for the tick anyway :) When you get a concrete solution for this, it would be great if you could post it as an answer, as I surely would like to see it in action as I guess other will be in the future.
Codesleuth
A: 

I don't think time is a number or time formatted column, time is unfortunately a text string containing the numbers and dilimetrs of the time format. This is why sorting after the time column is illegal. Removing the dilimiters ":" and "," casting to integer and then sorting numirically could do the job

anonymousB
huh? The time column is actually *not* my problem. Even if so, your "solution" is still wrong for the problem not at hand.
Johannes Rudolph