Solved

Enumerate Results in SQL Query?

Posted on 2008-10-14
4
637 Views
Last Modified: 2012-05-05
Is there any clean way to enumerate the results of an SQL query in a listbox?  The main reason for asking this is that I need a way to count the row/results so if I sort them, the row number is consistent.  Thanks in advance^
strlist2 = "SELECT (SELECT Count(ID) FROM Assignments WHERE [Associate_name] = '" & assoc_ind & "' AND " & _
           "Month([Assignment_Date]) = Month(Now())) as Count_rec, Assigned_to, [Number], [Type], [Assigned_by], [Assignment_Date] FROM Assignments " & _
           "WHERE [Associate_name] = '" & assoc_ind & "' AND Month([Assignment_Date]) = Month(Now()) ORDER BY 6;"

Open in new window

0
Comment
Question by:jad0083
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22715207
you can set the row source of the listbox using strlist2

me.listbox.rowsourcetype="table/query"
me.listbox.rowsource=strlist2
0
 

Author Comment

by:jad0083
ID: 22715307
Uhm, I already have that variable as the rowsource of the listbox.  Problem is that on one of the columns of the listbox, I would want to have an enumeration:

Something like this:

Enumeration | Count |  Assigned_to | Number | Type | Assigned_by | Assignment_Date
       1                3            joe                     5       P             sam                    10/06/08
       2                2             jack                  4         A            bill                     10/04/08
       3                 7            Dude                3          C          Frank                   10/08/08
       4                 1             Paul                 8         B            Steve                     10/10/08                    


Thanks!
0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 23268825
Question PAQ'd, 500 points refunded, and stored in the solution database.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 7 35
Archiving Access table older than 6 months 9 39
Query Syntax 17 36
Microsoft Access 2013 / SQL Server 2012 back end  subform with #Name? 6 36
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

803 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