[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I get the row number to appear in a query?

Posted on 2012-08-22
11
Medium Priority
?
521 Views
Last Modified: 2012-08-22
I have the following query (see attached) which returns the id, first name and last name. Currently the output does not display the row number. I would like it to do so on the left hand side. How can I get that to appear as well?
Query-Design.jpg
Query-Output.jpg
0
Comment
Question by:geeta_m9
11 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 200 total points
ID: 38321432
Define 'the row number'.  
If you mean the position of the record in the table (first, second, third), then it won't display it, and if it matters to you then you'll want to put it into a column that can be queried.

SQL Server has the ROW_NUMBER() function that creates a 1, 2, 3, ... number based on how you choose to order it, but afaik Access does not have an equivalent.
0
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 1800 total points
ID: 38321449
data tables do not have row numbers, like in Excel; You should consider a database table to be more akin to a bag of marbles, where each marble contains all of the data for a particular record.

Having said that, you can generate a "row number" based on some sort order.  This is easiest in a report, where you don't even have to modify the query.  You simply create an unbound textbox on the report, give it a ControlSource of: =1, and then set the RunningSum property to "Over All" or "Over Group".

In a query, it is a little more difficult.  If you absolutely need the row number in the query output, you will need to define the field (or fields) that would define a unique sort order for the result set.
0
 

Author Comment

by:geeta_m9
ID: 38321479
Ok, what you say makes sense. I inserted the query in a form, and it now shows me the total number of records which is fine.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:geeta_m9
ID: 38321508
However, I would also like the query to give me the number of records in the qAdvisorApplicationReadOnly table (the table on the left hand side of the query) as I want to display this value in the form as well. Is there anyway I can get it to do that as well, or would I need to use a different query?

 Can I have a form that uses two different queries as the data source?
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38321551
if all you need is the number of records in that table, on your form, you could add an unbound textbox and set it's control source to something like:

=DCOUNT("*", "qAdvisorApplicationReadOnly")

You may have to replace "*" with the name of one of the fields in the table.
0
 

Author Comment

by:geeta_m9
ID: 38321608
I tried what you suggested, but it is not giving me the correct total. I created another query to count the total number of records in the table called qCountofApplications (see attached). How can I use it to display the total number of records (in this case 80) in the textbox?
Query-Count.jpg
Query-PID-Result.jpg
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38321648
If you look near the bottom of that query (output) window, you will see the current row number as well as the total number of rows.
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 1800 total points
ID: 38321936
Try putting the following in the ControlSource of the textbox.

=DLOOKUP("CountOfPID", "qCountOfApplications")
0
 
LVL 40

Expert Comment

by:als315
ID: 38321946
If you query have only one resulting line, you can add subform with this query as record source or you can use:
=dlookup("[CountofPID]","qCountofApplications")
You should requery this subform or field when data are changed
0
 

Author Comment

by:geeta_m9
ID: 38322078
fyed, your suggestion works! Thanks!
0
 

Author Closing Comment

by:geeta_m9
ID: 38322106
Thank you to everyone for your help!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question