Numbering

[dastr]

Hi all,

I have a table consisting of one column "Names" where I have the names of the participants in a competition; however amoung these names I also have entries like P1, P2, etc which stands for a participant whose name is not known, therefore a generic name was assigned - P1 -stands for the first participant who did not leave his/her name. Ok so I have the below:

Names
Anna
Maria
Luca
Luca
P1
P2

Does anybody know how I get numbers assigned to these names/abbreviations so that I get:

Names
1
2
3 (Luca apperas twice, so twice number 3)
3
4
5

Thank you in advance.

 

[Guus2005]

You need to split this one up into several pieces.

First: you need to get distinct Names
Second: you need to number these names
Third: Produce the end result.

First:

Select distinct Names from Table1 
or 
Select Names from Table1 group by Names.

We call this query Query1

Second:
Record Numbering. Here are two sources to get you started on that:
http://www.lebans.com/rownumber.htm
http://www.rogersaccesslibrary.com/forum/topic309.html

If you use lebans solution create a query like this

SELECT Serialize("Query1","Names",[Names]) AS NumberName, Names
FROM Query1

Call this query Query2.

Third: The final query uses Table1 and Query2

Select Query2.Numbername, Table1.Names From Table1 inner join Query2 on Table1.Names = Query2.names

Call this query Query3.

Just run the last query, Query3 to get the result.
To get better results you might want to store the result of Query2 into a table. Storing the result. Sequential executions of Query2 might result in unwanted values.

Just run it a few times and you'll see what i mean.

Please provide a sample database if this solution doesn't work for you.

Share & Enjoy!

 

[jdraw]

You might want to do some reading on Database Design concepts - Normalization.
A normalized table structure may resolve the issue.
http://lmgtfy.com/?q=database+normalization

 

[dastr]

Hi Guus,

I am trying to follow yr example, but cannot figure our Query2, here I am sending you the database.

Let me know what you think.

Greatly appreciated.

You need to split this one up into several pieces.

First: you need to get distinct Names
Second: you need to number these names
Third: Produce the end result.

First:

Select distinct Names from Table1 
or 
Select Names from Table1 group by Names.

We call this query Query1

Second:
Record Numbering. Here are two sources to get you started on that:
http://www.lebans.com/rownumber.htm
http://www.rogersaccesslibrary.com/forum/topic309.html

If you use lebans solution create a query like this

SELECT Serialize("Query1","Names",[Names]) AS NumberName, Names
FROM Query1

Call this query Query2.

Third: The final query uses Table1 and Query2

Select Query2.Numbername, Table1.Names From Table1 inner join Query2 on Table1.Names = Query2.names

Call this query Query3.

Just run the last query, Query3 to get the result.
To get better results you might want to store the result of Query2 into a table. Storing the result. Sequential executions of Query2 might result in unwanted values.

Just run it a few times and you'll see what i mean.

Please provide a sample database if this solution doesn't work for you.

Share & Enjoy!

 

[Guus2005]

The first query makes sure that the selected names are unique.
Anna
Maria
Luca
P1
P2

The second query adds a field with a sequence number to the record.
1 Anna
2 Maria
3 Luca
4 P1
5 P2

If we combine the second query with table1 and leave out the names we get
1
2
3
3
4
5

Which was what you wanted. I believe.

HTH

 

[RainLover]

You might want to do some reading on Database Design concepts - Normalization.
A normalized table structure may resolve the issue.
http://lmgtfy.com/?q=database+normalization

What jdraw wrote is correct.

If you Normalised your structure you would not have a problem.

I suggest you follow this recommendation because once learnt it will help prevent other problems that you may have or will have in the future.

 

[Guus2005]

I can think of an application where this issue might pose a problem.

Normalising is not always the answer to the question to which we get only so little information.
In this case i assume the TS has its database neatly normalised to the Boyce-Codd NF.

I don't know *why* the TS wants this problem solved. It usually is a train of thought that the TS follows and comes to the conclusion that *this* is the problem to be solved.

Share & Enjoy!

 

[dastr]

Hi Guus,

When i attempt to run the second query I get the following msg:

Undefined function "Serialize" in expression.

Do you know how to fix this?

Thx

The first query makes sure that the selected names are unique.
Anna
Maria
Luca
P1
P2

The second query adds a field with a sequence number to the record.
1 Anna
2 Maria
3 Luca
4 P1
5 P2

If we combine the second query with table1 and leave out the names we get
1
2
3
3
4
5

Which was what you wanted. I believe.

HTH

 

[Guus2005]

Obviously you didn't check out the links in my previous post.

Serialize is described here.
http://www.lebans.com/rownumber.htm