add a number sequence field in Query that resets for every set of

[Guest]

Hi,

How can I add a field in my query that produces a sequencial number that
resets for every group of data (grouped in ascending order). For example, my
table contains:

ID Dte IN OUT *New Field*
1000 2/1/05 10:00 11:00 1
1000 2/1/05 12:00 14:00 2
1000 2/1/05 15:00 18:00 3
1000 2/2/05 12:00 15:00 1
1000 2/2/05 16:00 18:00 2
1000 2/3/05 11:00 15:00 1

The *new field* should be assigned based on ascending order of (ID, DTE and
IN fields).

My goal is to make a crosstab query out of this and put it in a report that
will look like this:

"ID"

Date 1st Shift 2nd Shift 3rd Shift
2/1/05 10:00 - 11:00 12:00 - 14:00 15:00 - 18:00
2/2/05 12:00 - 15:00 16:00 - 18:00
2/3/05 11:00 - 15:00

Thanks a lot.

 

[Duane Hookom]

One possible solution:
SELECT tblArlene.ID, tblArlene.Dte, tblArlene.[IN], tblArlene.OUT,
Count(tblArlene.ID) AS NewField
FROM tblArlene INNER JOIN tblArlene AS tblArlene_1
ON (tblArlene.Dte = tblArlene_1.Dte) AND (tblArlene.ID = tblArlene_1.ID)
WHERE tblArlene.[IN]>=[tblArlene_1].[In]
GROUP BY tblArlene.ID, tblArlene.Dte, tblArlene.[IN], tblArlene.OUT;

 

[Guest]

Thank you! I've just tried it now, it works out beautifully.

One possible solution:
SELECT tblArlene.ID, tblArlene.Dte, tblArlene.[IN], tblArlene.OUT,
Count(tblArlene.ID) AS NewField
FROM tblArlene INNER JOIN tblArlene AS tblArlene_1
ON (tblArlene.Dte = tblArlene_1.Dte) AND (tblArlene.ID = tblArlene_1.ID)
WHERE tblArlene.[IN]>=[tblArlene_1].[In]
GROUP BY tblArlene.ID, tblArlene.Dte, tblArlene.[IN], tblArlene.OUT;


--
Duane Hookom
MS Access MVP

Hi,

How can I add a field in my query that produces a sequencial number that
resets for every group of data (grouped in ascending order). For example,
my
table contains:

ID Dte IN OUT *New Field*
1000 2/1/05 10:00 11:00 1
1000 2/1/05 12:00 14:00 2
1000 2/1/05 15:00 18:00 3
1000 2/2/05 12:00 15:00 1
1000 2/2/05 16:00 18:00 2
1000 2/3/05 11:00 15:00 1

The *new field* should be assigned based on ascending order of (ID, DTE
and
IN fields).

My goal is to make a crosstab query out of this and put it in a report
that
will look like this:

"ID"

Date 1st Shift 2nd Shift 3rd Shift
2/1/05 10:00 - 11:00 12:00 - 14:00 15:00 - 18:00
2/2/05 12:00 - 15:00 16:00 - 18:00
2/3/05 11:00 - 15:00

Thanks a lot.