Sequence numbers

[Guest]

I have a table which holds students answers to multiple choice examination
questions (A, B, C, etc). I need to select records according to the subject,
make a table of these records and then format the table according to the
requirements of a third party system to which the data is to be transferred.

I am doing it by using a macro to run a make table query and then a number
of update queries. It's all straight-forward except for one requirement.
The recipient of the data requires that the 3rd field of each record should
contain a sequence number (001 for the first record, 002 for the second, and
so on). The field is empty at the moment - can you suggest how I might
populate the field to meet this requirement. The job will then be complete.

Thanks for any help - as ever!

Jim Jones
Gaborone
Botswana

 

[Allen Browne]

Presumably you have some kind of primary key in your table, which:
a) uniquely identifies the record, and
b) defines the sort order for these records.
The concept of "first record", "second record" would be meaningless without
that.

Assuming a table named Table1, with a numeric primary key named ID, you
could use an Update query to populate your field with this expression:
DCount("*", "Table1", "ID <= " & [ID])

Hopefully this is just for some temporary purpose: the idea of storing these
dependent values is unnormalizing your table.

 

[Tom Ellison]

Dear Jim:

Are the rows you place in these results sorted in some manner? And, if so,
is that based on a key that guarantees uniqueness? If so, please post the
query that returns everything you have now with that missing column, and
I'll code it for you. This query should include an ORDER BY clause that
reflects the unique ordering for these rows. I need that to create the
"Ranking" (which is a general term for the kind of sequence number you
want).

Tom Ellison

 

[Guest]

Dear Tom

Many thanks for your prompt response and for your offer of help.

Each record contains a field [centre] which identifies the school and a
field [cand]which identifies the student in that school. These fields
guarantee the uniqueness of the record. The recipient of the file does not
specify any order for the records. However, I use [centre] and [cand] to
order the record, and the make table query terminates with the following SQL
statement

ORDER BY [OMR Responses 40].centre, [OMR Responses 40].cand;

(The table is called OMR Resonses 40)

Other fields contain the subject code and the student's answers. There are
also fields which contain text or blank fillers specified by the recipient
although meaningless to me. These fields contain the same data for all
records and so are easy to deal with.

Thanks again

Jim Jones
Gaborone
Botswana

 

[Guest]

Dear Allen

Thanks for the quick response and for the help. The job is certainly only
for a temporary purpose - I need to transfer data in a format specified by
the recipient. I will, of course, keep the main data table, but the table
containing the selected data and the formatting will be deleted once I know
that the data transfer has taken place successfully

I will include a primary key as you suggest and try the Dcount expression.

Thanks again

Jim Jones
Gaborone
Botswana

Presumably you have some kind of primary key in your table, which:
a) uniquely identifies the record, and
b) defines the sort order for these records.
The concept of "first record", "second record" would be meaningless without
that.

Assuming a table named Table1, with a numeric primary key named ID, you
could use an Update query to populate your field with this expression:
DCount("*", "Table1", "ID <= " & [ID])

Hopefully this is just for some temporary purpose: the idea of storing these
dependent values is unnormalizing your table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which holds students answers to multiple choice examination
questions (A, B, C, etc). I need to select records according to the
subject,
make a table of these records and then format the table according to the
requirements of a third party system to which the data is to be
transferred.

I am doing it by using a macro to run a make table query and then a number
of update queries. It's all straight-forward except for one requirement.
The recipient of the data requires that the 3rd field of each record
should
contain a sequence number (001 for the first record, 002 for the second,
and
so on). The field is empty at the moment - can you suggest how I might
populate the field to meet this requirement. The job will then be
complete.

Thanks for any help - as ever!

Jim Jones
Gaborone
Botswana

 

[Chris2]

I have a table which holds students answers to multiple choice examination
questions (A, B, C, etc). I need to select records according to the subject,
make a table of these records and then format the table according to the
requirements of a third party system to which the data is to be transferred.

I am doing it by using a macro to run a make table query and then a number
of update queries. It's all straight-forward except for one requirement.
The recipient of the data requires that the 3rd field of each record should
contain a sequence number (001 for the first record, 002 for the second, and
so on). The field is empty at the moment - can you suggest how I might
populate the field to meet this requirement. The job will then be complete.

Thanks for any help - as ever!

Jim Jones
Gaborone
Botswana

Jim Jones,

The following assumes you have an ascending sequential integer
Primary Key.

The table and column names come from a solution to an earlier
similar problem, but it should be adaptable to your situation.


CREATE TABLE Titles_20051129_1
(TitleID AUTOINCREMENT
,TitleName TEXT(5)
,CONSTRAINT pk_Titles_20051129_1
PRIMARY KEY (TitleID)
)


Sample Data:

1, Jim
2, Jan
3, John
4, Al
5, Frank
6, Sam
7, Kate
8, Sally
9, Susie
10, Amy
11, Kevin
12, Phil


Query:

SELECT T1.TitleName
,(SELECT SWITCH(COUNT(T1.TitleID) MOD 3 = 1, 1
,COUNT(T1.TitleID) MOD 3 = 2, 2
,COUNT(T1.TitleID) MOD 3 = 0, 3)
FROM Titles_20051129_1 AS T01
WHERE T01.TitleID <= T1.TitleID)
AS NumberSeq
FROM Titles_20051129_1 AS T1


TitleName, NumberSeq

Jim, 1
Jan, 2
John, 3
Al, 1
Frank, 2
Sam, 3
Kate, 1
Sally, 2
Susie, 3
Amy, 1
Kevin, 2
Phil, 3



The idea is to adapt this to have your "3rd column" receive the
artificially generated repeating numbers instead of the NumberSeq
column.




Sincerely,



Chris O.

 

[Chris2]

Note: I was assuming that you wanted a repeating sequence number,
but I have have just realized that may not have been what you
wanted.

If all you want is an ascending sequence number:

SELECT T1.TitleName
,(SELECT COUNT(*)
FROM Titles_20051129_1 AS T01
WHERE T01.TitleID <= T1.TitleID)
AS NumberSeq
FROM Titles_20051129_1 AS T1


Sincerely,

Chris O.

 

[Guest]

Dear Chris2

Many thanks for the help. It's working!

Jim Jones