Counting Data in a related table

[Chefjay22]

Hello all. I always get such awesome help from you guys… so thank you in
advanced for any help!

Here is my problem. I have a set of data in a flat table that is of course
related data. I have been tasked with grouping information and counting
types of records. So here is what I need to do:

I have a table called tblMessages. There are four fields… from left to
right the fields are named Subject, Category, SubCategory, and Message. What
I need to do is count the number of subjects, then the number of Categories
in that subject, the number of Subcategories for this subject, then the
number of messages per subject. Basically my query needs to display the
subject name and counts for each other field described above. I can get the
1st part to work, but by the time it gets to subcategory I am getting bad
data. Is this good from a cross-tab? I was never any good at them lol..
Any help would be appreciated! Thanks!

 

[KARL DEWEY]

Is this what you want ---
SELECT tblMessages.Subject, Count(tblMessages.Subject) AS CountOfSubject,
tblMessages.Category, Count(tblMessages.Category) AS CountOfCategory,
tblMessages.SubCategory, Count(tblMessages.SubCategory) AS
CountOfSubCategory, Count(tblMessages.Message) AS CountOfMessage
FROM tblMessages
GROUP BY tblMessages.Subject, tblMessages.Category, tblMessages.SubCategory;

 

[Gary Walter]

Here is my problem. I have a set of data in a flat table that is of
course
related data. I have been tasked with grouping information and counting
types of records. So here is what I need to do:

I have a table called tblMessages. There are four fields. from left to
right the fields are named Subject, Category, SubCategory, and Message.
What
I need to do is count the number of subjects, then the number of
Categories
in that subject, the number of Subcategories for this subject, then the
number of messages per subject. Basically my query needs to display the
subject name and counts for each other field described above. I can get
the
1st part to work, but by the time it gets to subcategory I am getting bad
data. Is this good from a cross-tab? I was never any good at them lol..
Any help would be appreciated! Thanks!

I could be mistaken but it sounds like you
are asking for "DISTINCT" counts?

i.e., qryDistinctSubjectCategory

SELECT DISTINCT
Subject,
Category
FROM
tblMessages;

so, one way to get distinct count of Category's for each Subject
(if [Subject] is type TEXT)

SELECT
t.Subject,
Count(t.Subject) as SubjectCnt,
DCount("Category","qryDistinctSubjectCategory",
"[Subject]='" & t.Subject & "'") As DistCatCnt
FROM
tblMessages AS t
GROUP BY
t.Subject;

if guessed correctly, this could be expanded for SubCategory, etc.

qryDistinctSubjectSubCategory

SELECT DISTINCT
Subject,
SubCategory
FROM
tblMessages;

SELECT
t.Subject,
Count(t.Subject) as SubjectCnt,
DCount("Category","qryDistinctSubjectCategory",
"[Subject]='" & t.Subject & "'") As DistCatCnt,
DCount("SubCategory","qryDistinctSubjectSubCategory",
"[Subject]='" & t.Subject & "'") As DistSubCatCnt
FROM
tblMessages AS t
GROUP BY
t.Subject;

it sounds like you may not want "distinct" Messages count,
but just number of non-null Messages for each Subject?

SELECT
t.Subject,
Count(t.Subject) as SubjectCnt,
DCount("Category","qryDistinctSubjectCategory",
"[Subject]='" & t.Subject & "'") As DistCatCnt,
DCount("SubCategory","qryDistinctSubjectSubCategory",
"[Subject]='" & t.Subject & "'") As DistSubCatCnt,
DCount("Messages","tblMessages",
"[Subject]='" & t.Subject & "'") As MsgCnt
FROM
tblMessages AS t
GROUP BY
t.Subject;

That may be one way...

Good Luck,

gary