Numbering in a table

[Guest]

Hi All,

I have two tables with a one to many relationship, one table has a single
record with an autonumbered field for Clients, the other table (for delays)
has a many records per client, linked by the same number as on the Client and
also have a unique autonumbered field i.e.

Client table
ID
1 Joe Bloggs
2 John Smith
3 M. Mouse

Delay table
ID ClientID Comment
1 1 Reason1
2 1 Reason2
3 1 Reason1
4 2 Reason7
5 2 Reason3
6 2 Reason2
7 2 Reason2

I need to report on the last three delays for each Client ID, is there any
way i can number as follows:

ID ClientID Comment Number
1 1 Reason1 1
2 1 Reason2 2
3 1 Reason1 3
4 2 Reason7 1
5 2 Reason3 2
6 2 Reason2 3
7 2 Reason2 4

and then pick out numbers 1, 2, & 3 for ClientID 1 and 2, 3 & 4 for ClientID
2 or any other way of doing this, using 'max' or 'last' only gives me the
last one reason.

Thanks
Emma

 

[Van T. Dinh]

See if the following Microsoft Knowledge Base articles help:

http://support.microsoft.com/?id=210039

and

http://support.microsoft.com/?id=208822

 

[Guest]

Hi Emma
If all you want is the last three entries, there is no need to add the count.
Try this different approach

Delay table
ID ClientID Comment
1 1 Reason1
2 1 Reason2
3 1 Reason1
4 2 Reason7
5 2 Reason3
6 2 Reason2
7 2 Reason2

SELECT M1.ID, M1.ClientID ,M1.Comment
FROM [Delay table] AS M1
WHERE M1.ID In (SELECT Top 3 M2.ID
FROM [Delay table] as M2
WHERE M2.ClientID=M1.ClientID
ORDER BY M2.ID Desc)

 

[Marshall Barton]

I have two tables with a one to many relationship, one table has a single
record with an autonumbered field for Clients, the other table (for delays)
has a many records per client, linked by the same number as on the Client and
also have a unique autonumbered field i.e.

Client table
ID
1 Joe Bloggs
2 John Smith
3 M. Mouse

Delay table
ID ClientID Comment
1 1 Reason1
2 1 Reason2
3 1 Reason1
4 2 Reason7
5 2 Reason3
6 2 Reason2
7 2 Reason2

I need to report on the last three delays for each Client ID, is there any
way i can number as follows:

ID ClientID Comment Number
1 1 Reason1 1
2 1 Reason2 2
3 1 Reason1 3
4 2 Reason7 1
5 2 Reason3 2
6 2 Reason2 3
7 2 Reason2 4

and then pick out numbers 1, 2, & 3 for ClientID 1 and 2, 3 & 4 for ClientID
2 or any other way of doing this, using 'max' or 'last' only gives me the
last one reason.

There is a flaw in your thinking here. You are missing a
field that determines the order of the records so there is
no way to determine the "last" anything about the data.

If you were thinking that a table remembers when records
were entered or that the autonumbner can be used for this
purpose, you are mistaken. Note that an autonumber primary
key is only required to be unique, they can go negative, may
become random and, if you should ever use Replication, will
not even be a number.

A common way to keep track of the oder that the records were
created is to add a date field (Default to =Date()) or to
use code to calculate you Number value when the record is
created. The latter can be done only if you use a form to
add the records and the form's BeforeUpdate event procedure
would use code like:
Me.Numfield = Nz(DMax("Numfield", "Delay", _
"ClientID = " & Me.ClientID), 0) + 1