sort alphabetically then number 2

[kkson]

Hi, Need a little help.

I need a number field, can't be an autonumber. That will number consectively, alphabetically from A to Z. (andrews = 1, buns = 2, chan= 3 and so on). now if i delete buns(2) I need to requery the table and now andrews =1 and chan=2. If i have 20 names and then add adams, adams should now be 1. so i need to requery and renumber. can this be done. Autonumber would have adams as 21 and i can't make him 1.

Thanks for any help.

 

[Remou]

Can this number exist purely in a query? Or must it be a field in a table?

 

[kkson]

I would say a table, the field would replace an autonumbered field that is used for another purpose. Having it just in a qry would require combining the other 2 qrys that use the field. and they have to be separates.

 

[Remou]

All I can think of is that you renumber in the On Delete and On Insert events of a form.

 

[cmmrfrds]

You should be able to solve this problem with a query that creates the number and then join the other queries with the query that creates the number. You can generate the row numbers with a self join on the table provided there is a unique id on the table.

 

[kkson]

not sure what a self join is? If i have an autonumber field that would be a unique id right.

 

[cmmrfrds]

An example of dynamic row numbers using a self join.

Data in table.

ID cart_id Order
1 1 123456
2 1 123457
3 1 123458
4 2 123459
5 2 123460
6 2 123461
7 3 123462
8 3 156123
9 3 159987
11 3 159987
12 3 159987

SELECT Count(*) AS row, A.ID, min(A.cart_id) AS cart_id1
FROM TestOrder AS A, TestOrder AS B
WHERE (((B.ID)<=[A].[id]) and A.cart_id = B.cart_id)
GROUP BY A.ID
ORDER BY A.ID;


row ID cart_id1
1 1 1
2 2 1
3 3 1
1 4 2
2 5 2
3 6 2
1 7 3
2 8 3
3 9 3
4 11 3
5 12 3

This query created a row numbers for each value cart_id

 

[kkson]

I used this:
SELECT Min(A.name) AS name1, Count(*) AS row, A.id
FROM table1 AS A, table1 AS B
WHERE (((B.id)<=[A].[id]) AND ((A.name)=.[name]))
GROUP BY A.id
ORDER BY Min(A.name), A.id;

and got:

row id name1
1 1 ann
1 2 bett
1 3 sam
1 4 john
1 5 ted
1 7 paul

the row # is not incrimenting, and it didn't order alphabetically.

 

[cmmrfrds]

Can you show some sample data from your table. Is each name in more than 1 record?

 

[cmmrfrds]

If you want to have rows across entire table then leave off the 2nd part of the where clause. The example I showed before did the row numbers by cart_id not across the table.

SELECT Min(A.name) AS name1, Count(*) AS row, A.id
FROM table1 AS A, table1 AS B
WHERE ((B.id)<=[A].[id])
GROUP BY A.id
ORDER BY Min(A.name), A.id;

 

[kkson]

If I add a name, alex. his id # is now #8 I need the row# to be changed to #1 because it will be the first one alphabetically. Basically if I add or remove a name I need the row# to change to reflect there order (alphabetically) Alex= 1, ann=2 bett=3 john=4 paul=5 sam=6 and ted = 7. if i delete bett, then john=3 paul=4 sam=5 and ted=6. If i add ben then ben = 3 bett= 4 john=5 sam=7 ted = 8.

It needs to renumber based on alphabetical order.

Thanks for the help!

 

[cmmrfrds]

Are the names unique? If so then

SELECT A.name, Count(*) AS row
FROM table1 AS A, table1 AS B
WHERE A.name>=B.name
GROUP BY A.Name

 

[kkson]

ok now they all have a row # of 1. The names could appear more than once but they have a account letter with them. (dan, A) and (dan, B) are so they are unique in that reguard. now the row# needs to increment by 1 after the first one. 1, 2, 3, 4, Could that be done with a dmax and the add 1 and keep the alphabetical order?

Thanks

 

[cmmrfrds]

Something is getting lost in the communication. Please provide the SQL you used. Does the data match what you showed in you sample earlier.

 

[kkson]

heres the code.

SELECT A.name, B.account, Count(*) AS row
FROM table1 AS A, table1 AS B
WHERE (((A.name)>=.[name]))
GROUP BY A.name, B.account;

seems how the names could be listed more than once i had to add the account field. there can only be 1 instance of the name per account #. If I use you last bit of code it works if there name is listed once, but when i add another instace of the name the numbering get messed up. ann was 1 but if i add another ann it becomes a 4. so i added the account field but now the numbers stay at 1 and 2.

Also if i use the orignal code: (SELECT A.name, Count(*) AS row
FROM table1 AS A, table1 AS B
WHERE A.name>=B.name
GROUP BY A.Name)

then try to run an update qry to another table it will not let me do it due to the qry not being updateable.

THANKS

 

[cmmrfrds]

If you just add account to the where clause, what happens.

SELECT A.name, Count(*) AS row
FROM table1 AS A, table1 AS B
WHERE A.name=.[name] and a.account >= b.account
GROUP BY A.name;

 

[kkson]

the numbering is off now ann=3

here is the db:

http://users.adelphia.net/~akkeen/db1.zip

its just a sample.

thanks

 

[cmmrfrds]

Will this work for you. Create a query called
UniqueNames
SELECT name
FROM table1
GROUP BY name
ORDER BY name;

Create another query called
RowNumbers
SELECT min(a.name) AS name, count(*) AS row
FROM UniqueNames AS A, UniqueNames AS B
WHERE b.name<=a.name
GROUP BY a.name
ORDER BY min(a.name);

 

[PHV]

You wanted something like this ?
SELECT A.name, A.account, A.ID, Count(*) AS row
FROM table1 AS A, table1 AS B
WHERE A.name & A.account >= B.name & B.account
GROUP BY A.name, A.account, A.ID

or unique names only ?
SELECT A.name, Count(*) AS row
FROM (SELECT DISTINCT name FROM table1) AS A
, (SELECT DISTINCT name FROM table1) AS B
WHERE A.name >= B.name
GROUP BY A.name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kkson]

cmmrfrds and phv both of your codes work perfectly. However when I take the row # and try to update the table i get 'operation must use an updateable query' I am trying to take the new row# and put that in a table that will use it to base the printout order on. Here is the sql for the update query i made.

UPDATE Table1 INNER JOIN Query3 ON Table1.id = Query3.ID SET Table1.sort = [query3]![row];

query3 right now is the code that phv gave(top one) but using cmmrfrds I get the same error. the sort field in table 1 is number, double for settings.

Thanks,