Random Sequence and Row (record) Number Selected

[eerich]

Hello,

I've written a query to use the Random sequence for 25 rows out of a table, but I would like to know if there is a way to retrieve the actual record or row number as part of the results. I don't have an autonum field in the table. There are about 1.5 million record rows in the dataset.

The query I've written is:

SELECT TOP 25 rnd(docnum) AS RandomSeq, DOCNum, LineItem
From Corporate
ORDER BY Rnd(docnum);

Any help is appreciated.

 

[dhookom]

There are no record or row numbers in Access. Records in tables can be identified by their primary key field(s).

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[eerich]

Duane,

Just so that I'm clear on what you're saying: I won't be able to get a numeric value of the row number where the results are stored? For example.

If I have a random selection returning 3 records out of 10 and the information that is stored in the table corresponds to records 5, 8 and 9, I cant have the query return the results stating that the random results are from records 5, 8 and 9?

There is no primary key in this dataset and I'm just looking for a simple identifier as to which record number in the table contains the data results.

Thanks for your assistance.

 

[dhookom]

You don't have "records 5, 8 and 9" in Access tables. Records in a table are like marbles in a bag. There is no order. If you need to identify unique records, you need a unique value stored in the table.

Maybe we could identify a solution if you provided your requirements.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[eerich]

Duane - Thanks.

I believe the solution is for me to just add an autonum field in future tables so that when I run the query it pulls the autonumber. I tested it on a new import and it seemed to work as I expected.

Thanks.