Sequential Increase Column in a Query 1

[GGleason]

I have data that I would like to add a column to indicate a sequential increase. For example, a query has the following result

Name Task
ABC apple
DEF apple
GHI banana
JKL orange
MNO orange

I would like to have a third column that shows a sequential increase with each change in the Task column. For example, the above query results would look like this:

Name Task Seq
ABC apple 1
DEF apple 1
GHI banana 2
JKL orange 3
MNO orange 3

I don’t know if this can be accomplished in SQL or by a module, but I am open to either.

TIA,
GGleason

 

[braindead2]

in the FAQ in this forum there a 2 functions that show how to increment. I would think you could customize either of these by simply putting a condition to test to see it the value in task has changed

good luck

 

[GGleason]

braindead,

Thanks for the tip, but these functions do not behave like I want them to.

Thanks,
GGleason

 

[GGleason]

I made my own function that solves the problem. It's not as elegant as I had hoped, but it works.

GGleason

 

[braindead2]

Please post here for others to see! May earn you a star some day!

 

[GGleason]

Here is sample syntax where the module is used in a query:

SELECT tblTest.*, ChgRec("tblTest","SELECT tblTest.* FROM tblTest ORDER BY Comb","Comb",[Comb],true) AS iRec
FROM tblTest
ORDER BY tblTest.Comb;


Here is the code:

Option Compare Database
Option Explicit

Public Function ChgRec(strTableName As String, strSQL As String, strMyField As String, _
MyMatch As Variant, blnYes As Boolean) As Single

' Last modified on 12/05/01 4:31PM GCG

' Arg. 1 = Table name reference
' Arg. 2 = SQL statement ; if this is present, it is used instead of Arg. 1, otherwise ""
' Arg. 3 = The field name you want to reference
' Arg. 4 = The field value you want to reference
' Arg. 5 = If false, ChgRec returns ascending interger of change
' If true, ChrRec returns 0 when the the Arg. 5 "true" value is even & 1 when it is odd

' If this is returning only zeroes, there is a bug somewhere!

Dim dbs As Database, dbsName As String
Dim qdfTableName As QueryDef, rstTableName As Recordset
Dim sngLastRec As Single, i As Single, j As Single, T As Integer
Dim varOldRec As Variant

ChgRec = 0

On Error GoTo ChgRecErr

dbsName = CurrentDb.Name
Set dbs = OpenDatabase(dbsName)

strTableName = IIf(InStr(strTableName, "[&quot = 0 And InStr(strTableName, " &quot > 0, "[" + strTableName + "]", strTableName)

strSQL = IIf(Len(Trim(strSQL)) = 0, "SELECT * FROM " + strTableName + ";", strSQL)
Set qdfTableName = dbs.CreateQueryDef("", strSQL)
Set rstTableName = qdfTableName.OpenRecordset(dbOpenDynaset)
If rstTableName.BOF = True Then
ChgRec = 0
sngLastRec = 0
rstTableName.Close
qdfTableName.Close
Exit Function
End If

rstTableName.MoveLast
sngLastRec = rstTableName.RecordCount

rstTableName.MoveFirst

For i = 1 To sngLastRec
For T% = 0 To rstTableName.Fields.Count - 1
If rstTableName.Fields(T%).Name = strMyField Then
If i = 1 Then
j = 1
Else
If varOldRec <> rstTableName.Fields(T%).Value Then
j = j + 1
End If
End If
If MyMatch = rstTableName.Fields(T%).Value Then
ChgRec = IIf(blnYes = False, j, IIf(IsEven(j) = True, 0, 1))
rstTableName.Close
qdfTableName.Close
Exit Function
End If
varOldRec = rstTableName.Fields(T%).Value
Exit For
End If
Next T%
rstTableName.MoveNext
Next i

rstTableName.Close
qdfTableName.Close
dbs.Close

ChgRecErr:
End Function

Public Function IsEven(varValue As Variant) As Boolean
' Last modified on 11/26/01 2:14PM GCG
IsEven = IIf(varValue Mod 2 = 0, True, False)
End Function



GGleason
Soli Deo Gloria

 

[tlbroadbent]

Try this query. NOTE: It could be slow if the table is large. Indexing the Task column will speed the query.

SELECT
t.Name, t.Task
(SELECT Count(*)
FROM (SELECT Distinct Task
FROM [tblTest]) AS q
Where Task<=t.Task) AS iRec
FROM tblTest AS t
ORDER BY t.Task, t.Name; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[GGleason]

Terry,

I am very much impressed! It works very nicely without the coding overhead of my solution.

I had to make some minor code changes since I am running MSA 97 SR-2. Here is my revised code:

SELECT t.Name, t.Task,
(SELECT COUNT(*)
FROM
[SELECT DISTINCT Task
FROM tblTestX]. AS q WHERE Task<=t.Task) AS iRec
FROM tblTestX AS t
ORDER BY t.Task, t.Name;

Thanks,
GGleason