Null Values

[toleafs]

Hello
Any help is appreciated
I have a query that has 60 columns of Yes/No repsonses. for example:

strInititative; Code1 ;Code2; Code3 ; and so to Code60
1 ; -1 ; 0 ; 0;

What I am trying to do is report on those Codes were its a Yes (-1).
Does this make sense? Is this possible?

thanks in advance for your help

 

[boblarson]

Well, your structure is not good for reporting. That is why we constantly tell you (and others) NORMALIZE YOUR DATABASE. Build the table structure properly to begin with and do not make it based on how you envision a form to work for input. You can always find a way to make a form work the way you want through code and such but setting up the database in such a non normalized way is going to make getting meaningful data back out a real pain in the backside.

So, your question doesn't quite make sense. Do you mean you want to get a count of yes' for each column or pull only records that have a yes, regardless as to how many? What is it you are really looking for? I fear it is going to be a real pain to get this for you though. Can you say a query with 59 OR statements in it?

 

[toleafs]

Bob
thanks soo much for your reply. Do you have any ideas that you can share?
My current table structure is as follows:

strName- text
strIndicator - text
strPICode - number (primary key)
strPIName- text
Code1 - yes/no
Code2- yes/no
and so on...
Code60 - yes/no

Each PICode can have multiple yes selections...
This table was created so that an easy user form can be created to capture all codes for each PICode....As you noticed.
This was not thought out! as you can see.... Any suggestion would be appreciated

 

[xenou]

I guess if you haven't normalized your table then you get something like this:

Select 
   -(
    [Code1] + [Code2] + [Code3] + [Code4] + [Code5] + [Code6] + [Code7] + [Code8] + [Code9] + [Code10]
    [Code11] + [Code12] + [Code13] + [Code14] + [Code15] + [Code16] + [Code17] + [Code18] + [Code19] + [Code20]
    [Code21] + [Code22] + [Code23] + [Code24] + [Code25] + [Code26] + [Code27] + [Code28] + [Code29] + [Code30]
    [Code31] + [Code32] + [Code33] + [Code34] + [Code35] + [Code36] + [Code37] + [Code38] + [Code39] + [Code40]
    [Code41] + [Code42] + [Code43] + [Code44] + [Code45] + [Code46] + [Code47] + [Code48] + [Code49] + [Code50]
    [Code51] + [Code52] + [Code53] + [Code54] + [Code55] + [Code56] + [Code57] + [Code58] + [Code59] + [Code60]
    )
AS CountOfYes
FROM Table1;

That's not pretty but there you go...

 

[boblarson]

A corrected table structure would be to have a table structure like this:

strName- text
strIndicator - text
lngPICode - Long Integer (primary key)
strPIName- text

tblCodeNumbers
CodeID - Autonumber (PK)
CodeName - Text

tblCodesSelected
CodeSelectedID - Autonumber (PK)
lngPICode - Long Integer (FK)
CodeID - Long Integer (FK)
CodeValue - Yes/No

Then there can be as many codes as necessary without having to change the structure. And you could, with numbering the controls on a form and some code instead of binding the form, you could have the same input form and if set up correctly you would be able to add or delete any checkboxes from it and never have to touch the code.