sequence number by group from query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Is it possible to generate a sequence number that resets by group from a
query?

Heres some data from the table:

Week Date Boxes
2 09/01/06 842
2 13/01/06 805
3 16/01/06 900

I would like it to appear as:

Week 1st 2nd
2 842 805
3 900

I followed an example by Duane Hookum but couldn't get it to work.

Any ideas?
Matt
 
Looks like you want a crosstab query that changes the date to a value like
1st, 2nd, etc. It would be rather complicated.
 
Hi,

I've managed the query, now i need to output the results properly - joys of
pivot tables.
Heres my final query:

SELECT W1.DepWeek,
W1.[D-Date],
W1.Code,
W1.CTMoves,
(SELECT Count(W3.EVoy) AS CountOfEVoy
FROM [W Vessel Actual Exchange Rate by vessel] AS W3
WHERE (((W1.DepWeek)=[W3].[DEPWEEK]) and (w1.service = w3.service) and
(w1.[d-date] >= w3.[d-date]))
) AS Seq,
W1.Service
FROM [W Vessel Actual Exchange Rate by vessel] AS W1;
 
Back
Top