Numering records in a group

[Guest]

I have a table linked to another one with a one-to-many relationship.
I need a query that would number the records in a group indicated in field
POS.

Table A Table B
ID ID DETAIL POS
1 1 A 1
2 1 B 2
3 3 C 1
4 4 D 1
4 D 2

How could I achieve that?

Thanks in advance for any help.

 

[Tom Ellison]

Dear Claudio:

Please show what the result of this query would be. I believe I can do it.
Specifically, in what unique order would you want the rows numbered? In
the alphabetical order of DETAIL, for example?

Tom Ellison

 

[Guest]

Dear Tom,

Thanks for your willingness.
Maybe I was not clear enough.
The result of the query should be the Field POS which I understand the way I
put it down looked like was already on the Table, but it is not. I would like
to create it. In other words when in Table B there are several details for
one ID I would liked these to be numbered in an ascending numerical order.
Hope this help clarifying.

Thanks for your time.

 

[Tom Ellison]

Dear Claudio:

"numbered in ascending numerical order" according to what? Randomly? Using
some order in the data? That's what I'm trying to find out so I may be able
to help.

I asked for you to show how this numbering would apply to the data shown.
If not randomly numbering them, then what?

Tom Ellison

 

[Guest]

Tom,
This is similar to my problem of displaying the 25th record for each group.
My initial query qryUniqueChronologicalWords displays a s follows:

SubjectID DateSpoken WordSpoken
1 1/1/06 Hi
1 1/1/06 Daddy
1 1/2/06 Mama
2 12/22/05 Boy
2 12/23/05 Dog
3 2/1/06 Baby

I think I need to number the records within each SubjectID so I can then
query for the 25th and 50th records for each SubjectID and also get the first
25 records for each SubjectID.
Chip

 

[Tom Ellison]

Dear Chip:

You do not need to number all of them to do this, but it uses a very similar
method to Ranking to just find the one you want. However, if there are
fewer than 25 or 50 rows, it won't find any. Would you want it to find the
19th row when that's all there are? I won't cover that option at this time.

Now, you want the 25th row, but you must specify the order that is used to
count them. Do you want the 25th row in DateSpoken order. In WordSpoken
order? Or would you like to sort them using bothe these columns? Or
something else? Also, please note that if there are rows in a set that have
identical Date and Word, and there is a 2 way tie for 24th place, or a 3 way
tie for 23rd or 24th place, then there is no row (or rows) at 25th place!
Uniqueness in the ordering has no substitute! Also, to be reliable,
uniqueness must be enforced.

Now, I just assumed that your term "Group" meant SubjectID. Was that the
correct assumption. If you think I'm being picky about such definitions,
wait till you see what the computer does with any misdefined terms for this!
You're going to have to dot every T and cross your eyes to get the system to
do this! So, obviously I have to be certain about what you want to begin to
construct it.

And, it can get pretty complex, too, depending on your answers to all these
questions! Remember that the ranking columns must be specified IN ORDER of
their significance. That makes a difference, too.

I feel just like I do trying to get a specification out of a client now.

Tom Ellison

 

[Guest]

Tom,
Yes, the SubjectID is the grouping. The ranking is by DateSpoken. To prevent
a tie, I would use wordspoken as the second ranking. The three fields
guarantee uniqueness. Also, I will not want any records for a subject who
does not reach the required number. Our end result will be displaying the
age when a subject spoke his/her 25th new word and 50th new word then
separately display a list of the first 25 words with a category (noun,
verb,etc.) All that will be simple if I can get the first part.
Chip

 

[Tom Ellison]

Dear Chip:

I think we're ready to rock and roll now!

SELECT SubjectID, DateSpoken, WordSpoken
FROM qryUniqueChronologicalWords Q
WHERE (SELECT COUNT(*)
FROM qryUniqueChronologicalWords Q1
WHERE Q1.SubjectID = Q.SubjectID
AND (Q1.DateSpoken < Q.DateSpoken
OR (Q1.DateSpoken = Q.DateSpoken
AND Q1.WordSpoken < Q.WordSpoken))
= 24)

How is that for a starting point?

Now, if two rows are really tied (not unique as you promised) so that both
are in 24th place, then the next place is 26th place, and there IS NOT 25th
place. That result will me MISSING just like happens when there are fewer
than 25 words. There could be a million of them, but there's no 25th place.
So, if this has to be unique, you may want to place a constraint on your
table, that is a Primary Key or Unique Index, to absolutely enforce this
requirement.

Tom Ellison

 

[Guest]

Tom,
I appreciate your help. However...
I get zero records. I have looked at the input query and I should get
results for 90% of the SubjectIDs. The query runs for 4 minutes even though
there are only 1300 records in the input query.

 

[Tom Ellison]

Dear Chip:

I can offer two options:

- Email me with the attached database. First, make a copy of the database
and change the file extension to XXX. (You may have to change a Windows
Explorer setting to even be able to see file extensions.) Zip it and attach
it. If it bounces, then it's too large for the email server I use. We'll
have to make other arrangements.

- Post again, and I'll leave the thread alone. Someone else may figure this
out.

As for performance, some index tweaking may be in order. I'll attempt that
if you go the first route.

Tom Ellison