Query to update ranking value in order of record date not working

[penfold1234]

Hi, I wonder if you can help?

I have an update query which I would like to use in order to update a number of records in a table. I want to assign each applicable record a ranking based on the record start date; earliest dated record gets the lowest ranking, incrementing by one each time. . . . so a set of 5 records receive the rankings 1,2,3,4,5 based on their start date.

I have written a select query called ‘RecordCountUpdateQuery’ which provides me with the sub group of records which need to be ranked and sorts them by StartDate. I have written the following update query to provide the ranking itself:

[/SIZE][/FONT]
[FONT=Arial][SIZE=3] [/SIZE][/FONT]
[FONT=Arial][SIZE=3]UPDATE RecordCountUpdateQuery SET RecordCountUpdateQuery.RecordCount = DCount("GroupID","RecordCountUpdateQuery","StartDate <= [StartDate]");[/SIZE][/FONT]
[FONT=Arial][SIZE=3] [/SIZE][/FONT]
[FONT=Arial][SIZE=3][Code][/SIZE][/FONT]
[FONT=Arial][SIZE=3] [/SIZE][/FONT]
[FONT=Arial][SIZE=3]The problem is that the query updates all 5 records with the DCOUNT value, so I am left with a group of records which all have the record count value of 5 rather than 1,2,3,4,5. I had thought that the update query would execute for each line / record in the RecordCountUpdateQuery and as they were in order of date, the last portion of the DCOUNT query would cause the number to reduce by one each time. However this doesn’t happen. The query doesn’t appear to be looping or taking account of the last part of the DCOUNT.[/SIZE][/FONT]
[FONT=Arial][SIZE=3] [/SIZE][/FONT]
[FONT=Arial][SIZE=3]Is there a way I can get this to work. I’m not too hot on SQL so would ideally like to keep this simple if possible. [/SIZE][/FONT]
[FONT=Arial][SIZE=3] [/SIZE][/FONT]
[FONT=Arial][SIZE=3]Any help would be greatly appreciated . . . . [/SIZE][/FONT]
[FONT=Arial][SIZE=3] [/SIZE][/FONT]
[FONT=Arial]Thanks[/FONT]

 

[John Big Booty]

Ranking would seem to be a calculated field, and it is generally recommend not to store such data, as it can calculated in a query at any time it is required for display purposes in either a Form or a report.

Perhaps the attached sample will give you some pointers.

 

[penfold1234]

Hi John,

Thanks so much for getting back to me so quickly. I can see the logic behind your example and would definitely use this approach in the future because I agree that storing a calculated filed isn't good practice. That said, I'm not sure that I can avoid it in this instance as so many other elements in the database rely on the ranking being in the table. It wasn’t a problem until users were given the ability to delete records, which throws the rankings out . . . Hence the need for this update query to ‘re assign’ the ranking at the point of deletion. If this is the only way then I will have to re-engineer some what and risk opening a can of worms. . . However is there a way to get the update to work in this instance (even though it isn’t very good practice)?

Sorry to be a pain!

Thanks

 

[John Big Booty]

There are always exceptions to rules, if your situation is one I can't comment

However you should be able to bash my example so that instead of a select query the final query is an update query

 

[Brianwarnock]

Hi John,
Hence the need for this update query to ‘re assign’ the ranking at the point of deletion.
Thanks

So how is the deletion done, do they select the record from a form and hit a command button which causes the delete query to run?
If so then it can also cause an update query to run using the info from the Form to subtract 1 from all rankings higher than the one being deleted, then do the deletion.

I think that should work.

brian

 

[penfold1234]

Hi Brian,

Yes that is exactly what I am planning to do. This doesn't happen too often (only when duplications have been made in error). A screen allows a record ID to be selected from a drop down in order to be deleted. When a button is pressed a macro instructs a delete query to remove the record with the ID matching that selected. What I plan to do is to get the macro to run the re-ranking query either before or after the delete. The trouble is I am going mad trying to get the think to rank. I was using a DCOUNT before posting this question, but have been looking at using Count since reading John's posting, but in every case all I can get the query to do is to set all count fields to the total number of records. It is driving me mad!!

Do you have any ideas? I am using Access 2002.

 

[Brianwarnock]

What I am suggesting is that before the deletion pull the rank of the record to be deleted, then the update query just subtracts 1 from all ranks > than that, Big John's approach can be run at anytime to clean up the file which has its advantages.

Brian

 

[penfold1234]

Why I didn't think of that I am not sure . . . . I guess you go so far down one track you loose sight of other (more simple) options. . . .

Thank you Brian I'll give that a go!