Pardon me, for missing that.
I tried with the second select and
unfortunately this gives a total number of records.
The result is:
Teacher Class Period Student Stu_No
Mr. A A 1 Joe 159
Mr. A A 1 Joe 159
Mr. A A 1 Bob 159
Mr. A A 1 Jim 159
Mr. A A 2 Sue 159
Mr. A B 1 Jon 159
What I need is:
Teacher Class Period Student Stu_No
Mr. A A 1 Joe 1
Mr. A A 1 Joe 2
Mr. A A 1 Bob 3
Mr. A A 1 Jim 4
Mr. A A 2 Sue 1
Mr. A B 1 Jon 1
Can't this be done?
-----Original Message-----
Your query doesn't resemble mine suggestion. I had
two "SELECT " in my query
versus your one. Try this:
SELECT Teacher, Class, Period, Student,
(SELECT Count(*) FROM DailySchedData A
WHERE A.Teacher = DailySchedData.Teacher
AND A.Class = DailySchedData.Class
AND A.Period = DailySchedData.Period
AND A.Student <= DailySchedData.Student) as Stu_No
FROM DailySchedData
ORDER BY Teacher, Class, Period, Student;
--
Duane Hookom
MS Access MVP
--
message
I am unable to make the query example that you gave me
work. I receive the message "You tried to execute a
query that does not include the specified
expression "Teacher" as part of an aggregate function."
it looks like this:
SELECT DailySchedData.TEACHER, DailySchedData.Class,
DailySchedData.Period, testA.STUDENT, Count(*) AS STuNo
FROM DailySchedData INNER JOIN testA ON
(DailySchedData.Shift = testA.Shift) AND
(DailySchedData.PosNum = testA.PosNum)
AND (DailySchedData.Class = testA.Class)
AND (DailySchedData.TEACHER = testA.TEACHER)
WHERE (((testA.STUDENT)<=[DailySchedData]! [STUDENT]))
ORDER BY DailySchedData.TEACHER, DailySchedData.Class,
DailySchedData.Period, testA.STUDENT;
I know that the numbering would be easier at the report
level, if I could get away with that I would do it in a
second. However, I have to create a way to number the
students based on Teacher, Class, Period. - This
numbers
corresponds to an already in-place schedule system.
Is there anything else that I can try?
Or to alter the query?
thank you for your help
-----Original Message-----
You need to add Student to the sort. Also, I asked if
this was going to a
report where the numbering would be much easier. The
following query assumes
your table name is tblA. Change all "tblA" to your
table
or query name.
SELECT Teacher, Class, Period, Student,
(SELECT Count(*) FROM tblA A
WHERE A.Teacher = tblA.Teacher
AND A.Class = tblA.Class
AND A.Period = tblA.Period
AND A.Student <=tblA.Student) as Stu_No
FROM tblA
ORDER BY Teacher, Class, Period, Student;
--
Duane Hookom
MS Access MVP
--
in
message
I do need the counting to occur at either the query
level
or at the table level.
I am sorting by teacher, class, period > this is
where
I
need to have the stu_no > to number each student
that
falls into that section.
If it were feasible and a onetime thing - I would
create
several tables for each teacher, class, period and
make
an autonumber for the stu_no - then just join them
together.
However, this data is always going to be changing
as it
is a schedule.
I am REALLY stuck with this and don't know the way
to
accomplish the output.
I would appreciate any help at all that you could
offer.
thank you
-----Original Message-----
All query designs allow sorting. Is this query
going
into a report? If so,
you should do your counting there. If not, come on
back.
--
Duane Hookom
MS Access MVP
"Maureen" <
[email protected]>
wrote
in
message
I was looking at the query and with a totals
query
there
is no order by only group by.
-----Original Message-----
The order by is Teacher, class, period
each row that has a different student would
cause
the
stu_no to increase by 1.
When the next row has a new Teacher, class or
period,
the
stu_no would start at 1 again.
Basically counting and number each student for
each
teacher, class, period.
-----Original Message-----
I don't think you can get a running total
without
an
ORDER BY. I didn't
notice an ORDER BY in your sample that matched
anything
that looked like a
running sum. I like puzzles but it would help
if
you
would describe the
pattern.
--
Duane Hookom
MS Access MVP
"Maureen"
AS Stu_No