Couunting Trues

[Messacar]

As you’ll see I’m a still real novice at this, but here goes:



I’ve developed an Access DB for our magazine collections numbering around 20,000 copies (strictly a guesstimate since there’s been no real method of counting them).

A sample of the query follows:

What shows here as true/false is a tick mark (true) is a copy that we have. Is there any way that I can count the number in our possession?

Any and all help appreciated.
Fred

Issue Key	Magazine Title	Alternate Title 1	Issue Year	Volume Numbers	January	February	March	April	May	June	July	August	September	October	November	December	Notes-1	Full Size or Model	Delete Record
144	Model Aviation (USA)		1936		False	False	False	False	False	True	True	True	False	False	False	False		M	False
144	Model Aviation (USA)		1942		True	True	True	True	True	True	True	True	True	True	True	True		M	False
144	Model Aviation (USA)		1943		True	False	False	False	False	False	False	False	False	False	False	False		M	False
144	Model Aviation (USA)		1953		False	False	True	False	False	True	False	False	False	False	False	False		M	False
144	Model Aviation (USA)		1955		False	True	False	True	False	False	True	False	False	True	True	True		M	False
144	Model Aviation (USA)		1956		False	False	False	True	False	True	True	False	False	True	False	False		M	False
144	Model Aviation (USA)		1957		True	True	True	True	True	True	True	True	False	True	True	True		M	False

[davegri]

Maybe add a column to the query:
IssueCount: Sum(January, February, March, April, May, June, July, August, September,October, November, December) * -1


since TRUE is minus one (-1) you need to multiply the sum of the months by minus one to get a positive result.

[June7]

True = -1 and False = 0.

Do you want to sum records as well as month fields? Should post your query SQL.

SELECT *, (January, February, March, April, May, June, July, August, September, October, November, December) * -1 AS TotalMonths FROM table;

or

SELECT Sum(January, February, March, April, May, June, July, August, September, October, November, December) * -1 AS TotalMags FROM table;

[xps35]

Why do you store copies that are missing for years you have a copy only?

I think you should only store copies you own in 2 tables. One tabel being Magazine the second one Copy (MagazineID, Year, Month). Counting is a piece of cake then.

[Micron]

I suspect the table(s) look just like that query, in which case the db would not be normalized. That alone makes counting anything more difficult.

[jojowhite]

SELECT 
    [Issue Key], 
    [Magazine Title], 
    Sum((January, February, March, April, May, June, July, August, September, October, November, December) * -1) As TotalIssued 
FROM YourTableName 
GROUP BY 
    [Issue Key], 
    [Magazine Title];

[Gustav]

Run this query:

SELECT 
    [Issue Key], [Magazine Title], [Issue Year],
    Abs(Sum([January]+[February]+[March]+[April]+[May]+[June]+[July]+[August]+[September]+[October]+[November]+[December])) AS Issues
FROM 
    Magazines
GROUP BY 
    [Issue Key], [Magazine Title], [Issue Year]

Result: