Using data from one row in the next

  • Thread starter Thread starter Jake Frankham
  • Start date Start date
J

Jake Frankham

Hi All

I would REALLY appreciate some assistance with this one, if anyone has a
spare minute?

I have a query which (in short) looks like this:

clientID clientName weeksDone

123 J Doe 5
123 J Doe 2
215 T Blair 4
215 T Blair 3

As you can see, there is a new record for each time a client 'does' some
weeks.
(There is a lot more to this table, I am just keeping it simple for brevity
!!)

What I need to do is this:
I need another column (say 'prevWeeks') which shows the previous weeks, thus
something like:

clientID clientName weeksDone prevWeeks

123 J Doe 5
123 J Doe 2 5
215 T Blair 4
215 T Blair 3 4

It would also be useful (but not essential) to keep a running total of the
weeks in each subsequent record, like:

clientID clientName weeksDone prevWeeks totalWeeks

123 J Doe 5 5
123 J Doe 2 5 7
215 T Blair 4 4
215 T Blair 3 4 7

Thanks everyone

Jake
 
There is no such thing as a "previous" record without some sort of
field/value that can be used to establish an order. Do you have a field
value that states:
123 J Doe 5 comes after 123 J Doe 2 ?
 
Hi Duane

No I don't have such a field/value

How would I add such a value (bearing in mind my table is actually a query)
Is this possible?

If not, please could you let me know the solution assuming I DID have such a
value

Many thanks again

Jake
 
Your query would need to use a subquery similar to:
SELECT qselClients.*,
(SELECT TOP 1 WeeksDone
FROM qselClients q
WHERE q.UniqueRankingField <qselClients.UniqueRankingField
ORDER BY UniqueRankingField DESC) as PrevWeeks
FROM qselClients;

UniqueRankingField is the field that identifies the order of the records.
 
Back
Top