create a counter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fasiano - 26 Feb 2007 17:43 GMT

If you please could help me i will be very glad.
this is what i have:
StudentID Class Date
S031N0032 EENG-122 1/1/2007
S031N0032 EENG-122 3/5/2007
S031N0032 EENG-122 6/7/2007
S022N0012 EENG-443 3/2/2005

This is what i want:
StudentID Class Date Counter
S031N0032 EENG-122 1/1/2007 1
S031N0032 EENG-122 3/5/2007 2
S031N0032 EENG-122 6/7/2007 3
S022N0012 EENG-443 3/2/2005 1

When the combination of StudentID and Class change(either of two changes),
the counter should start from 1.
Thank you

Reply to this Message

Evan Camilleri - 26 Feb 2007 17:50 GMT

you'd have to do it via vba-code. do you have any idea of vba coding?

where are you entering these records.... in a form?

> If you please could help me i will be very glad.
> this is what i have:
[quoted text clipped - 14 lines]
> the counter should start from 1.
> Thank you

Reply to this Message

Jamie Collins - 27 Feb 2007 15:42 GMT

> > If you please could help me i will be very glad.
> > this is what i have:
[quoted text clipped - 15 lines]
>
> you'd have to do it via vba-code.

"Have to"? I do not agree. A SQL solution is possible e.g.

CREATE TABLE StudentSchedule (
StudentID VARCHAR(9) NOT NULL,
[Class] VARCHAR(8) NOT NULL,
[Date] DATETIME NOT NULL
)
;
INSERT INTO StudentSchedule (StudentID, [Class], [Date])
VALUES ('S031N0032', 'EENG-122', #2007-01-01 00:00:00#)
;
INSERT INTO StudentSchedule (StudentID, [Class], [Date])
VALUES ('S031N0032', 'EENG-122', #2007-03-05 00:00:00#)
;
INSERT INTO StudentSchedule (StudentID, [Class], [Date])
VALUES ('S031N0032', 'EENG-122', #2007-06-07 00:00:00#)
;
INSERT INTO StudentSchedule (StudentID, [Class], [Date])
VALUES ('S022N0012', 'EENG-443', #2007-03-02 00:00:00#)
;
SELECT S1.StudentID, S1.[Class], S1.[Date], (
SELECT COUNT(*)
FROM StudentSchedule AS S2
WHERE S1.StudentID = S2.StudentID
AND S1.[Class] = S2.[Class]
AND S1.[Date] >= S2.[Date]) AS seq
FROM StudentSchedule AS S1
;

Jamie.

--

Reply to this Message