Make a sequence number in a query

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.

 

[John Spencer]

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.

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