Counting many fields

[jkearns]

Hi,

I'm having trouble getting a COUNT() on many fields in a table. I have
a table that records the number of times a Student was absent or late
on a given day (attendance is taken 3 times every day). My fields look
like:

StudentID (PK) - Number
TodaysDate (PK) - Date
AssemblyLate - Yes/No
AssemblyAbsent - Yes/No
MorningLate - Yes/No
MorningAbsent - Yes/No
AfternoonLate - Yes/No
AfternoonAbsent - Yes/No

What I want to do, is get the number of times a Student was late or
absent over a given time period. I have qryMultiDay that grabs all of
these fields but then limits TodaysDate to between X and Y (info
provided from a form). I then have 6 more queries (one for each
late/absent) that query qryMultiDay to find the count of each field.
For example, the AfternoonLate query looks like:

SELECT qryMultiDay.CurrentID, Count(qryMultiDay.AfternoonLate) AS
CountOfAfternoonLate
FROM qryMultiDay
GROUP BY qryMultiDay.CurrentID, qryMultiDay.AfternoonLate
HAVING (((qryMultiDay.AfternoonLate)=True));

I am then trying to join all of these 6 quries together to make a
report that might look like:

Student Assembly L A Morning L A Afternoon L A
Student01 0 4 0 4 1 4
Student02 2 1 0 1 2 2
etc.

Problem: when I join all of the queries, I get no results. Any
suggestions on what might be wrong (I think it has something to do with
the fact that all students do not appear in all records), or a better
way to do this?

Thanks in advance!
JK

 

[Marshall Barton]

I'm having trouble getting a COUNT() on many fields in a table. I have
a table that records the number of times a Student was absent or late
on a given day (attendance is taken 3 times every day). My fields look
like:

StudentID (PK) - Number
TodaysDate (PK) - Date
AssemblyLate - Yes/No
AssemblyAbsent - Yes/No
MorningLate - Yes/No
MorningAbsent - Yes/No
AfternoonLate - Yes/No
AfternoonAbsent - Yes/No

What I want to do, is get the number of times a Student was late or
absent over a given time period. I have qryMultiDay that grabs all of
these fields but then limits TodaysDate to between X and Y (info
provided from a form). I then have 6 more queries (one for each
late/absent) that query qryMultiDay to find the count of each field.
For example, the AfternoonLate query looks like:

SELECT qryMultiDay.CurrentID, Count(qryMultiDay.AfternoonLate) AS
CountOfAfternoonLate
FROM qryMultiDay
GROUP BY qryMultiDay.CurrentID, qryMultiDay.AfternoonLate
HAVING (((qryMultiDay.AfternoonLate)=True));

I am then trying to join all of these 6 quries together to make a
report that might look like:

Student Assembly L A Morning L A Afternoon L A
Student01 0 4 0 4 1 4
Student02 2 1 0 1 2 2
etc.

Problem: when I join all of the queries, I get no results. Any
suggestions on what might be wrong (I think it has something to do with
the fact that all students do not appear in all records), or a better
way to do this?

You can do all this in a single query:

SELECT qryMultiDay.CurrentID,
Abs(Sum(AssemblyLate)) As NumAfternoonLate,
Abs(Sum(MorningLate)) As NumMorningLate,
. . .
FROM thetable
WHERE TodaysDate Between X And Y

 

[Tom Ellison]

Dear JK:

An apple to the teacher!

I'm afraid I need to inform you that your problems are of your own creation.
The solution is going to be slightly painful, whether you choose to fix the
way the data is stored, or if you write a complex query to solve the
immediate problem and then wait for the monster to re-emerge.

OK, if you wanted to build this so it worked easily, your table would look
like:

StudentID(PK)
TodaysDate(PK)
Incident(PK)

The Incident column would contain one of the text values:

AssemblyLate
AssemblyAbsent
MorningLate
MorningAbsent
AfternoonLate
AfternoonAbsent

The existence of a record in this table represents one "infraction". A
student with no infractions would not appear in the table at all. I call
this a "sparse matrix" meaning that there is an imaginary matrix with one
dimension being a student, another being the date, and the third, the
specific infraction. Of all the possible "intersections" in this matrix,
only a few are recorded.

If you do this, the query work gets really easy. You can count how many
students were late on a given morning within a range of dates. You can
count how many times a given student was absent in the afternoon within a
range of dates. You can count how many dates there were no students absent
from assembly (given a table of the dates when assembly was held). Very,
very powerful. And these queries would be fairly simple, compared to the
(I'm sorry to say) mess you've got now.

Leaving that alone for now (let me know if you need help filling out that
concept if you want to do the work), there are still two approaches without
changing the table and rearranging all the data.

Now, I suppose you can COUNT() how many times each student was AssemblyLate
within a time frame. For the total instances he was AssemblyLate, or
MorningLate, or AfternoonLate, I'm thinking:

SELECT CurrentID,
(SELECT Count(*) FROM qryMultiDay Q1
WHERE Q1.CurrentID = Q.CurrentID
AND Q1.AssemblyLate = True)
AS CountOfAssemblyLate,
(SELECT Count(*) FROM qryMultiDay Q1
WHERE Q1.CurrentID = Q.CurrentID
AND Q1.MorningLate = True)
AS CountOfMorningLate,
(SELECT Count(*) FROM qryMultiDay Q1
WHERE Q1.CurrentID = Q.CurrentID
AND Q1.AfternoonLate = True)
AS CountOfAfternoonLate
FROM qryMultiDay Q
GROUP BY CurrentID

You can easily change this to sum the 3 categories of Late for each student:

SELECT CurrentID,
(SELECT Count(*) FROM qryMultiDay Q1
WHERE Q1.CurrentID = Q.CurrentID
AND Q1.AssemblyLate = True) +
(SELECT Count(*) FROM qryMultiDay Q1
WHERE Q1.CurrentID = Q.CurrentID
AND Q1.MorningLate = True) +
(SELECT Count(*) FROM qryMultiDay Q1
WHERE Q1.CurrentID = Q.CurrentID
AND Q1.AfternoonLate = True)
AS CountOfAllLate
FROM qryMultiDay Q
GROUP BY CurrentID

Does this work for you? If you wish to study how this is done, look up
topics like subquery and aliasing. Or come back here (after you've tried to
look these up, please) and I'll try to fill you in.

The middle solution is to create the appearance of the "properly normalized"
data with UNION query, from which you can easily obtain everything you want,
and a lot more, just like I first described. Let me know if you want me to
take the time to explain this further.

Tom Ellison

 

[Marshall Barton]

Oh bleep, I made a mess of that. Try this instead

You can do all this in a single query:

SELECT CurrentID,
Abs(Sum(AssemblyLate)) As NumAfternoonLate,
Abs(Sum(MorningLate)) As NumMorningLate,
. . .
FROM thetable
WHERE TodaysDate Between X And Y
GROUP BY CurrentID

Thanks for catching that Tom ;-)

 

[Tom Ellison]

Hey, no problem, my friend. Note that I was just a bit tactful (unusual for
me, I expect) in just letting you fix it. Not that I wasn't absolutely
certain all you'd need was a hint.

Tom

 

[jkearns]

Tom

As I have no data yet (other than test data), so I can change the
backend of the database without too much trouble.

I never really thought about normalizing this part of the database to
that extent but in thinking about it now, it makes much more sense.

tblAttendance:
StudentID (PK)
TodaysDate (PK)
InfractionID (PK)

tblInfraction:
InfractionID (PK)
InfractionDescription

tblInfraction
ID Descripiton
1 Assembly - Late
2 Assembly - Absent
3 Morning - Late
4 Morning - Absent
5 Afternoon - Late
6 Afternoon - Absent

I don't really understand how if I then query this how it will be
simpler to count the number of individual infractions. I'm thinking it
would look like:

SELECT StudentID, Count(Infraction)
FROM tblAttendance
WHERE Infraction = 1 AND TodaysDate > 1/1/2005 AND TodaysDate <
1/5/2005;

This will give me the count of the number of times each student was
Late for Assembly between the 1st and 5th of January, but then I'm
stuck with the same problem of before of having to join all of these
individual queries together to make a report that shows how many times
each student was late and/or absent for each of the attendance periods.

What am I missing? Thanks for your help!

JK

 

[Tom Ellison]

Dear JK:

I'm glad you're thinking on this. Here's how I expect it to work:

SELECT StudentID, COUNT(*) AS CountOfLate
FROM tblAttendance
WHERE Infraction IN("Assembly - Late", "Morning - Late", "Afternoon -
Late")
AND TodaysDate BETWEEN #1/1/2005# AND #1/5/2005#

I tend to always COUNT(*) rather than put a column name in there. If you
put a column name in there, then it won't matter which column name you use,
because it is counting rows in the table independent of what is in any
particular column. That's just a style thing for me, but it keeps me from
even having the think about what I'm really counting.

Indeed, I'd have a table of infractions as you show. I'd have a column in
that table that separates Late from Absent. You could then JOIN to this
table and query Late by this value from the infractions table. Any
attribute of the data you're storing can, and should be modelled. It may
come in handy. Perhaps a student comes to class but leaves early for a
doctor's appointment. That's not Late, nore is it Absent. It's something
else. Heck, I'd have a table for AbsenceTypes like: Late, Absent, Departed
Early, Excused for Band, and whatever else you may need to record. You
don't have to anticipate all the possibilities ahead of time. You can just
insert them as they come up.

I'd at least consider more detail. Each AttendanceException (not all are
necessarily infractions) would be either a complete absence, or a partial
absence, like Late, Left Early, or even Left and Returned. Some absences
may be because the student was at the office, and this may or may not be for
disciplinary reasons. Another column would say whether the infraction was
excused (illness, other school function, parental request) or not. To some
extent, you may want to record a number of such attributes for each
CauseOfAbsence. This allows them to be summarized together or separately.

Now, I'm not so much trying to tell you about what your job has to look like
when you're done. It's about getting yourself to think about what will be
available for your reporting later on. Will you eventually want to track
all the time a student is gone from the classroom for any reason, then
whether this was a mandatory absence (called out by parent or another staff
member), an accidental absence (illness, injury, clothing damaged) a
parental absence, etc.

The next step would be how to model the things you want to be able to tap
into later. Where do these attributes belong. What classes of attributes
do they form (this is usually a set of mutually exclusive members making up
a class). It's both a science and and art. Except when it gets screwed up.
Then its anywhere from a missed opportunity to a disaster.

So, I'm glad to get you thinking along these lines early on, when the
project is not so far advanced, and no great amount of effort has been
squandered.

One tip I'd like to offer (and this can be controversial) is about your
InfractionID. I have found it very advantageous just to have a Natural Key
for something like this. You don't need an ID on everything, especially
extremely small table, ones that may never have 100 rows in any forseeable
circumstance. You need the InfractionDescription to be unique anyway. It
makes a perfectly good key as is.

Tom Ellison

 

[John Vinson]

Hi,

I'm having trouble getting a COUNT() on many fields in a table.

That's because the Count() operator doesn't count fields: it counts
records. Count([fieldname]) will count all of the records for which
that field is not NULL.

You can count yes/no fields by using the fact that YES is stored as -1
(+1 in SQL/Server) and NO is stored as zero. If you use

Sum(Abs([yesnofieldname]))

you'll get a count of the YES values.

John W. Vinson[MVP]

 

[jkearns]

Tom,

Thanks for all of that. It all certainly makes sense. The only thing
is that I'm not trying to count the number of times a student was late
(generally). I want to specifically know how many times in a given
time period, a student was Late to Assembly, Absent from Assembly, Late
to Morning, Absent from Morning, Late to Afternoon, and Absent from
Afternoon. So, I'd have six queries that looked like:

SELECT StudentID, COUNT(*) AS CountOfAssemblyLate
FROM tblAttendance
WHERE InfractionID = 1
AND TodaysDate BETWEEN #1/1/2005# AND #1/5/2005#

SELECT StudentID, COUNT(*) AS CountOfAssemblyAbsent
FROM tblAttendance
WHERE InfractionID = 2
AND TodaysDate BETWEEN #1/1/2005# AND #1/5/2005#

SELECT StudentID, COUNT(*) AS CountOfMorningLate
FROM tblAttendance
WHERE InfractionID = 3
AND TodaysDate BETWEEN #1/1/2005# AND #1/5/2005#

SELECT StudentID, COUNT(*) AS CountOfMorningAbsent
FROM tblAttendance
WHERE InfractionID = 4
AND TodaysDate BETWEEN #1/1/2005# AND #1/5/2005#

SELECT StudentID, COUNT(*) AS CountOfAfternoonLate
FROM tblAttendance
WHERE InfractionID = 5
AND TodaysDate BETWEEN #1/1/2005# AND #1/5/2005#

SELECT StudentID, COUNT(*) AS CountOfAfternoonAbsent
FROM tblAttendance
WHERE InfractionID = 6
AND TodaysDate BETWEEN #1/1/2005# AND #1/5/2005#

After I have these six queries that each counts a different Infraction,
how can I then combine these in one query to make a report of that
shows all the students that committed an infraction, and how many of
each infraction they committed?

Thanks,
JK

 

[Tom Ellison]

Dear JK:

You are counting on certain values of InfractionID to have specific
meanings, as shown in your example of the table. That will work, but it
makes reading your code dependent on knowing these code values. I must tell
you I don't like it, for reasons that may become apparent over the years,
especially if there happen to be more infractions that need to be added.
Did you understand my recommendation to have a column in tblInfractions that
tells you whether a given infraction is "late" or "absent", along with the
ability to add new InfractionIssue values like Late, Absent, or Left Early?

As to your question, I think maybe you are going for a cross-tab appearance:

StudentID 1 2 3 4 5 6
12345 3 1
12346 1 9

Above, Student 12345 was never late for assembly, or absent at all, but was
late for morning 3 times and afternoon once. Student 12346 was late for
assembly once and absent from mornings 9 times. Is that it?

Having ID codes for everything, rather than students' names and the names of
the Infractions would seem to be unwieldy. But then, I'm this nut who
thinks using a natural key for relationships is a good thing, especially for
any table that has only a few rows. Well, that discussion has been beaten
to death more than once. But this does illustrate part of that point.
Without a natrual key for the relationship, you must JOIN to the Student
table to get the person's name and to the Infraction table to determine the
name of the infraction. Now, certainly, that will work! The big argument
for using surrogate (ID) keys for relationships is that it's faster. Well,
it's not faster for sure when all you need is the value that is in the
natural key, which would have been in the dependent table, thus requiring no
JOIN lookup, is all you want. Also, I recommend you had better have a
unique index on both the student table name (or other natural key) and on
the Infraction table Description. If you're going to report these values,
as in the crosstab above, what value is a report if two rows have the same
Name, or if two Infraction columns have the same name? Given the chance to
screw this up, some user will eventually do so. So, now you have two
indexes into which it must insert keys every time you add a new student,
instead of just one. Yet the argument for surrogate keys is that they're
"faster." Well, that's not strictly true in all respects, two of which I've
given above.

Whew! But this sometimes trips my trigger.

Tom Ellison

 

[jkearns]

Tom,

I agree that having natural keys for the Infraction Table will work.
It's just so ingrained in my head to have a unique key for everything
and that names may sometimes be the same. In this case though, the
names can't really be the same (or it would be the same infraction).

I need a StudentID field as many students at this school have the same
name!

Crosstab query. That's what I'm looking for - thanks. I appreciate
all your help.

JK

 

[Tom Ellison]

Dear JK:

Having a natural key for the Infraction Table is NOT an alternative that
should be used to avoid having a unique key. As I would design a database,
the InfractionDescription should be unique.

For example, when choosing which infraction to apply to a new infraction
row, you would typically have a combo box listing all the acceptable values
for infractions. Would you want this list to show the same description more
than once? If you did, then a user would be unable to choose between them
in any consistent and expected manner. Isn't it the user's intention when
they enter the data to make choices that are unambiguous? Yet, if you have
both a surrogate ID key and a duplicated description, how will they know how
two choices differ? Will you show them the ID value as well? If so, do you
train them how the two different ID values for the same description actually
differ, so then can make an intelligent, and hopefully consistent choice?
Putting in a set of Descriptions that are enforced unique, and thus at least
potentially unambiguous, and whose descriptions actually fully,
unambiguously, and usefully describe each row as being different from all
others, that's a good thing. Indeed, unless you expect the users to see and
use the ID values to discriminate between infractions that have identical
descriptions, you must have, and should enforce uniqueness in the
description.

It has been my observation that many programmers use the ID to make every
row unique and neglect the necessity of having the natural key be unique as
well. The argument against natural keys is sometimes that they get so big.
In terms of disk storage, this is spurious, since a unique index on the
natural key is usually essential anyway. When this is realized, it becomes
apparent that the addition of the surrogate key is the thing that is extra,
and uses space in the database both for the column and for the index.

Now, I'd like to touch on the "same name" problem. Saying that you may have
students with the same name does not destroy the concept of using a natural
key, but it does complicate it a bit. Do you have any two students with the
same parents who have the same name. I'm guessing not. This suggests a two
part unique key.

One of the reasons for creating all the unique natural keys is to prevent
duplicate data entry. This is especially important in multi-user
environments (even if there is only one computer, you can have multiple
users!)

What you want to do is to prevent the Student table from having any student
in it more than once. If you have an unique index (which could mean having
the natural key be the PK, or not) on ParentsName/StudentName it will
probably prevent anyone from accidentally putting a student in twice. If a
student is in twice (expecially if the relationships are by ID "surrogate"
key) then some of his infraction rows will probably come out under one of
the two IDs and the other infractions will come out under the other ID. If
you keep a unique index by ParentName/StudentName you would probably catch
this before the second instance of a Student table row is created, thus
avoiding a type of data corruption that can be difficult to detect and
remedy.

So, having a surrogate key makes the mechanics of the database very sound,
but having a unique natural key index is the first step toward actually
having integrity in the data itself! Integrity in data isn't just a
mechanical issue, it's an issue of accurately modeling the real world.
There are corruptions that cannot be found with a program, but which can be
prevented (to a large extent) with your programming.

With respect to your specific query, will it be enough to report the ID of
the students? Would it not help someone reading this on a report, for
example, to at least also see the student's name? Or does the
administration of the school think of their students as numbers?

Finally (whew!) I'm glad to hear that the crosstab hint I gave was
apparently the final clue you needed. Good luck!

Tom Ellison

 

[JK]

Tom,

I should have been more clear - yes, I got rid of the InfractionID and
now the Description as the PK and it is unique. There is a combo box
so that users can only select one of the six types of infractions when
entering/modifying data.

So, now I have two tables:

tblAttendance
StudentID (PK)
TodaysDate (PK)
Infraction (PK)

tblInfraction
InfractionDescription (PK)

The list of values in tblInfraction are:
Assembly - Late
Assembly - Absent
Morning - Late
Morning - Absent
Afternoon - Late
Afternoon - Absent

Now, I want to make a report that looks like:

Absentee Report for 15-December-2005
Student Assembly Morning Afternoon
Jane L
John A A A
Bob A L

First, I made a form to enter the date that the user wants the absentee
report from. I've called that form frmAttendanceDailyPop with a text
box called txtDate. Then I want to make a Crosstab query that gets all
my data. My query looks like:

PARAMETERS [Forms]![frmAttendanceDailyPopUp]![txtDate] DateTime;
TRANSFORM Count(tblAttendance.TodaysDate) AS CountOfTodaysDate
SELECT tblAttendance.StudentID, tblStudent.LastName,
tblStudent.FirstName
FROM tblStudent INNER JOIN tblAttendance ON tblStudent.StudentID =
tblAttendance.StudentID
WHERE
(((tblAttendance.TodaysDate)=[Forms]![frmAttendanceDailyPopUp]![txtDate]))
GROUP BY tblAttendance.StudentID, tblStudent.LastName,
tblStudent.FirstName
ORDER BY tblStudent.LastName, tblStudent.FirstName
PIVOT tblAttendance.Infraction;

I'm having problems making a report out of this though. I've put in the
following code to read whether or not the student is late or absent
(can't be both) to display the L or the A - the actual text boxes that
show the Count(TodaysDate) are not visible, and I made three new text
boxes called txtAssembly, txtMorning, and txtAfternoon.

If Not IsNull(Me("Assembly - Late")) Then
Me("txtAssembly") = "L"
ElseIf Not IsNull(Me("Assembly - Absent")) Then
Me("txtAssembly") = "A"
Else
Me("txtAssembly") = ""
End If

If Not IsNull(Me("Morning - Late")) Then
Me("txtMorning") = "L"
ElseIf Not IsNull(Me("Morning - Absent")) Then
Me("txtMorning") = "A"
Else
Me("txtMorning") = ""
End If

If Not IsNull(Me("Afternoon - Late")) Then
Me("txtAfternoon") = "L"
ElseIf Not IsNull(Me("Afternoon - Absent")) Then
Me("txtAfternoon") = "A"
Else
Me("txtAfternoon") = ""
End If

If on a given day, there was no one Late to Assembly, then the report
won't display properly (I get a "The Microsoft Jet Database Engine does
not recognize " as a valid field name or expression"). I think this is
because one/many of the fields contain no values for all the students
(and thus would not appear in the crosstab query for that day).

Please let me know if you have any suggestions on how to fix this (or
do it using a better method.

Thanks!
JK

 

[Tom Ellison]

Dear JK:

For my own applications I don't usually have crosstabs, but I'm thinking the
thing you want is in the TRANSFORM portion. Your query is giving you a
COUNT() here. It's always, 1, right? How about if it were to give you the
MAX value of Infraction instead (or, as I'd probably do, FIRST()).

There's actually only one value there, so either MAX, MIN, FIRST, or LAST
can only give you that value. And the value will be the
InfractionDescription (if I've got this figured correctly). You could
easily process that into your L or A, especially if you add a column to
tblInfraction that could hold that L or A (and any letter assigned to
represent any new infraction added later).

Does that make sense? Does it work?

Tom Ellison

Tom,

I should have been more clear - yes, I got rid of the InfractionID and
now the Description as the PK and it is unique. There is a combo box
so that users can only select one of the six types of infractions when
entering/modifying data.

So, now I have two tables:

tblAttendance
StudentID (PK)
TodaysDate (PK)
Infraction (PK)

tblInfraction
InfractionDescription (PK)

The list of values in tblInfraction are:
Assembly - Late
Assembly - Absent
Morning - Late
Morning - Absent
Afternoon - Late
Afternoon - Absent

Now, I want to make a report that looks like:

Absentee Report for 15-December-2005
Student Assembly Morning Afternoon
Jane L
John A A A
Bob A L

First, I made a form to enter the date that the user wants the absentee
report from. I've called that form frmAttendanceDailyPop with a text
box called txtDate. Then I want to make a Crosstab query that gets all
my data. My query looks like:

PARAMETERS [Forms]![frmAttendanceDailyPopUp]![txtDate] DateTime;
TRANSFORM Count(tblAttendance.TodaysDate) AS CountOfTodaysDate
SELECT tblAttendance.StudentID, tblStudent.LastName,
tblStudent.FirstName
FROM tblStudent INNER JOIN tblAttendance ON tblStudent.StudentID =
tblAttendance.StudentID
WHERE
(((tblAttendance.TodaysDate)=[Forms]![frmAttendanceDailyPopUp]![txtDate]))
GROUP BY tblAttendance.StudentID, tblStudent.LastName,
tblStudent.FirstName
ORDER BY tblStudent.LastName, tblStudent.FirstName
PIVOT tblAttendance.Infraction;

I'm having problems making a report out of this though. I've put in the
following code to read whether or not the student is late or absent
(can't be both) to display the L or the A - the actual text boxes that
show the Count(TodaysDate) are not visible, and I made three new text
boxes called txtAssembly, txtMorning, and txtAfternoon.

If Not IsNull(Me("Assembly - Late")) Then
Me("txtAssembly") = "L"
ElseIf Not IsNull(Me("Assembly - Absent")) Then
Me("txtAssembly") = "A"
Else
Me("txtAssembly") = ""
End If

If Not IsNull(Me("Morning - Late")) Then
Me("txtMorning") = "L"
ElseIf Not IsNull(Me("Morning - Absent")) Then
Me("txtMorning") = "A"
Else
Me("txtMorning") = ""
End If

If Not IsNull(Me("Afternoon - Late")) Then
Me("txtAfternoon") = "L"
ElseIf Not IsNull(Me("Afternoon - Absent")) Then
Me("txtAfternoon") = "A"
Else
Me("txtAfternoon") = ""
End If

If on a given day, there was no one Late to Assembly, then the report
won't display properly (I get a "The Microsoft Jet Database Engine does
not recognize " as a valid field name or expression"). I think this is
because one/many of the fields contain no values for all the students
(and thus would not appear in the crosstab query for that day).

Please let me know if you have any suggestions on how to fix this (or
do it using a better method.

Thanks!
JK

 

[JK]

Tom,

I tried using First() and even changed tblInfraction to now have two
fields - Description and AbsentOrLate [contains an "A" or an "L"]. The
query now has:

TRANSFORM First(tblInfraction.AbsentOrLate) AS FirstOfAbsentOrLate

The query works well (and shows an A or an L where appropriate), but
I'm still running into the same problem with the report. On a day that
has no students that are absent and/or late for assembly, morning,
and/or afternoon, there is a bound text box in the report with no field
in the query.

You mentioned that you do not usually use crosstabs in your
applications. How do you get multi-column information in a report from
a table/query of values then? Any suggestions would be appreciated.

Thanks!
JK

 

[JK]

Tom,

I figured it out. I made a report with 3 subreports in the detail
section that displays the "L" or "A" depending on the infraction. The
subreports are bound to the StudentID, TodaysDate, and to the text
boxes Assembly, Morning, and Afternoon.

Thanks for all your help through this.

JK

 

[Tom Ellison]

Dear JK:

About your report problem. When there's no data, what do you want? No data
is a situation which has an event doesn't it? Or a condition you can test?
Since there's really no report when this happens, I cancel the report and
give the user a message box instead. Would that be helpful? I can look up
the details of how I've done this if that's what you want.

As for how I do it, you'll be sorry you asked! I don't use Access with Jet
but with MSDE. This ships right along with Access and is the native
database of Access if you write a project (ADP) instead of the MDB. It's a
significant shift, however, not to be undertaken lightly.

The effect that is done easily using a crosstab is produced using a
completely different technique. By the way, the crosstab is a completely
non-standard and I expect non-portable feature of Access. That's not
necessarily a strong argument for not using crosstabs - they're pretty
useful if you haven't already acquired some rather expert query skills. The
alternative is to generate the SQL with a list of sub-queries for the
columns you want to generate. This can be done by first running a query
that gives you the column headings / column names to query in each
sub-query. If you know what these things are, and how to handle them, it
can take less than a half hour to put one together. Anyway, if your working
outside Jet, you don't have a choice.

This approach has some advantages. You may already know you can have more
than one column of row headings for the crosstab, kind of a hierarchy from
more general to more specific information. Well, you could do the same
thing with column headings: have a hierarchy of two or more, with
sub-headings for columns just like for rows. If you think about it, you've
probably seen this kind of table in print. You can also generate multiple
"cells" at each "intersection."

Imagine a printed report where the top line of column headings had the names
of the 50 states (not all on one page) and under each state, the name of 5
to ten cities in that state. The rows might be population in different
census, broken out by race, with both the total population of that city in
that census for that race, and the % of that race in the total population of
that city. Now you have a two tier row heading, census year and race, and a
two tier column heading of State and City. And, at each intersection, you
have population and percent. There are several things about this you cannot
do with the crosstab. But you can do this readily generating the SQL as
I've described.

The point, then, is that crosstab is very useful, but it is limited in
capacity and restricted to Jet (not portable).

I'm glad to explain this, but don't think you'll need this for what you're
doing now (I would not have been shy to recommend it if it were necessary in
the first place). It's fun to discuss it, however.

Is the idea of aborting the report and just showing a message box to the
user to explain that sufficient? Please look at online help for "no data"
first, then come back here if you need more help.

Tom Ellison

Tom,

I tried using First() and even changed tblInfraction to now have two
fields - Description and AbsentOrLate [contains an "A" or an "L"]. The
query now has:

TRANSFORM First(tblInfraction.AbsentOrLate) AS FirstOfAbsentOrLate

The query works well (and shows an A or an L where appropriate), but
I'm still running into the same problem with the report. On a day that
has no students that are absent and/or late for assembly, morning,
and/or afternoon, there is a bound text box in the report with no field
in the query.

You mentioned that you do not usually use crosstabs in your
applications. How do you get multi-column information in a report from
a table/query of values then? Any suggestions would be appreciated.

Thanks!
JK