how to check boolean in access

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

how to check boolean in access

Author

Topic

vux
Starting Member

45 Posts

Posted - 2004-07-27 : 00:18:21

I have a filed essential which is a boolean (bit on SQL Server 200 and Yes/No in access 2000)

I want to count how many entries are essential (checked) per year and how many not

what is the right syntax??? is it possible to fill both colums in one query?

SELECT Count(Overview.TravelID) AS EssentialTravels, Overview.FiscalYear
FROM Overview
GROUP BY Overview.FiscalYear, Overview.Essential
HAVING Overview.Essential= ????;

SELECT Count(Overview.TravelID) AS NotEssentialTravels, Overview.FiscalYear
FROM Overview
GROUP BY Overview.FiscalYear, Overview.Essential
HAVING Overview.Essential = NOT ????;

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-27 : 00:50:02

This should work in both cases:

SELECT Count(Overview.TravelID) AS EssentialTravels, Overview.FiscalYear
FROM Overview
WHERE Overview.Essential <> 0
GROUP BY Overview.FiscalYear, Overview.Essential

The problem I believe is that SQL sees it's TRUE's as 1 and Access sees them as -1 (although I may have these mixed up.)

TIP - do your filtering in the WHERE clause rather than the HAVING. It saves SQL from having to aggregate the whole table before it filters the records.

vux
Starting Member

45 Posts

Posted - 2004-07-27 : 01:00:25

hi,

thx for your answer.

at the moment all vealues for essential are NULL on the server, if I type in 1 (as default) they are still null and not true - how can I set the default value to true or false?

i would like to test

how will i get 2 colums (essential/non essential) grouped by year with your querry?

thanks for your help!

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-27 : 01:11:20

You'll need to alter your table design to add the defaults to the fields.

For your query, try the following:

SELECT FiscalYear,
Sum( IIF(Nz(Essential,0) = 0, 0, 1)) as NonEssential,
Sum( IIF(Nz(Essential,0) = 0, 1, 0)) as IsEssential
FROM Table1
GROUP BY FiscalYear

vux
Starting Member

45 Posts

Posted - 2004-07-27 : 03:45:11

hi again,

hm could you explain me the logic/syntax of this if statement, would be useful to know i think

also i have a date colum dd/mm/yy in overview

how do i only get the year, sth like

SELECT overview.getYear(departureDate)?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-27 : 13:30:28

The IIF statement acts like an in-line IF-Then. And the Nz() function is saying if Essential is NULL then use 0. So the IIF is saying in effect "if Essential is zero or null then return 0 else return 1". By using zero and 1 here, within the SUM statement, timmy is effectively giving you a count of rows. However it appears to me (and I might be wrong because I haven't had my coffee yet) that timmy has the lables NonEssential and IsEssential reversed.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-27 : 13:32:40

As for your date question, Access has a function called YEAR(). So you could say

SELECT YEAR(departureDate)
FROM OVERVIEW
WHERE ...

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

vux
Starting Member

45 Posts

Posted - 2004-07-28 : 05:01:37

hi guys,

thanks for the help.

somehow i just did it like that, palin:

SELECT [Overview].[Essential], Count([TravelID]) AS NbTravels,
FROM Overview
GROUP BY [Overview].[TravelYear], [Overview].[Essential];

actually now i see that this is more or less what timmy wrote in the first response - the where is not nesseccary, since it is either true or false

and thats it! it will give me the number of essential travels and beneath the number of non essential travels

did you know that that was all i wanted, maybe i asked to complicated...

still the IIF thing is interesting

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-29 : 16:50:26

Ah yes, that gives you the results in rows. The IIF() function allows you to transpose that to columns.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources