Make a sequence number in a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RSunday - 19 Nov 2007 22:11 GMT

How do I make a query that numbers each record in a separate field?

Even better - numbers the records in the child table - i.e. every time the
parent table has a new record, the sequence in the child-records starts from
1 again.

I want to use this field for conditional formatting , where everything but
the first parent record (sequence = 1) gets special formatting.

Reply to this Message

mscertified - 19 Nov 2007 23:43 GMT

What do you mean by the 'first parent record'. Records in a relational
database have no inherent sequence except by a column or columns by which
they may be sorted. So just do the sort and then apply the conditional
formatting to the first record returned by the sort. Or maybe I am not
understanding your question?

-Dorian

> How do I make a query that numbers each record in a separate field?
>
[quoted text clipped - 4 lines]
> I want to use this field for conditional formatting , where everything but
> the first parent record (sequence = 1) gets special formatting.

Reply to this Message

RSunday - 20 Nov 2007 08:11 GMT

I have a table of families (my "parent table") and a table with family
members (my "child table"). I can make a joined query giving me:

The Johnsons Kyle
The Johnsons Sandra
The Johnsons Peter
The Johnsons Toni
The Smiths Amy
The Smiths Joe
The Ryans Jack
The Ryans Cathy
The Ryans Sally

This I can show in a continuous form - but I want it to look:

The Johnsons 1 Kyle
2 Sandra
3 Peter
4 Toni
The Smiths 1 Amy
2 Joe
The Ryans 1 Jack
2 Cathy
3 Sally

And here I figured I could just make the family name font color go white
(like the background) if the member number > 1. So how do I make my query
number the members?

Forget about the purpose of this example - it is a made up example - but my
problem is real.

> What do you mean by the 'first parent record'. Records in a relational
> database have no inherent sequence except by a column or columns by which
[quoted text clipped - 12 lines]
> > I want to use this field for conditional formatting , where everything but
> > the first parent record (sequence = 1) gets special formatting.

Reply to this Message

John Spencer - 20 Nov 2007 12:18 GMT

It sounds as if you need a ranking query. With your sample that would look
something like the following

SELECT FamilyName, IndividualName
, (SELECT Count(*) FROM TheTable as TB
WHERE TB.FamilyName = TA.FamilyName
AND TB.IndividualName > TA.IndividualName) + 1 as Rank
FROM TheTable as TA

For this to work as specified, you would need to display the data in order
of the fields used to determine the ranking within the group - by FamilyName
and IndividualName.

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I have a table of families (my "parent table") and a table with family
> members (my "child table"). I can make a joined query giving me:
[quoted text clipped - 48 lines]
>> > but
>> > the first parent record (sequence = 1) gets special formatting.

Reply to this Message