Aggregate functions

[garymilam]

Ok, so I have query with the following criteria: Total Classes: Count(IIf([tblDocuments]![Red]+[tblDocuments]![blue]+[tblDocuments]![green]=Yes,1,Null)). This query has some proprietary info in it so I can't show the real words so I used colors. but anyway, I want it to count the number of classes, except when there is a closed date for the class, I don't want it to count the closed class.

[NeoPa]

Do you realise your SQL there is checking for one, and only one, of those items to resolve to Yes?

Booleans used in arithmetic don't cause the arithmetic to be Boolean Arithmetic. IE. Each Boolean True resolves to -1 (In Jet/ACE - 1 in SQL Server) and Yes, is also True of course.

Thus, any more, or less, of those values that are True will make the result either less than -1 or 0. Compare that to Yes (-1) and it just won't match.

For an answer to your full question you'll need to include the full question, of course. It's perfectly acceptable to obscure names as you have done, but if you only post a fraction of the relevant details you may wait a while for help.

[PhilOfWalton]

I think it would be helpful to know what possible values one could expect from tblDocuments!Red.
for example between - 1000 and 5000
0 or -1 only
Between Aaron & Zebedee

I assume Blue & Green will give similar results

Phil

[garymilam]

let me see if I can say this without getting in trouble...LOL. I have 3 different document sets. Green documents, blue document, and red documents. I want the query to count each color and return a total. But if a document has a destruction date, I don't want the query to count it. All the documents are represented by a checkbox. So for example if I have a document and it is red, I check the red box, if it is blue I check the blue box, ect... but when I get rid of that document it has a destruction date. so I want to count each color document for a total color but if it has a destruction date don't count it..Thanks for all the help

[PhilOfWalton]

So let's just deal with one document set

DCount("Documents", "GreenDocuments", "DestructDate is Null")
 

This should count all the documents without a destruction date.

Then all you have to do is a similar thing for the other colours and add them up.

You must appreciate that unless we have the table details, or at least an accurate layout of the table, we can't guarantee the answer.

Something like

TblRedDocuments
    RedDocID               Autonumber
    RedDocName             Text
    RedDocDectructDate     Date
 

then we would be much better able to help

Phil

[garymilam]

Here is a sample of my DB.

[PhilOfWalton]

In your sample data, in Table 1, Document 4 has no colours selected, and document 5 has both blue & green.
Is this a typo?
What totals are you expecting for each of the three columns?

Phil

[garymilam]

yeah just typos, I want the total of each color in each column and then I will add a total column for all colors, but once again, if there is a destroyed date, don't count it.

[NeoPa]

You may be looking for something like :

Total Classes: -Sum(IIf([DocumentDestroyed] Is Null,[chkRed]+[chkBlue]+[chkGreen],0))

Bear in mind, this relies on the value of a Yes column equating to -1. It also relies on your having controls as named tied to the fields.

Aggregate functions (as used here) deal with values on the report or form.
Domain Aggregate functions work with a specified record source.

Your query picture doesn't give much away. If you want to share your query then the SQL is a much more reliable way to do so. Otherwise, a description of what you're doing is required, which is still missing. You give a couple of details but there are still gaps in the overall picture. Are you including all records in the results? Are they grouped in any way (If not then you'll only get one record in the result)?

PS. Please make sure you fix any typos before posting in future. It's a shame to waste so much of people's time for the simple want of a little care.

[PhilOfWalton]

I would add that if there are only 3 types of properties (red, green & blue) for your document, then your table structure is just about acceptable. To me it doesn't really look normalised and I would have a separate table of "Colours"

TblColours
    ColourID          Auto    PK
    Colour            Text    (No duplicates)
 

And in your Docoment table

TblDocuments
    DocID      Auto   PK
    DocName    Text
    ColourID   Long   FK   Required
 

That way there is no possibility of having anything but 1 "colour" per document.

Phil