Add Numbers To A Listbox

[bwion]

Hey All,

I have a listbox whose rowsource is base on a select query and is ordered by
some of the variables in that query. I want another "column" in my list box
that has a number from 1 to number of items in listbox. Here is an example

_Number___Customer___Item___Quant___
| 1 Jane Doe Dog 2 |
| 2 Jane Smith Cat 1 |
| 3 John Smith Fish 12 |
| |

As you can see the data is ordered by the Customer. So I want to assign
incremental numbers based on my query. So, for example, if I add the name
"Jane Gore" and run a requery, I want it to redo the numbers so that they are
in order of the Customer (i.e. Jane Doe is #1, Jane Gore is #2, Jane Smith is
#3, and John Smith is #4). I have tried a couple things with count but
nothing is working quite right. Any thoughts or suggestions? Thanks in
advanced.

Ben Wion

 

[John Spencer]

You could try the following query and see if it works

SELECT Count(A.Customer) as [Number],
A.Customer, A.Item, A.Quant
FROM YourTable as A INNER JOIN YourTable as B
ON A.Customer >= B.Customer
GROUP BY A.Customer, A.Item, A.Quant



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[bwion]

Hey John,

It didn't work. The numbers were not in order (i.e. Jane Doe was #2, Jane
Smith was #4, and John Smith was #1). Any other thoughts?

Thanks,
Ben

You could try the following query and see if it works

SELECT Count(A.Customer) as [Number],
A.Customer, A.Item, A.Quant
FROM YourTable as A INNER JOIN YourTable as B
ON A.Customer >= B.Customer
GROUP BY A.Customer, A.Item, A.Quant



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hey All,

I have a listbox whose rowsource is base on a select query and is ordered by
some of the variables in that query. I want another "column" in my list box
that has a number from 1 to number of items in listbox. Here is an example

_Number___Customer___Item___Quant___
| 1 Jane Doe Dog 2 |
| 2 Jane Smith Cat 1 |
| 3 John Smith Fish 12 |
| |

As you can see the data is ordered by the Customer. So I want to assign
incremental numbers based on my query. So, for example, if I add the name
"Jane Gore" and run a requery, I want it to redo the numbers so that they are
in order of the Customer (i.e. Jane Doe is #1, Jane Gore is #2, Jane Smith is
#3, and John Smith is #4). I have tried a couple things with count but
nothing is working quite right. Any thoughts or suggestions? Thanks in
advanced.

Ben Wion

 

[John W. Vinson]

Hey John,

It didn't work. The numbers were not in order (i.e. Jane Doe was #2, Jane
Smith was #4, and John Smith was #1). Any other thoughts?

So sort it in the query:

SELECT Count(A.Customer) as [Number],
A.Customer, A.Item, A.Quant
FROM YourTable as A INNER JOIN YourTable as B
ON A.Customer >= B.Customer
GROUP BY A.Customer, A.Item, A.Quant
ORDER BY Count(A.Customer);