Numbering in a table

E

Emma Hope

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
 
O

Ofer

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)
 
M

Marshall Barton

Emma said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top