Expression in Query

[randolphoralph]

I have three fields

Correct Name-contains "Yes", "No", or "N/A"
Correct Color-contains "Yes", "No", or "N/A"
Correct Request-contains "Yes", "No", or "N/A"

I am trying to create a query that will do a count of each time that "No" is displayed in one of the three fields.

 

[redneckgeek]

You'll need to use a UNION for that:

SELECT SUM(CNT) AS TOTAL
FROM (
Select Count(SomeField) as cnt
From YourTable
Where [CORRECT NAME] = "NO"
UNION ALL
Select Count(SomeField) as cnt
From YourTable
Where [CORRECT COLOR] = "NO"
UNION ALL
Select Count(SomeField) as cnt
From YourTable
Where [CORRECT REQUEST] = "NO") RS

I usually use whatever field is the primarykey for the table as "SomeField"
but I don't think it really matters.

 

[randolphoralph]

This did work great to sum all of the three together. I am looking to make the 3 fields sum into 3 seperate fields..... I apologize I should have put that in the first post.

Is it possible to have the query run without pulling up a prompt box and asking for "SomeField"?

 

[redneckgeek]

This did work great to sum all of the three together. I was looking to make this sum into 3 seperate fields....Correct Name, Correct Color, and Correct Request. I apologize for not mentioning that earlier. Thanks

There's probably a way to do that with one of the domain aggregate (DSUM, ... ) function, but in 15 years of developing I've never used one of those, so I'm not sure. Here's my way of doing it:

SELECT SUM(CNT1) AS NAME, SUM(CNT2) AS COLOR, SUM(CNT3) AS REQUEST FROM (
Select Count(SomeField) as cnt1, 0 as cnt2, 0 as cnt3
From YourTable
Where [CORRECT NAME] = "NO"
UNION ALL
Select 0 as cnt1, Count(SomeField) as cnt2, 0 as cnt3
From YourTable
Where [CORRECT COLOR] = "NO"
UNION ALL
Select 0 as cnt1, 0 as cnt2, Count(SomeField) as cnt3
From YourTable
Where [CORRECT REQUEST] = "NO") RS

 

[randolphoralph]

I inputted this and changed the field names and added my table name and picked a a field name from my table for the "SomeField"

When I did ran the query I got a error message stating "Invalid bracketing of the name 'Select Count(First Name) as cnt1, 0 as cnt2, 0 as cnt3
From Note Table
Where[Fax NOTE'."

SELECT SUM(CNT1) AS FAX, SUM(CNT2) AS INTERNET, SUM(CNT3) AS PROVIDER
FROM [Select Count(First Name) as cnt1, 0 as cnt2, 0 as cnt3
From Note Table
Where [FAX NOTE] = "No"
UNION ALL
Select 0 as cnt1, Count(First Name) as cnt2, 0 as cnt3
From Note Table
Where [INTERNET NOTE] = "No"
UNION ALL Select 0 as cnt1, 0 as cnt2, Count(First Name) as cnt3
From Note Table
Where [PROVIDER NOTE] = "No"]. AS RS;

 

[redneckgeek]

Another irritating Access feature. It inserts brackets [] around subqueries. Paste it back into SQL view like this, and don't go back to the QBE view again.

SELECT SUM(CNT1) AS FAX, SUM(CNT2) AS INTERNET, SUM(CNT3) AS PROVIDER
FROM (Select Count(First Name) as cnt1, 0 as cnt2, 0 as cnt3
From Note Table
Where [FAX NOTE] = "No"
UNION ALL
Select 0 as cnt1, Count(First Name) as cnt2, 0 as cnt3
From Note Table
Where [INTERNET NOTE] = "No"
UNION ALL Select 0 as cnt1, 0 as cnt2, Count(First Name) as cnt3
From Note Table
Where [PROVIDER NOTE] = "No") RS;

 

[Brianwarnock]

I would have used the Sum(if.. approach

TotalN0CorrectName:Sum(If([Correct Name]="no",1,0))
Total etc

Just 1 simple query with 3 fields

Brian

 

[redneckgeek]

I would have used the Sum(if.. approach

TotalN0CorrectName:Sum(If([Correct Name]="no",1,0))
Total etc

Just 1 simple query with 3 fields

Brian

Sure... Take the easy way out

By the time I came up with that screwy answer, I was on my third IPA -
I'm pleading temporary insanity

 

[Brianwarnock]

Sure... Take the easy way out

By the time I came up with that screwy answer, I was on my third IPA -
I'm pleading temporary insanity

ROFL

Is that how you came up with that answer on the consecutive query that gave me a migraine before I'd decoded it?

Brian

 

[redneckgeek]

No... That answer required good scotch!

 

[Brianwarnock]

No... That answer required good scotch!

I'm drinking Macallan's Elegancia at the moment , well not at this minute, can't buy it in this country had to get it at Dallas airport, perhaps I should have had a tot before the decode, hope the op worked it out.

Brian

 

[randolphoralph]

I pasted in

SELECT SUM(CNT1) AS FAX, SUM(CNT2) AS INTERNET, SUM(CNT3) AS PROVIDER
FROM (Select Count(First Name) as cnt1, 0 as cnt2, 0 as cnt3
From Note Table
Where [FAX NOTE] = "No"
UNION ALL
Select 0 as cnt1, Count(First Name) as cnt2, 0 as cnt3
From Note Table
Where [INTERNET NOTE] = "No"
UNION ALL Select 0 as cnt1, 0 as cnt2, Count(First Name) as cnt3
From Note Table
Where [PROVIDER NOTE] = "No") RS;

And I received the error message stating Syntax error (missing operator) in the query expression "Count(First Name)'

How would I go about doing the Sum If approach?

I think after all this is done I am going to be drinking with ya'll

 

[Brianwarnock]

Use the technique I posted in Post 7

Brian

 

[randolphoralph]

So I post this in the SQL View?

Do I just paste this in the SQL View?
TotalN0CorrectName:Sum(If([Correct Name]="no",1,0))

Or do I add the Select and From?

I do apologize for my ignorance I am new to using SQL.

 

[Brianwarnock]

Use the query design grid and that will be coded in a field, I always start with the grid it helps avoid typos and syntax problems, you can always look at and modify the SQL later.
In the SQL it will appear as
Sum(If([Correct Name]="no",1,0)) As TotalN0CorrectName in the Select statement and you will still have the From

Brian

 

[redneckgeek]

For future reference: Don't use spaces in table names and field names, it forces you to enclose them in []. Like "FROM [NOTE TABLE]"

 

[randolphoralph]

Going forward I will not include any spaces in the table names or field names. That is good to know.

So I pull up a new query and did not select any fields...I then selected SQL View and input the folowing

SELECT Sum(If([Correct Name]="no",1,0)) AS TotalN0CorrectName
FROM [Note Table];

I saved and closed it. Then I tried to run the query and the message was "Undefined funtion 'If' in expression"

 

[Brianwarnock]

Sorry should be IIF I've been coding in Excel

BTW you can do all 3 fields at once and if you look back at design view you will see how easy it is to do it all in the grid.

Brian

 

[redneckgeek]

Sorry should be IIF I've been coding in Excel

Brian

Is Excel a brand of Whisky?

 

[Brianwarnock]

Is Excel a brand of Whisky?

LOL
guess I asked for that after recent posts.

Brian