Add a unique identifier/serial_number onto a query


table1 has 1 field called [CustomerName].

I want a simple query that gives an output (2 fields) like the following
1 jones
2 Smith
3 O'Brien
4 Obama

QUESTION: How do I add a simple sequential number onto a query (assuming that it does not exist in the source table)
Patrick O'DeaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kanti PrasadCommented:


SELECT t1.CustomerName, (
    SELECT Count(*)
    FROM table1 rno
    WHERE t1.CustomerName <= rno.CustomerName) as Rowno
FROM table1  t1
Kanti PrasadCommented:
Sorry  the columns should be sapped so use the below

(select count(rno.Customername)
from table1 rno
where rno.Customername < = t1.Customername) as Rowno,
from table1 t1
order by t1.Customername

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kant's answer above should work.  Just needs a slight correction.  Remove the space between the less than sign and the equal sign.  So it should be:
where rno.Customername <= t1.Customername

No points please.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Why do you need the unique number?  There is no efficient way to generate one with Access SQL.  Notice that the proposed solution requires running a separate query for each row of the main query AND it REQUIRES that the main query have some unique identifier itself.  So, if the main query returns 1000 rows, the query engine has to run 1000 other queries to generate the unique number.  Also, since the ID isn't saved anywhere, it can't be used for anything.

If the output is destined for a report, then generating a sequence number is trivial and efficient since the report process is sequential.  Just use an unbound control with a ControlSource of:
And then set the runningSum property on the Data Tab to Over All.
Patrick O'DeaAuthor Commented:
Response to Pat.

"Why did I need a unique number?"  (On subsequent reflection, I did a workaround by picking up a related key from a table).

Indeed, Access seems to find this  task more complex than perhaps it should be - very cumbersome and not at all pretty!

Your comment is appreciated.
SQL is set based.  If you can define a set, you can do the SAME thing to every member.  Although, you have a set, the process of generating a sequence number is dependent on a row's physical position in the set which makes the assignment a sequential operation rather than a set operation.  That is why it is difficult.  In a set, no record is related to any other record.  They are all equals and order is irrelevant.  Assigning a sequence number in a report is trivial because the report (not the query engine) is doing the assignment.  The report engine must process every row in the set to display it and so it is quite easy to simply count rows and include the generated number.  I think a function to generate sequence numbers may have been added in recent years to SQL Server but it hasn't made it into ACE.  So, if your BE is SQL Server, you could look into that and use a pass-through query.
Patrick O'DeaAuthor Commented:
Thank you all,

(I'm still studying Pat's comments ... very educational)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.