Sequential Numbering- Please Help!!

[Guest]

I need to figure out a way in access to give sequential numbers to different
records based on another number (increasing in order) for each person. For
example, Jane Doe has 5 records and I need those to be numbered 1-5 based on
the numbers (or IDs) 97014, 97015, 97016, 97017, 97018. So I need to create
the sequential numbers at a "person" level. Does anyone have a simple way to
do this?
Thanks so much!!!

 

[Lynn Trapp]

Victoria,
You should check out the DMax() function in Help.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Tom Ellison]

Dear Victoria:

Using my own names for your table and columns:

SELECT Name, SequenceNumber,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Name = T.Name
AND T1.SequenceNumber < T.SequenceNumber) AS Rank
FROM YourTable T

This assumes that the Name and SequenceNumber columns you have are, when
taken together, unique. Otherwise, the Rank column may repeat, as it cannot
distinguish between itentical rows. An additional column could be used to
break ties if you wish.

Tom Ellison

 

[Guest]

I converted that to what is below for my purposes and everything came back as
having a rank of 1. Any suggestions?

SELECT [tbl7].[PATIENT_ID], [tbl7].[ND_ID], (SELECT COUNT(*) + 1 FROM tbl7
WHERE tbl7.PATIENT_ID = tbl7.PATIENT_ID AND tbl7.ND_ID <
tbl7.ND_ID) AS Rank INTO tbl8
FROM tbl7;

 

[Tom Ellison]

Dear Victoria:

I've edited your query a bit, below:

SELECT PATIENT_ID, ND_ID,
(SELECT COUNT(*) + 1 FROM tbl7 T1
WHERE T1.PATIENT_ID = tbl7.PATIENT_ID
AND T1.ND_ID < tbl7.ND_ID) AS Rank
FROM tbl7;

Certain features of what I proposed, specifically the aliasing of the table,
seem to have disappeared in your version. I have also dropped the INTO
clause, which you can reinsert when you're ready, as I feel this makes it
easier for you to see what is happening.

Does this help?

Tom Ellison

I converted that to what is below for my purposes and everything came back
as
having a rank of 1. Any suggestions?

SELECT [tbl7].[PATIENT_ID], [tbl7].[ND_ID], (SELECT COUNT(*) + 1 FROM tbl7
WHERE tbl7.PATIENT_ID = tbl7.PATIENT_ID AND tbl7.ND_ID <
tbl7.ND_ID) AS Rank INTO tbl8
FROM tbl7;

Dear Victoria:

Using my own names for your table and columns:

SELECT Name, SequenceNumber,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Name = T.Name
AND T1.SequenceNumber < T.SequenceNumber) AS Rank
FROM YourTable T

This assumes that the Name and SequenceNumber columns you have are, when
taken together, unique. Otherwise, the Rank column may repeat, as it
cannot
distinguish between itentical rows. An additional column could be used
to
break ties if you wish.

Tom Ellison

 

[Guest]

Working now. Thanks so much!

Dear Victoria:

I've edited your query a bit, below:

SELECT PATIENT_ID, ND_ID,
(SELECT COUNT(*) + 1 FROM tbl7 T1
WHERE T1.PATIENT_ID = tbl7.PATIENT_ID
AND T1.ND_ID < tbl7.ND_ID) AS Rank
FROM tbl7;

Certain features of what I proposed, specifically the aliasing of the table,
seem to have disappeared in your version. I have also dropped the INTO
clause, which you can reinsert when you're ready, as I feel this makes it
easier for you to see what is happening.

Does this help?

Tom Ellison

I converted that to what is below for my purposes and everything came back
as
having a rank of 1. Any suggestions?

SELECT [tbl7].[PATIENT_ID], [tbl7].[ND_ID], (SELECT COUNT(*) + 1 FROM tbl7
WHERE tbl7.PATIENT_ID = tbl7.PATIENT_ID AND tbl7.ND_ID <
tbl7.ND_ID) AS Rank INTO tbl8
FROM tbl7;

Dear Victoria:

Using my own names for your table and columns:

SELECT Name, SequenceNumber,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Name = T.Name
AND T1.SequenceNumber < T.SequenceNumber) AS Rank
FROM YourTable T

This assumes that the Name and SequenceNumber columns you have are, when
taken together, unique. Otherwise, the Rank column may repeat, as it
cannot
distinguish between itentical rows. An additional column could be used
to
break ties if you wish.

Tom Ellison

I need to figure out a way in access to give sequential numbers to
different
records based on another number (increasing in order) for each person.
For
example, Jane Doe has 5 records and I need those to be numbered 1-5
based
on
the numbers (or IDs) 97014, 97015, 97016, 97017, 97018. So I need to
create
the sequential numbers at a "person" level. Does anyone have a simple
way
to
do this?
Thanks so much!!!

 

[Tom Ellison]

Dear Victoria:

Well, that was quick!

Now, just in case it may help, here's how it works.

The subquery is correlated on PATIENT_ID so it can count only those rows
that are for that patient.

The subquery is filtered to show only those with an ND_ID that is less than
that of the current row.

The subquery then COUNTs the rows found. If there are no rows prior to the
current row, then the COUNT is zero, but the Rank should be one, so I added
one.

That's about it. Pretty handy feature of queries. You may find you need it
for many things as you progress.

Tom Ellison

Working now. Thanks so much!

Dear Victoria:

I've edited your query a bit, below:

SELECT PATIENT_ID, ND_ID,
(SELECT COUNT(*) + 1 FROM tbl7 T1
WHERE T1.PATIENT_ID = tbl7.PATIENT_ID
AND T1.ND_ID < tbl7.ND_ID) AS Rank
FROM tbl7;

Certain features of what I proposed, specifically the aliasing of the
table,
seem to have disappeared in your version. I have also dropped the INTO
clause, which you can reinsert when you're ready, as I feel this makes it
easier for you to see what is happening.

Does this help?

Tom Ellison

I converted that to what is below for my purposes and everything came
back
as
having a rank of 1. Any suggestions?

SELECT [tbl7].[PATIENT_ID], [tbl7].[ND_ID], (SELECT COUNT(*) + 1 FROM
tbl7
WHERE tbl7.PATIENT_ID = tbl7.PATIENT_ID AND tbl7.ND_ID <
tbl7.ND_ID) AS Rank INTO tbl8
FROM tbl7;

:

Dear Victoria:

Using my own names for your table and columns:

SELECT Name, SequenceNumber,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Name = T.Name
AND T1.SequenceNumber < T.SequenceNumber) AS Rank
FROM YourTable T

This assumes that the Name and SequenceNumber columns you have are,
when
taken together, unique. Otherwise, the Rank column may repeat, as it
cannot
distinguish between itentical rows. An additional column could be
used
to
break ties if you wish.

Tom Ellison

I need to figure out a way in access to give sequential numbers to
different
records based on another number (increasing in order) for each
person.
For
example, Jane Doe has 5 records and I need those to be numbered 1-5
based
on
the numbers (or IDs) 97014, 97015, 97016, 97017, 97018. So I need to
create
the sequential numbers at a "person" level. Does anyone have a
simple
way
to
do this?
Thanks so much!!!

 

[Chris2]

I need to figure out a way in access to give sequential numbers to different
records based on another number (increasing in order) for each person. For
example, Jane Doe has 5 records and I need those to be numbered 1-5 based on
the numbers (or IDs) 97014, 97015, 97016, 97017, 97018. So I need to create
the sequential numbers at a "person" level. Does anyone have a simple way to
do this?
Thanks so much!!!

Victoria,

Table:

CREATE Table Unknown_11022005_1
(UnknownID AUTOINCREMENT
,IDs INTEGER
,Person TEXT(10)
,CONSTRAINT pk_Unknown_11022005_1 PRIMARY KEY (UnknownID)
)


Sample Data:

1, 97010, Sara Doe
2, 97011, Sara Doe
3, 97012, Sara Doe
4, 97013, Sara Doe
5, 97014, Jane Doe
6, 97015, Jane Doe
7, 97016, Jane Doe
8, 97017, Jane Doe
9, 97018, Jane Doe


Query:

SELECT (SELECT COUNT(U01.IDs) + 1
FROM Unknown_11022005_1 AS U01
WHERE U01.UnknownID > U1.UnknownID) AS NewIDs
,U1.Person
FROM Unknown_11022005_1 AS U1
ORDER BY 1


Output:

1, Jane Doe

2, Jane Doe

3, Jane Doe

4, Jane Doe

5, Jane Doe

6, Sara Doe

7, Sara Doe

8, Sara Doe

9, Sara Doe





Sincerely,



Chris O.