Get the total number of previous records

Question

I am creating a query in MS Access that needs to update newly created field with the total number of previous records. Something like this:

Original table:

|  ID | Name |
--------------
|  1  | Mark |
--------------
|  2  | Mich |
--------------
|  3  | Isak |
--------------

Now I add another column ( of type int ), e.g. Ordinal_Number :

|  ID | Name | Ordinal_Number |
-------------------------------
|  1  | Mark |                |
-------------------------------
|  2  | Mich |                | 
-------------------------------
|  3  | Isak |                | 
-------------------------------

Now I need to fill Ordinal_Number with the number of previous records :

|  ID | Name | Ordinal_Number |
-------------------------------
|  1  | Mark |       0        |
-------------------------------
|  2  | Mich |       1        | 
-------------------------------
|  3  | Isac |       2        | 
-------------------------------

I couldn't even get the idea how to try fighting this problem so I am asking here.

Is there a reason why `Ordinal_Number` is not simply `id - 1`? (Previous comment had `=` rather than `-`.) — Gordon Linoff, May 11 '14 at 05:11
@GordonLinoff: No there isn't. I thought that putting zero will explain my problem better. — AlwaysLearningNewStuff, May 11 '14 at 05:15

Olesya Razuvayevskaya · Answer 1 · 2014-05-05T08:02:32.623

1

 SELECT a.*, (SELECT COUNT(*) FROM Original_table WHERE a.ID>=ID) as Ordinal_Number
 FROM Original_table as a
 ORDER BY a.ID

For the update:

UPDATE Original_table o
set  Ordinal_Number=
(SELECT COUNT(*) FROM Original_table WHERE o.ID>=ID)

or for jet4 version

UPDATE Original_table o 
inner join
(SELECT a.*, (SELECT COUNT(*) FROM Original_table WHERE a.ID>=ID) as Ordinal_Number
 FROM Original_table as a
 ORDER BY a.ID) newt
on o.ID=newt.ID
Set o.Ordinal_Number=newt.Ordinal_Number

edited May 05 '14 at 08:02

answered May 05 '14 at 06:16

Olesya Razuvayevskaya

1,148
1
7
10

[This question](http://stackoverflow.com/questions/170578/operation-must-use-an-updatable-query-error-3073-microsoft-access) seems to explain my problem... – AlwaysLearningNewStuff May 05 '14 at 07:42
Right, if you are using get4, you cannot execute joint quesries within your update statement – Olesya Razuvayevskaya May 05 '14 at 07:49
Just in case, I have edited the quesy with alternative join clause. See if it works. But most likely, the compiler will treat the subquery as related – Olesya Razuvayevskaya May 05 '14 at 07:53
you have to add commit at the end of the statement :) i.e., after you execute everything, separately run commit; so that all the changes would be added into the table – Olesya Razuvayevskaya May 05 '14 at 08:13
I have tried adding `; commit;` at the end of your query but got errors. What am I doing wrong??? – AlwaysLearningNewStuff May 05 '14 at 08:17
Character found at the end of SQL statement. I am creating query in *Design View* in MS Access 2007. I have switched to *SQL View* and typed the query. – AlwaysLearningNewStuff May 05 '14 at 08:24
Ok. Access does not support several statements in one query. SO, you have to execute one. And then the commit; in a separate query. I suppose, you have already executed the first one. SO, all you need now is to run the commit; – Olesya Razuvayevskaya May 05 '14 at 08:35
I do not know how to form the second query. I have tried using transactions but keep getting error *Invalid SQL statement; Expected Select...* Can you help me with this last step? Thank you. Best regards. – AlwaysLearningNewStuff May 06 '14 at 20:29
Have you tried using the update type of query in your SQL view? – Olesya Razuvayevskaya May 07 '14 at 06:20

score 0 · Answer 2 · answered May 05 '14 at 06:15

In order to even begin to talk about getting values from a previous record, you have to define what "previous" means in the context of your table. For most purposes, Access treats a table as a "big bag of records", with no defined order -- most specifically, records are not inherently ordered by entry sequence. In any situation where you want to talk about "previous" or "next" records, you must specify a sort order for the records -- for example, in the ORDER BY clause of a query. And that sort order must be defined in terms of data in the records.

If you need to process records in order of entry, then you need to have a date/time field in the records that is set to Now() whenever a record is entered. If your records have a consecutive autonumber primary key, then In carefully restricted circumstances, you might use that key field as an indicator of the entry sequence. However, not all tables use autonumber keys, and even in those that do, autonumbers can become random, not consecutive, under certain circumstances. So if you care about entry sequence, you really ought to use a date/time field to store the information.

Once you have established a field that you can use to determine the sequence of records, you don't really need to store the current inventory total in any record. It can always be calculated on the fly by adding up the transaction quantities of all records entered up through the current one. In some cases, it may be useful to store it for efficiency's sake, but that runs the risk of having inconsistent data if, for example, somebody modifies one of the earlier records without recalculating the total in all subsequent records. So I don't recommend storing this calculated data unless you find you really need to.

I have autonumber as primary key that starts from 1. The problem is deletion of records. If I delete second record from above, I will have `1` and `3` as ID. Since I need to display the ordinal number of the record I have decided to add a new field and just fill it in with the number of previous records + 1. — AlwaysLearningNewStuff, May 05 '14 at 06:29

Gordon Linoff · Answer 3 · 2014-05-11T05:10:54.887

0

The ordinal number starts with 0, so I would do:

update Original
    set Ordinal_Number = (select count(*)
                          from Original as o2 
                          where o2.ID <= original.ID
                         ) - 1;

edited May 11 '14 at 05:10

answered May 11 '14 at 02:05

Gordon Linoff

1,242,037
58
646
786

It didn't work. Let me first clarify things : I am copying this query in MS Access, in query's *SQL View*. After running it, a dialog box pops up prompting me to enter value for `o2.ID`. After I do that the same dialog pops up only this time asks for `o.ID`. After I enter number, I get error *Operation must use an updatable query*, which makes me think that the problem I have is described in [this question](http://stackoverflow.com/questions/170578/operation-must-use-an-updatable-query-error-3073-microsoft-access?lq=1). Any thoughts on how to fix it? Thank you for replying. – AlwaysLearningNewStuff May 11 '14 at 02:54

Get the total number of previous records

3 Answers3