SQL Query - **HAS** to return "0" for a "No Record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL Query - **HAS** to return "0" for a "No Record

Author

Topic

essaar
Starting Member

1 Post

Posted - 2002-06-22 : 04:08:35

A little bit of BG.
Database in Access2K - Application is some sort of a Psychometry Test.

There is a table with Five fields, "A", "B", "C", "D", "QType".
Values for "A", "B", "C" & "D" can be numbers between 1 and 4.
Values for "QType" are either "L", "M", "N", "O" or "P" for each record.

How do I (with a Query):
Create a result summing up all the values greater than 3 in fields "A", "B", "C" & "D", Group By "QType"
AND
Get a numerical "0" value if there are no records greater than 3 for say, field "B", "QType" = "M"?

I am currently doing all the above at the client-end (VB application). Can it be done using a Query?

Hope I am clear. Would be grateful for any and all assistance.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-06-23 : 19:29:33

Hi essaar

I'm not sure if I'm understanding completely - you want to sum for each field for each Qtype, but only values > 3, and then if there are none you want to show 0 - is that correct?

If i've understood then you want
SELECT Sum(IIf([A]>2,[A],0)) AS SumofA, Sum(IIf([B]>2,[B],0)) AS SumofB, Sum(IIf([C]>2,[C],0)) AS SumofC, Sum(IIf([D]>2,[D],0)) AS SumofD, Qtype
FROM tA
GROUP BY Qtype;

But I'm really not sure that I understood exactly what you wanted..let me know.

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-24 : 16:49:43

Access ain't my best suit here... but is there a case statement?

select case
when a <= b and a <= c and a <= d then sum(b,c,d)
when b <= a and b <= c and b <= d then sum(a,c,d)
when c <= a and c <= b and c <= d then sum(a,b,d)
when d <= a and d <= b and d <= c then sum(a,b,c)
when qtype = 'm' and B < 3 then '0'
end
as test result

Might hit errors if all 4 values are the same.

Not sure if case is in access...

-----------------------
Take my advice, I dare ya

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-06-24 : 17:05:56

You should be able to use the Switch statement to accomplish what M.E is showing you.

*************************
Someone done told you wrong!

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-06-24 : 21:32:43

No you can't use a switch or case statement inside an access Query.

IIF is the statement you will need to use...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources