Count

[cyraxote]

Count (grrr...)

Hi, all.

I don't know why this is complicated, but...

I'm trying to count all records for which the value of a certain field is true (the field is a True/False data type).

What I get is the total number of records, not just the ones with the box checked.

I've tried all sorts of Count variations to no avail.

e.g.
SELECT Count(MOL.Hand) AS CountOfHand
FROM MOL
HAVING (((Count(MOL.Hand))=True));

Any wisdom?

Thanks.

Rodney

 

[WayneRyan]

Rodney,

Count doesn't return True/False:

SELECT Count(MOL.Hand) AS CountOfHand
FROM MOL
HAVING Count(MOL.Hand)) > 1;

Wayne

 

[cyraxote]

Wayne:

Thanks, but that gets me the same result, a count of the total number of records.

As I said, dunno why this is complicated.

Rodney

 

[RayH]

try this:

SELECT Count(MOL.Hand) AS CountOfHand
FROM MOL
HAVING MOL.Hand=True;

 

[cyraxote]

That's it!

Thanks very much.

Rodney

 

[WayneRyan]

Rodney,

Wait!!!

I'm confused now! With the True conditional, you only wanted
to return the records that "had records". They all do!

What did we just accomplish?

Wayne

 

[cyraxote]

>Sigh<

Now, when I tried to duplicate that query within a larger query, I get an error about not using an aggregate function or some such.

 

[cyraxote]

This is the SQL

SELECT DISTINCTROW Dockets.Month, Dockets.Year, Sum([MOL_Master]![EndPage]-[MOL_Master]![StartPage]+1) AS TotalPages, Count(MOL_Master.[2nd disk?]) AS CountOf2nd

FROM (MOL_Master INNER JOIN Dockets ON MOL_Master.DocketNumber = Dockets.DocketNumber) INNER JOIN Schedule_Master ON Dockets.DocketNumber = Schedule_Master.DocketNumber

GROUP BY Dockets.Month, Dockets.Year

HAVING (((Dockets.Year)=2004) AND ((MOL_Master.[2nd disk?])=True))

ORDER BY Dockets.Month, Dockets.Year;

 

[cyraxote]

Wayne:

I'm confused about your confusion.

My DB has a True/False field called "Hand". Let's say there are 100 total records. I want to count the number of records for which "Hand" is True (i.e., has a check). My original attempts all led to a result of 100 (instead of 9, which is what I was looking for).

Does that help?

Rodney

 

[WayneRyan]

Rodney,

Select *
From YourTable
Where Hand = True;

or

Select Count(*)
From YourTable
Where Hand Is Not Null;

Wayne

 

[cyraxote]

Wayne:

Thanks.

My larger problem is getting multiple things to work together. My DB contains information about issues of a journal; each record is an article. There is a field called "Dockets"; all of the articles in, for example, the Jan issue have the same Docket number. The DB contains all the info about a particular article, from authors and title and production in/out dates to number of pages and number of illustrations, etc.

I'm trying to create a monthly issue report with the following information: no of articles (Count), no of total pages in the issue (Sum), no of articles for which we required a 2nd disk from the author, no of articles that were edited by hand (my original post), and completion date for the entire issue. All of this is in the DB, and I need to create a query to feed the report.

Unfortunately, my involvement and experience with Access is very sporadic and episodic, so I have immerse myself in a task and solve it. When I return to another problem a few months later, I've forgotten most of what I learned.

Anyway, if you any golden path, I would appreciate a heads up. Access doesn't seem to want multiple counts in the same query.

Thanks.

ROdney

 

[Jon K]

Do the counting of Hand with: abs(sum([MOL_Master].[Hand])) AS CountOfHand
and move: (((Dockets.Year)=2004) AND ((MOL_Master.[2nd disk?])=True))
to the Where Clause:-

SELECT DISTINCTROW Dockets.Month, Dockets.Year,
Sum([MOL_Master]![EndPage]-[MOL_Master]![StartPage]+1) AS TotalPages,
Count(MOL_Master.[2nd disk?]) AS CountOf2nd,
abs(sum([MOL_Master].[Hand])) AS CountOfHand
FROM (MOL_Master INNER JOIN Dockets ON MOL_Master.DocketNumber = Dockets.DocketNumber) INNER JOIN Schedule_Master ON Dockets.DocketNumber = Schedule_Master.DocketNumber
WHERE (((Dockets.Year)=2004) AND ((MOL_Master.[2nd disk?])=True))
GROUP BY Dockets.Month, Dockets.Year
ORDER BY Dockets.Month, Dockets.Year;

 

[cyraxote]

Well, that solves one problem but raises multiple others. It also produces incorrect data. For example, the Jan 2004 issue contains only 1 record of 30 in which the "2nd Disk" is checked. However, this query reports that there are for for January (and for every other month, for that matter) and counts only the pages of these.

I guess I'm a liitle confused about how to accomplish this.

Thanks to all.

 

[Jon K]

Probably I have misunderstood the intention of your original SQL statement. I thought the (((Dockets.Year)=2004) AND ((MOL_Master.[2nd disk?])=True)) was for the selection of records to be included in the query so I moved it to the Where Clause.

In a Totals query, Having applies to the groups whereas Where applies to the records. Since your query hadn't grouped by MOL_Master.[2nd disk?], you couldn't put ((MOL_Master.[2nd disk?])=True) in the Having Clause.