Counting Records

  • Thread starter Thread starter qaz
  • Start date Start date
Q

qaz

I have three tables which are all related by key: Category >> Subject >>
Detail.

The keys are related as follows:
Category.IDCat >> Subject.IDCat
Subject.IDSubj >> Detail.IDSubj

(all relationships are one to many)

I want to return a single query (I don't want to have to run multiple
queries) which will return all data grouped by Category & Subject AND I want
to return a group count for each subject (I want to return the number of
"detail" records within each subject. So, if the Category is Plant and the
Subject is Orchid and there are 7 detail records on different Orchids, I
want to return a recordset which contains

Category: Plant
Subject: Orchid
SubjCount: 7
Detail: [7 Detail Records]

etc., etc. for other categories & subjects.

I am having a heck of a time creating a query that will do this, though I
suspect it is rather straight forward. Could someone provide me a simple (I
hope) "template" query to solve this problem?

Thanks.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Of course you know that queries return results formatted like this:

Category Subject SubjCount Detail
-----------------------------------------
Plant Orchid 1 detail line 1
Plant Orchid 1 detail line 2
Plant Orchid 1 detail line 3
.... etc. ...

Not as you've formatted your desired resultset. Unless you're using ADO
"MSDataShape" OLE provider, or printing a report & using the TextBox's
Hide Duplicate property.

I suppose you've tried something like this:

SELECT C.Category, S.Subject, Count(*) As SubjCount, D.Detail
FROM Category As C INNER JOIN (Subject As S INNER JOIN Detail As D
ON S.IDSubj = D.IDSubj) ON C.IDCat = S.IDCat
WHERE C.CategoryName = 'Plant'
AND S.SubjectName = 'Orchid'
GROUP BY Category, Subject, Detail

This will produce my first example result set.

This will produce a detail count w/o the detail lines:

SELECT C.Category, S.Subject, Count(D.*) As DetailCount
FROM Category As C INNER JOIN (Subject As S INNER JOIN Detail As D
ON S.IDSubj = D.IDSubj) ON C.IDCat = S.IDCat
WHERE C.CategoryName = 'Plant'
AND S.SubjectName = 'Orchid'
GROUP BY Category, Subject

Note that I changed the SubjCount to DetailCount, since that seems to be
what you want - a count of the detail rows associated w/ the indicated
Category & Subject.

Perhaps, your best bet is to use a Report & the Hide Duplicates
property. Group the report by SubjectName & include a TextBox w/
=Count(*) in the group footer to get the count of Detail lines.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQe69XoechKqOuFEgEQI0pwCcCGO2WDxDZqorrHO60B/ZpHPxxOwAoL04
pUfzTnaKQpEuY/NGyP4ZZ2xM
=wNes
-----END PGP SIGNATURE-----

I have three tables which are all related by key: Category >> Subject >>
Detail.

The keys are related as follows:
Category.IDCat >> Subject.IDCat
Subject.IDSubj >> Detail.IDSubj

(all relationships are one to many)

I want to return a single query (I don't want to have to run multiple
queries) which will return all data grouped by Category & Subject AND I want
to return a group count for each subject (I want to return the number of
"detail" records within each subject. So, if the Category is Plant and the
Subject is Orchid and there are 7 detail records on different Orchids, I
want to return a recordset which contains

Category: Plant
Subject: Orchid
SubjCount: 7
Detail: [7 Detail Records]

etc., etc. for other categories & subjects.

I am having a heck of a time creating a query that will do this, though I
suspect it is rather straight forward. Could someone provide me a simple (I
hope) "template" query to solve this problem?
Click to expand...
 
Back
Top