Need query field assigning line number for each found record

R

Ray S.

My query collects accounting data. Each query must be assigned a batch number
which the user manually enters according to stated criteria. I need to add a
field to the query that will identify each record by a line number:
1,2,3,4,5, etc. Can someone help me with either SQL or how I can do this in
the query builder? I greatly appreciate your help. Just reading your
discussions is an excellent education!
 
M

Michel Walsh

Hi,

You can rank your records (first, second, 3, 4, ... ) You need one (or
some) field that makes the ranking unique, without ex-equo.


SELECT a.f1, a.f2, a.f3, COUNT(*) As rank
FROM myTable As a INNER JOIN myTable As b
ON a.f1>b.f1 OR (a.f1=b.f1 AND a.f2 >= b.f2 )
GROUP BY a.f1, a.f2, a.f3


as example, assuming 3 fields, but (f1, f2) are enough to specify ordering.
If you already have a primary key:

SELECT a.f1, a.f2, a.f3, COUNT(*) As rank
FROM myTable As a INNER JOIN myTable As b
ON a.pk >= b.pk
GROUP BY a.f1, a.f2, a.f3


does the trick.



Hoping it may help,
Vanderghast, Access MVP
 
R

Ray S.

Thanks Michael. As it turns out no one of the queried accounting fields is
totally unique. Would you suggest I call up a totally unique auto-numbered ID
field form an underlying table?
 
M

Michel Walsh

Hi,


Yes, if the join does not duplicate it, in the result.


Hoping it may help,
Vanderghast, Access MVP
 
R

Ray S.

You're a great resource; but I've run into another snag. The table underlying
my query does not have a single primary key, rather a three field composite
key.
 
M

Michel Walsh

Hi,


Then you are due for a long statement like

f1> g1 OR ( f1=g1 AND ( f2>g2 OR ( f2=g2 AND f3>= g3 ) ) )


It is like asking if 4' 5" 15/16 is >= to x' y" z/16

If 4 > x, it is. Else, if 4=x, then you have to look at the inches, and
again, for the inches, and eventually again for the sixteenth.

Your ON clause will just be more complex, but basically, that just a matter
to write the logical expression allowing to answer "is it >= than", when
more than one "number" (entity) is involved, as in a measure supplied in
feet-inches-sixteenth, or hour-minute-second. Something I personally avoid,
and why I prefer a surrogate key to a compound key, but we can deal with
compound key anyhow... just a matter of a more "complex" statements to be
generated.



Hoping it may help,
Vanderghast, Access MVP
 
R

Ray S.

Conceptually, your solution works like a charm; but I have discovered that
even using all the composite key fields I still don't get a clean ranking
without "ties". I'm exploring some other way of doing this. I know I can
append the results of my query into an empty table with an autonumber field.
That gives me the required sequential record numbering for each record; but
then I have the problem of re-setting the autonumber back to one. That
involves using the Compact utility; but I found that I cannot invoke it from
a macro. So now, I need code that will re-set the autonumber from a function.
I found one referenced by Microsoft, but I can't get it to work. I followed
their instructions exactly, but I get an "external name not defined" compile
error, and when I try to invoke the function form a macro I also get that
Access can't find the name of the table. I've tried preceding it with Tables!
but I still get the error.

Here's the Microsoft autonumber re-set method:

"You can use the following function in a Microsoft Access database (.mdb) to
programmatically reset the seed value of your AutoNumber field. You can add
the function to a module, and then run it in the Debug window. Or, you can
call the function from a command button or from a macro.

Note For this code to run correctly, you must reference both the Microsoft
ActiveX Data Objects 2.x and the Microsoft ADO Ext 2.x for DDL and Security
Libraries (where 2.x is 2.1 or later.) To do so, click References on the
Tools menu in the Visual Basic Editor. Make sure that the Microsoft ActiveX
Data Objects 2.x and the Microsoft ADO Ext 2.x for DDL and Security Libraries
check boxes are selected."

***code***

Function ChangeSeed(strTbl As String, strCol As String, lngSeed As Long) As
Boolean
'You must pass the following variables to this function.
'strTbl = Table containing autonumber field
'strCol = Name of the autonumber field
'lngSeed = Long integer value you want to use for next AutoNumber.

Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column

'Set connection and catalog to current database.
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn

Set col = cat.Tables(strTbl).Columns(strCol)

col.Properties("Seed") = lngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties("seed") = lngSeed Then
ChangeSeed = True
Else
ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing

End Function

***end code***

Any ideas?
 
R

Ray S.

Sorry to bother you with such a long reply... Anyway, I was able to get both
solutions to work. I really appreciate your help.
 
Top