## Querying Responses

### Problem:

Having stored our checkbox data as integers how does one

query the table to find the corresponding bits which are set?

### Background:

Although the pervasiveness of TCP/IP and network masks has

made more people aware of the binary number system let’s review how to figure out which bits are

set. Given an integer how does one

calculate which particular bits are set.

A math teacher would give a better explanation on the terminology behind

the following process but the following is a process one can use.

### The Process:

The following is a list of bits. As I mentioned in the previous article each bit corresponds to a given checkbox.

```
Checkbox1 Bit 1 2^0 = 1
Checkbox2 Bit 2 2^1 = 2
Checkbox3 Bit 3 2^2 = 4
Checkbox4 Bit 4 2^3 = 8
Checkbox5 Bit 5 2^4 = 16
Checkbox6 Bit 6 2^5 = 32
Checkbox7 Bit 7 2^6 = 64
Checkbox8 Bit 8 2^7 = 128
```

In the last article Storing Responses the process of storing

checkbox data as integers was demonstrated.

Following the procedures from that article on may end up with an integer

value of 37 stored in the database.

Given 37 is a value in the database how would one go

backwards to figure out which bits this integer corresponds. The manual process uses the following

logic. Find the bit corresponding to a

value not larger than 37 but as close to 37 as possible. In this case the value would be 32 which

means bit 6 is set. Subtract 37 from

the value of the set bit. This will leave 5.

Now do the process again. The

value closest to 5 without going over is 4, this means bit 3 is set. Take away the 4 from 5 and you are left with

1 this means bit 1 is set. So to

represent 37 in binary format we need bits 6, 3 and 1 set.

Programming the above logic can be done. It is a long and tedious process. Another method would be to use the following

formula for each of the bits we need to test (Note: we will only need to test

the bit greater than the largest integer in the table). Below is the example for each bit followed

by the corresponding SQL statement.

## Expressions to Return Each Bit

`IntValue`

: is the integer value

`Mod`

: is an Access function of the

form X MOD Y where returns the remainder when X is divided by Y. Transact-SQL has a similar using `%`

`Int(X)`

: returns the integer portion of the value X

```
Bit1: (Int([intValue]/2^0) Mod 2)
Bit2: (Int([intValue]/2^1) Mod 2)
Bit3: (Int([intValue]/2^2) Mod 2)
Bit4: (Int([intValue]/2^3) Mod 2)
Bit5: (Int([intValue]/2^4) Mod 2)
Bit6: (Int([intValue]/2^5) Mod 2)
Bit7: (Int([intValue]/2^6) Mod 2)
Bit8: (Int([intValue]/2^7) Mod 2)
```

Because both Access and SQL can be used to query a SQL

database and both are used in my current working environment I have included

the SQL statements in using the syntax for each application.

### SQL Statement (Access):

```
SELECT (Int([intValue]/128) Mod 2) AS Bit8,
(Int([intValue]/64) Mod 2) AS Bit7,
(Int([intValue]/32) Mod 2) AS Bit6,
(Int([intValue]/16) Mod 2) AS Bit5,
(Int([intValue]/8) Mod 2) AS Bit4,
(Int([intValue]/4) Mod 2) AS Bit3,
(Int([intValue]/2) Mod 2) AS Bit2,
(Int([intValue]/1) Mod 2) AS Bit1,
tblinttest. IntValue as IntegerValue
FROM tblinttest
```

To specify a specific bit add a `where`

clause using:

```
WHERE ((((Int([inttest]/128) Mod 2))=1)
AND (((Int([inttest]/64) Mod 2))=1));
```

### SQL Statement (Transact-SQL):

```
SELECT (convert(int,([inttest]/128)) % 2) AS Bit8,
(convert(int,([inttest]/64)) % 2) AS Bit7,
(convert(int,([inttest]/32)) % 2) AS Bit6,
(convert(int,([inttest]/16)) % 2) AS Bit5,
(convert(int,([inttest]/8)) % 2) AS Bit4,
(convert(int,([inttest]/4)) % 2) AS Bit3,
(convert(int,([inttest]/2)) % 2) AS Bit2,
(convert(int,([inttest]/1)) % 2) AS Bit1,
tblinttest.Inttest as IntegerValue
FROM tblinttest
```

To specify a specific bit add a `where`

clause using:

```
WHERE (((convert(int,([inttest]/128)) % 2)=1)
AND ((convert(int,([inttest]/64)) % 2)=1));
```

It is now possible to write a query to return counts and

other information from the survey data mentioned in the first article. The next

article will discuss performance and other miscellaneous aspects of this table

design method.