Sequential number field in query

[tim419762]

I'm interested in having a sequentially numbered field show up in a query. The reason is that the results of the query will be directly exported to Excel, and as the file is sorted and filtered, it would be helpful to have a field that tracks what number the record was. As I'll be distributing the file, this field will be the 'identifier' for the line (or record). I'm not interested in using a autonumber field, as I want the records to always start at '1' in each query that is run.

I've searched for this answer but come up dry. I'm aware of how to do this in a recordset (that would be easy) and even in a temp table, but what I'm really after is the ability to just have something that I can put into a query without coding.

Thanks,

Tim

 

[John Big Booty]

One way to achieve what you are after is to in fact use an Auto Number.

The first step would be to find the minimum of that number in a query. Then use that result in your final query to create a calculated field in your query that is AutoNumber-(MinimumOfAutoNumber-1) your records will then be sequentially numbered from one up.

 

[John Big Booty]

Caveat to the above; So long as records have not been deleted, otherwise there will be holes in the sequence.

 

[tim419762]

Mr. Booty,

Thanks for your quick reply. In addition to the issue with 'missing records', there are a few other items that push me away from using an autonumber solution. Namely:
If the query returns a filtered list (which it will most of the time), the first record (i.e. number 1) may not be in the recordset
Also, if the query contains sorting options, there's little chance the records will be in the order of the autonumber field.

Thus, I think I'm still in the conundrum. Any other ideas? Would this better be posted in the VBA discussion?

 

[ACMAIN_CHM]

hi tim419762 ,

as there is no ROW_NUM like other database system e.g. Oracle, so you can not directly get the ROW_NUM in ACCESS, but you can count how may records which before your current records if you have the order by fields.

select count(*) as rownum, a.id,a.fd01,a.fd02
from yourTable a inner join yourTable b on a.id>=b.id
group by a.id,a.fd01,a.fd02

above is a standard SQL, in ACCESS you can also use the DCOUNT to get the 'ROW_NUM'

best regards
ACMAIN

 

[ajetrumpet]

You can also get line numbers of records this way tim (you have to have a field in the query though, that has unique values to identify every record):

Function RecordLine(ID AS "whatever variable type your Unique field is")

Dim db As Database, rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("QUERY NAME", dbOpenDynaset)

With rs

  .FindFirst ("Unique Field Name = " & ID)
  'FindFirst ("Unique Field Name = '" & ID & "'") [color=red]<--- use this line if "ID" is a string variable only[/color]
    RecordLine = .AbsolutePosition + 1

End With

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

This works with any recordset, query or table, as long as you can uniquely identify the record somehow.

 

[John Big Booty]

If none of the above suggestions meet your needs. Another method would be to append your selected records into a temporary table, to pick up a sequential Auto number. You could then use the method I suggested earlier, then just delete the records from your temporary table once they have been exported.

 

[Mike375]

I find the best way to do this type of thing is to loop down the records.

The attached has a form, table and a couple of macros. Macro1 determines how many records to be done (and can be done dynamically) and Macro 2 how you want the increment done. The form has Macro1 on the label.

I also use a similar system for progressive totals, right Adam

 

[ajetrumpet]

Shut up Mike. My way works best...

 

[Mike375]

Adam,

You will pleased to know that the last one you made to handle sort issues/progressive I now use to test laptop batteries under processing conditions as with just a few records it good for a 2 hour processing run

 

[ajetrumpet]

good to know mIke. Good to know!