?
Solved

A count row like a autoincrement column

Posted on 2006-11-27
7
Medium Priority
?
308 Views
Last Modified: 2010-05-18
Hello guys,

I need to create in my query a column a sequence, as I have 7000 rows and I filter by inicial letter, like
a , so I want all register that starts with A, I need to create a sequence column that starts from 1.

like this:

For A

1
2
3
4

For B
1
2
3
4
5


How?
0
Comment
Question by:hidrau
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 18018226
Hi hidrau,

You can use the function below to add a sequence number for each group.
You need to pass the Initial letter as the argument.

Myseq:GetNextrNo(left(myfield),1)

You must ensure that your data is correctly sorted in your query.

Pete

Function getnextno(groupval)

Static mygroup
Static LastNo
If Nz(mygroup, "") <> groupval Then
LastNo = 0
mygroup = groupval
End If
LastNo = LastNo + 1
getnextno = LastNo

End Function
0
 
LVL 1

Author Comment

by:hidrau
ID: 18018261
Ins't there any way to do this directly in SQL, because I will assemble this query in Delphi, and custom-made function doesn't word out of access :((

I don't know but only access functions works, when I tried to create some and called it from my delphi application, my application didn't work.

You see!

0
 
LVL 77

Expert Comment

by:peter57r
ID: 18018299
To do it in sql, you must have a field which has a  unique value within each group (letter) and the data must be sorted on this field within the group.
Do you have such a field?

Pete
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 1

Author Comment

by:hidrau
ID: 18018305
ok, I can create a field with value 0, it is possible so, to create the sequence?


0
 
LVL 77

Expert Comment

by:peter57r
ID: 18018350
I think we have a language pronblem.
I'll try to re-do my response.

In your table, do you have a field which  contains a unique value for each record? (a Primary key, for example).
If so, you need to sort your query on these fields:
1.Initial Letter
2.Unique Key

Are you able to do this?

Pete
0
 
LVL 1

Author Comment

by:hidrau
ID: 18018404
Ok, Peter,

I have a primary key, but let me explain better.

When I filter my query this way:

SELECT * FROM TabWord
WHERE LEFT(WordField,1)='b'
ORDER BY WordField

all the rows that start with 'B' letter goes from 3950 until 8670

when I tell you this number, it is because it is a primary key.

I need to create this query order by WordField, but I need to create a sequence that
goes from 1 to ... the end of my rows filtered.



0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 18018495
You must use your own primary key field name in the sql below:

SELECT Left([wordfield],1) AS Init, C1*,  (select count(*) from Tabword where left(wordfield,1) = left(c1.wordfield,1) and primarykeyfield<=c1.primarykeyfield) AS Rank FROM Tabword AS C1
ORDER BY Left([companyname],1), C1.primarykeyfield;


Pete
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

752 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