Numbering

[JAD814]

Hello,

Thanks in advance for any and all help. Basically (to keep it as short as possible) I have a table with 2 columns that comprise the key (we'll call them idx1 and idx2).

What I want to do is (starting at 1, then 2, etc) assign yet a 3rd idx automatically. So Say I have...

idx1 idx2
-------------
123 555
123 777
123 999
456 111
567 222
567 333

I'd like to see...


idx1 idx2 newColumn
---------------------------
123 555 01
123 777 02
123 999 03
456 111 01
567 222 02
567 333 02

Thanks again for any help with this!!!

 

[JAD814]

Forgive my mistake...

I'd like to see...


idx1 idx2 newColumn
---------------------------
123 555 01
123 777 02
123 999 03
456 111 01
567 222 01
567 333 02

THANKS AGAIN!!!

 

[dhookom]

You can try something like:

SELECT Idx1, IDx2, (SELECT Count(*) FROM tblNoName N WHERE N.idx1 = tblNoName.idx1 AND n.idx2<=tblNoName.Idx2) as NewColumn
FROM tblNoName;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JAD814]

OK, how about I back up a step and try this (as that solution doesn't quite do it).

I have a table that has numerous records with an idx column that isn't unique YET (the goal of the SQL I'm looking for is to MAKE the combination of the 2 columns unique), so here's what I have (in theory)

index1 index2
-----------------
123
456
456
789
789
789

Here's what I'd like to see

index1 index2
-----------------
123 01
456 01
456 02
789 01
789 02
789 03

I'm starting to question if there really IS a way to do this, but it'd be GREAT if someone knew a way, NOTHING I'm trying is working!

Thank you, dhookom, for your reply, and thanks in advance to everyone for trying!

 

[lespaul]

Are the other fields in each record identical, like this:
[tt]
index1 field1 field2 Field3 field4
-----------------
123
456 xxx xxx xxx xxx
456 xxx xxx xxx xxx
789 yyy yyy yyy yyy
789 yyy yyy yyy yyy
789 yyy yyy yyy yyy
[/tt]

or different, like this:
[tt]
index1 field1 field2 Field3 field4
-----------------
123
456 xxx 123 bbb xxx
456 xxx 345 aaa ccc
789 yyy 124 yyy yyy
789 bbb 758 ttt www
789 yyy 967 aaa yyy
[/tt]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[dhookom]

You can't number/rank/sequence your records in a query without having a field value that identifies the order.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[adalger]

It might help to know why you need this, as opposed to using, e.g., autonumber so every record has a unique identifier. I.e., why is it important that the unique identifiers start at 1 for each distinct value of idx1?

TMTOWDI - it's not just for Perl any more

 

[dhookom]

Is this for displaying in a report?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]