Incrementing a Counter

[dave]

If I have a table with one column containing the following records:

FieldA
A
A
A
A
B
B
C
C
C

Can I with a simple piece of SQL, in a Query, create an incrementing
field which is reset
on change of FieldA? To produce;

FieldA Counter
A 1
A 2
A 3
A 4
B 1
B 2
C 1
C 2
C 3

I know I can do it with code, but can I with SQL?

Thanks

Dave

 

[Allen Browne]

No. There is no simple SQL to sequence records like in Access that when the
values are repeating and there is no unique key to sequence them.

 

[Guest]

Dave:

Leaving aside the question as to what use a table with only one column of
non-unique values can be, you can only do this by adding a primary key column
to the table, e.g. an autonumber (MyID in the examples below). Then you can
return sequential values for each distinct value of FieldA with:

SELECT FieldA,
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.FieldA = T1.FieldA
AND T2.MyID <= T1.MyID) As Counter
FROM YourTable AS T1
ORDER BY FieldA, MyID;

This query will not be updatable as the use of an SQL aggregating function,
even in a subquery, makes a query read only. To make it updatable you'd have
to use the VBA DCount function:

SELECT FieldA,
DCOUNT("*", "YourTable", "FieldA = """ & [FieldA] & """
AND MyID <=" & [MyID]) As Counter
FROM YourTable
ORDER BY FieldA, MyID;

Ken Sheridan
Stafford, England

 

[dave]

Thanks Ken, that should do it for me.

My table, of course, has more than just a single column, but it does
not a have any form of unique id - ir's the data I've been provided by
an external source.

It seems to me we need two enhancements to the Count function, the
ability to specify:
1. When to increment - at detail level or at particular level break
2. When to reset - at a particular level break (or not at all!)

Dave Cope
Preston, UK

 

[Guest]

Dave:

The Count function does exactly what it says on the tin; it counts rows.
What rows it counts is something you control with the SQL statement. Your
data may well have a unique identifier in the form of a combination of
columns, unless there are rows which are absolutely identical in terms of the
values in all columns, but regardless of that all you need to do is append
the data to an empty table whose columns match those of the imported table
and which in addition has an autonumber column. The query I sent you will
then work; you simply have to list all the columns you want returned in the
outer query's SELECT clause rather than just FieldA.

It’s a simple task to automate this by creating a Delete query to empty the
table with the autonumber column and an Append query to append the rows from
the imported table. You can then run these in succession with some code or a
macro whenever new data is imported.

Ken Sheridan
Stafford, England

 

[Jamie Collins]

If I have a table with one column containing the following records:

FieldA
A
A
A
A
B
B
C
C
C

Can I with a simple piece of SQL, in a Query, create an incrementing
field which is reset
on change of FieldA? To produce;

FieldA Counter
A 1
A 2
A 3
A 4
B 1
B 2
C 1
C 2
C 3

I know I can do it with code, but can I with SQL?

If the challenge is to do this with Jet SQL code only (i.e. no VBA
code), given this table:

CREATE TABLE MyTable (
FieldA CHAR(1) NOT NULL,
CHECK (FieldA LIKE '[A-Z]'),
FieldB INTEGER NOT NULL,
CHECK (FieldB > 0),
UNIQUE (FieldA, FieldB)
);

then this proc should maintain the 'counter':

CREATE PROCEDURE MyProc
FieldA CHAR(1) = 'A')
AS
INSERT INTO MyTable
(FieldA, FieldB)
SELECT :FieldA, IIF(MAX(T1.FieldB) IS NULL, 0, MAX(T1.FieldB)) + 1 AS
FieldB
FROM MyTable AS T1
WHERE T1.FieldA = :FieldA;

Jamie.

--

 

[dave]

Ken,
Thanks, as I noted earlier, your SQL does the trick.

I think I'm just getting hung up over the fact that using the standard
count functionality requires the SQL processor to make multiple reads
of the record - once for the record itself and again for the counting
operation.

I have previously used a Cognos language called Quiz (mini-computer
based report writer preceding their BI tools) which included a method
for specifiying when the counter should be incremented and when the
counter should be reset. In fact the methods applied to all aggregate
functions, not just COUNT.

Once again, thanks for your help.

Dave Cope
Preston, UK

 

[dave]

Ken,
Thanks, as I noted earlier, your SQL does the trick.

I think I'm just getting hung up over the fact that using the standard
count functionality requires the SQL processor to make multiple reads
of the record - once for the record itself and again for the counting
operation.

I have previously used a Cognos language called Quiz (mini-computer
based report writer preceding their BI tools) which included a method
for specifiying when the counter should be incremented and when the
counter should be reset. In fact the methods applied to all aggregate
functions, not just COUNT.

Once again, thanks for your help.

Dave Cope
Preston, UK